SUMIF formulas

  • Thread starter Thread starter Caroline
  • Start date Start date
C

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
 
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
 
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...
 
Back
Top