Selected Answer

The formula below will do what you want.

[CODE=SUMPRODUCT(SUMIF(Data1,Codes,Amounts))+SUMPRODUCT(SUMIF(Data2,Codes,Amounts))[/CODE]

To make it easier to read I named the ranges as follows.

*Data1* = L5:L21 in your worksheet
*Data2 *= M5:M21 in your worksheet
*Amounts* = N5:N21 in your worksheet
*Codes* = A5:A7 in your sample worksheet

You can replace the names in the formula with the actual range addresses but since at least the *Codes* range is dynamic it will be a lot more efficient to apply the dynamism to the named range specification than to every formula. Look up "Dynamic named ranges" or ask on this forum.

The funny thing is that my method can handle only one column. There must be a better way to this. If there is it eluded me this evening. Therefore my formula just draws the total for each data column and adds them up.

In my search for a better solution I hit upon the formula below which is using *SUM* instead of *SUMPRODUCT*. Unfortunately, it has the same drawback but seems to handle it a little smarter.

`=SUM(SUMIFS(Amounts,Data1,Codes),SUMIFS(Amounts,Data2,Codes))`

Note that this is an array formula. It must be committed with *Shift+Control+Enter* (in place of the simple *Enter* for normal formulas). Just type or paste the formula in the *Formula Bar* as usual but don't hit *Enter *when you are done typing. Press *Shift+Control+Enter* instead. When confirmed correctly Excel will dispaly the formula surrounded by curly braces in the *Formula Bar*. Else, there will be a *#VALUE* error.

This may tilt the balance to let you favour the *SUMPRODUCT* variety. Actually, *SUMPRODUCT* is perhaps the only worksheet function which naturally handles arrays, a capability otherwise reserved for array formulas.