SUMIFS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 28 Nov 2007 11:43 GMT

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

Reply to this Message

Mike H - 28 Nov 2007 11:55 GMT

Hi,

You could use sumproduct

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

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

Mike

> 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
[quoted text clipped - 3 lines]
> Any help would be very much appreciated.
> Thanks

Reply to this Message

Stephen - 28 Nov 2007 11:59 GMT

> 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
[quoted text clipped - 4 lines]
> 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),D1:D99)
will sum D1:D99 where column A contains "dog", column B is greater than 5
and column X equals Sheet2!G3.

Reply to this Message

Roger Govier - 28 Nov 2007 12:09 GMT

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 D1:D100 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

Signature

----
Regards
Roger Govier

Reply to this Message