SUMIFS

[Mark]

At work I'm running Excel 2002. I want to use the SUMIFS function but I
believe this function is only for Excel 2007. Is there a similar funtion in
2002?
I'm trying to sum a range using multiple criteria from within the same
speadsheet.
Any help would be very much appreciated.
Thanks

 

[Mike H]

Hi,

You could use sumproduct

=SUMPRODUCT((B1:B10="This")*(C1:C10="That")*(D110))

Sums D1-D10 for every occurence of This & That in B1-B10, C1-C10 respectively


Mike

 

[Stephen]

At work I'm running Excel 2002. I want to use the SUMIFS function but I
believe this function is only for Excel 2007. Is there a similar funtion
in
2002?
I'm trying to sum a range using multiple criteria from within the same
speadsheet.
Any help would be very much appreciated.
Thanks

I don't know about 2007, but you can do summing with multiple criteria in
older versions with SUMPRODUCT. For example
=SUMPRODUCT(--(A1:A99="dog"),--(B1:B99>5),--(X1:X99=Sheet2!G3),D199)
will sum D199 where column A contains "dog", column B is greater than 5
and column X equals Sheet2!G3.

 

[Roger Govier]

Hi Mark

Take a look at the Sumproduct function.

=SUMPRODUCT(--($A$1:$A$100="Test"),--($B$1:$B$100="Another
test"),--($C$1:$C$100=50),$D$1:$D$100)

This would add all cell values in D1100 where the corresponding entries in
A was Test, in B was Another Test and C was 50.

You can use cell references instead of entering the values in the formula.

For more help on Sumproduct take a look at Bob Phillips site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html