SUMIF formulas
 

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

SUMIF formulas
 

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
--
Max
Singapore
xde
---
"Caroline" wrote:
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

SUMIF formulas
 

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

--
Wag more, bark less


"Caroline" wrote:

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