Multi Criteria then sum

L

lost at work

Need help! I need to have the following put into a function:
If B25:B60 = "Sports" and C25:C60 = "New" and G25:G60 = "11/1/2004" then add
only those that meet all three criteria.

Thanks to the great brain that helps.
 
C

cvolkert

Here you go - not sure what you want to sum, but I'll assume column H
for the sake of this formula - change as needed:

=sumproduct((B25:B60 = "Sports")*(C25:C60 = "New")*(G25:G60 =
"11/1/2004"*1)*H25:H60)

Let me know if this doesn't work or if you have more questions - Chad
 
B

Bob Phillips

Add what? Or count?

=SUMPRODUCT(--(B25:B60="Sports"),--(C25:C60="New"),--(G25:G60=--"2004-01-11"
))
 
B

Bob Phillips

I assume that you mean H25:H60

=SUMPRODUCT(--(B25:B60="Sports"),--(C25:C60="New"),--(G25:G60=--"2004-01-11"
),H25:G60)
 
M

Matt

Guys, what would cause me to get a #Num error when doing this function??

This is getting irritating!!!
 
D

Domenic

Matt said:
Guys, what would cause me to get a #Num error when doing this function??

This is getting irritating!!!
Click to expand...

You'll get that error if you're using whole column references.
SUMPRODUCT doesn't accept them. You could change the reference to a
'near' whole column reference, such as B2:B65536.

Hope this helps!
 
M

Matt

Thanks, that took away the #num error, but now it's not totaling. It has to
do something with the date because when I run the formula with two text
columns it works, but when I change it to one text column and then also
looking for a date, it comes out with a zero amount.
 
D

Domenic

Matt said:
Thanks, that took away the #num error, but now it's not totaling. It has to
do something with the date because when I run the formula with two text
columns it works, but when I change it to one text column and then also
looking for a date, it comes out with a zero amount.
Click to expand...

Make sure that the format for the date column matches the cell format
for its criterion. Try the following...

1) Select an empty cell

2) Edit > Copy

3) Select the date column

4) Edit > Paste Special > Add > Ok

Does this help?
 
M

Matt

Nope, that didn't work. Still came up with zero. Here's the sample table
I'm trying to use before I impliment this on my real table:

A B C D
Date Name Product Qty
6/1/2005 Jason bikes 6
6/2/2005 Randy cars 8
6/3/2005 Randy bikes 9
6/1/2005 Daniel cars 8
6/2/2005 Matt cars 231
6/3/2005 Daniel bikes 654
6/1/2005 Jason cars 654
6/2/2005 Daniel cars 65

Here's my formula:
=SUMPRODUCT(--(A2:A12="6/1/2005"),--(C21:C12="Bikes"),D2:D12)

There's something with the date I know. Does it need to be in "" ? I've
tried both ways and it doesn't work. I even tried changing it from a date
format to a number (38504) that didn't work either.
 
M

Matt

Nevermind... It is the format of the date. When I enter A2:A12=38504 it
works. Thank you for your help!!
 
D

Domenic

You need to coerce the date to a numerical value...

=SUMPRODUCT(--(A2:A12="6/1/2005"+0),--(C21:C12="Bikes"),D2:D12)

Hope this helps!
 
D

Dave Peterson

I'd use:

=SUMPRODUCT(--(A2:A12=date(2005,6,1)),--(C21:C12="Bikes"),D2:D12)

Just to remove any ambiguity between mdy and dmy dates.
 
Top