Selected Answer

Your question is, "Is there a formula that will copy the total figure from column B into the correct analysis column?"

The answer is no. No formula can write anything to any cell. Formulas can only change the value of the cell in which they reside. Therefore you need to reverse your logic, as Don has already suggested. In the attached workbook this idea is implemented. This is the formula that does all the work. Enter it in D1 and copy it to the right and down as required.

`=IF($C2=D$1,$B2,0)`

The trick is accomplished by setting the List range for the validation dropdown to $D$1:$G$1 (I would use a named range instead of the worksheet address). Now the formula just checks if the selected validation item matches the column caption. This idea could be expanded to use substitutes, enabling validation choices to be different from column captions. That's another question.

Where validation and caption don't match a zero is inserted. I advocate this as being superior to "" (null string) which would also work. The difference is that zero is a number and "" is text. If you try to do calculations with text an error will result. However the SUM() function interprets text as zero. Hence you may not have a problem with "".

In the attached worksheet display of zeroes is suppressed. This is done in the cell format setting of range D2:G3. It's* #,##0.00;-#,##0.00;;@*. Quickly explained, there are 4 instructions between semicolons. Excel applies them to numbers which are positive, negative, zero or text, in that sequence. The instruction for zero is blank. Therefore zeroes are displayed as blanks.

Observe the difference in the number format in column B and D2:G3. Column B uses a built-in format, D:G have a custom format.