Sumproduct 3 Criteria

  • Thread starter Thread starter JimS
  • Start date Start date
J

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).
 
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
 
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
Click to expand...

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