Sumproduct 3 Criteria

[JimS]

I'm trying to determine the results of three criteria. Three data
points in three different columns.

A b c
1/24/09 20h 250
1/24/09 20h -36
1/24/09 20h 450

If I do a sumproduct of two criteria B and C, where B="20h" I get 664,
which is good. But if I do a sumproduct of all three I get an answer
of:

9,959,250

I know this has something to do with the date.

So, obviously I want to sum everything in column C that has matching
days and is equal to B (20h).

 

[Pete_UK]

Well, Jim, you've only got two conditions and a third column that you
want to sum. Look at it this way:

=SUMPRODUCT((A1:A100=DATE(2009,1,24))*(B1:B100="20h"))
or:
=SUMPRODUCT(--(A1:A100=DATE(2009,1,24)),--(B1:B100="20h"))

will COUNT the number of rows that match those conditions, whereas:

=SUMPRODUCT((A1:A100=DATE(2009,1,24))*(B1:B100="20h")*(C1:C100))
or:
=SUMPRODUCT(--(A1:A100=DATE(2009,1,24)),--(B1:B100="20h"),(C1:C100))

will SUM the cells in column C that have matching criteria in columns
A and B.

Of course, you could put your criteria in separate cells to make it
more flexible, like this

=SUMPRODUCT((A1:A100=D1)*(B1:B100=E1)*(C1:C100))

where D1 contains the date and E1 contains 20h.

Note that (C1:C100) has no criteria to compare.

Hope this helps.

Pete

 

[JimS]

Of course, you could put your criteria in separate cells to make it
more flexible, like this

=SUMPRODUCT((A1:A100=D1)*(B1:B100=E1)*(C1:C100))

where D1 contains the date and E1 contains 20h.

Note that (C1:C100) has no criteria to compare.

Hope this helps.

Pete

I've been studying this, Pete. I think what I'm doing is much too
complicated. I'm going to have to simplyfy somehow and start over.

Thanks for you help.

 

[Pete_UK]

You're welcome, Jim - thanks for feeding back.

I was going to give you a link to Bob Phillip's site at:

http://www.xldynamic.com/source/xld.html

where he has a very good article about SUMPRODUCT, but the link
appears to be broken when I tried it a minute ago.

Pete

 

[Pete_UK]

Bob's site seems to be up again now, so have a look here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete