SUMIF formulas

[Caroline]

Hi there

I'm trying to get the following fomula to work.

=SUMIF('Actuals YTD'!$A$2:$A$630,"=5010111",'Actuals
YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5010211",'Actuals
YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5120111", 'Actuals
YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5200111",'Actuals
YTD'!$C$2:$N$630)

It doesn't appear to be picking up the figures in any other column other
than "C", when it should be adding up from "C" to "N". A SUMPRODUCT doesn't
work due to the fact the data I'm searching on doesn't run in sequence.

Any help greatly appreciated!!

Thanks
Caroline

 

[Max]

Lightly tested ok, think you could try something like this:
=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{555,333,444},0))*B2:C10)
Adapt the ranges & the criteria ie the nums within the {...} bit, to suit

As for your obs on SUMIF, it works only for a single sum col, ie the
leftmost col C in your expression, albeit Excel seems to happily accept the
multi-col, thus giving you the false impression that it works that way. The
above sumproduct should provide you with a concise way to arrive at the
expected results. Success? hit the YES below

 

[Brad]

Just a thought, if you had another column that added up the information in
columns C-N and you could use your current formula and access the "new"
column of numbers...