Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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! |
#9
|
|||
|
|||
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! |
#10
|
|||
|
|||
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? |
#11
|
|||
|
|||
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? |
#12
|
|||
|
|||
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? |
#13
|
|||
|
|||
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. |
#14
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
How do I do multi VLOOKUP's based on certain criteria per cell? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |