Multi Criteria then sum
 

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.

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


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380735

Add what? Or count?

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

--
HTH

Bob Phillips

"lost at work" <lost at wrote in message
...
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.

Oops! Add total of visitors H25:G60

Thanks,
--
Lost at Work


"Bob Phillips" wrote:

Add what? Or count?

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

--
HTH

Bob Phillips

"lost at work" <lost at wrote in message
...
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.

You folks are the greatest!!!

Thank you!!
--
Lost at Work


"cvolkert" wrote:


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


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380735

I assume that you mean H25:H60

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


--
HTH

Bob Phillips

"Lost at Work" wrote in message
...
Oops! Add total of visitors H25:G60

Thanks,
--
Lost at Work


"Bob Phillips" wrote:

Add what? Or count?


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

--
HTH

Bob Phillips

"lost at work" <lost at wrote in message
...
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.

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

This is getting irritating!!!



"Lost at Work" wrote:

You folks are the greatest!!!

Thank you!!
--
Lost at Work


"cvolkert" wrote:


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


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380735

In article ,
"Matt" wrote:

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

This is getting irritating!!!

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!

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.

"Domenic" wrote:

In article ,
"Matt" wrote:

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

This is getting irritating!!!

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!

In article ,
"Matt" wrote:

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.

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?

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.

"Domenic" wrote:

In article ,
"Matt" wrote:

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.

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?

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

"Matt" wrote:

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.

"Domenic" wrote:

In article ,
"Matt" wrote:

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.

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?

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!

In article ,
"Matt" wrote:

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.

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.

Matt wrote:

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.

"Domenic" wrote:

In article ,
"Matt" wrote:

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.

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?


--

Dave Peterson