|
SUMPRODUCT question
I am trying to count based on multiple conditions. Here is a sample of my
table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Hi,
It's the quotes, try this =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) and if Feb is a correctly formatted date try this =SUMPRODUCT((A2:A22=1188)*(MONTH(B2:B22)=2)) Putting the number in quotes makes it a text string. Mike "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))
works fine.. Boss "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
I use a funky workaround for stuff like this. I write a third (can be hidden)
column in which I combine the 2 values, usually with a carat as a separator, just in case the combined values could be ambiguous (e.g. 1 & 00 are 100, but so are 10 & 0). So... I could count the 1188 & Feb combination by adding column C and then asking: =COUNTIF(C1:C14,"1188^FEB") A B C 1188 Feb =A1&"^"&B1 1188 Feb =A2&"^"&B2 1188 Apr =A3&"^"&B3 1213 May =A4&"^"&B4 1188 May =A5&"^"&B5 1259 Mar =A6&"^"&B6 1188 Mar =A7&"^"&B7 0994 Jun =A8&"^"&B8 1188 Jun =A9&"^"&B9 0994 Jul =A10&"^"&B10 1188 Jun =A11&"^"&B11 0993 May =A12&"^"&B12 1188 Jun =A13&"^"&B13 1188 Feb =A14&"^"&B14 "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
If column A is using numbers as opposed to text, then you need to remove the
quotes. =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) or =SUMPRODUCT(--(A2:A22=1188),--(B2:B22="Feb")) HTH, Paul -- "PFAA" wrote in message ... I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Thanks. The first one worked!
So I don't need to use an array? Why do all the instructions I found about multiple conditions tell me to use an array formula? Thanks, PFAA "Mike H" wrote: Hi, It's the quotes, try this =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) and if Feb is a correctly formatted date try this =SUMPRODUCT((A2:A22=1188)*(MONTH(B2:B22)=2)) Putting the number in quotes makes it a text string. Mike "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Hi,
Sumproduct works like an array entered type formula but it doesn't need to be array entered. I'm glad that worked and thanks for the feedback. Mike "PFAA" wrote: Thanks. The first one worked! So I don't need to use an array? Why do all the instructions I found about multiple conditions tell me to use an array formula? Thanks, PFAA "Mike H" wrote: Hi, It's the quotes, try this =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) and if Feb is a correctly formatted date try this =SUMPRODUCT((A2:A22=1188)*(MONTH(B2:B22)=2)) Putting the number in quotes makes it a text string. Mike "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Me again...sorry.
With regards to you suggestion of adding "MONTH". I would like to be able to enter dates in this format Mmm dd (i.e. Feb 14 or Mar 2 or Jul 31 etc.). Can it ready that date format and recognize the month? Can I do that? Also, can I enter this formula in Worsheet 2 and ask it calculate the Cells in Worksheet 1? Thanks, PFAA "Mike H" wrote: Hi, Sumproduct works like an array entered type formula but it doesn't need to be array entered. I'm glad that worked and thanks for the feedback. Mike "PFAA" wrote: Thanks. The first one worked! So I don't need to use an array? Why do all the instructions I found about multiple conditions tell me to use an array formula? Thanks, PFAA "Mike H" wrote: Hi, It's the quotes, try this =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) and if Feb is a correctly formatted date try this =SUMPRODUCT((A2:A22=1188)*(MONTH(B2:B22)=2)) Putting the number in quotes makes it a text string. Mike "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
thanks, when I removed the quotations it worked!
What do the "--" represent? I've seen that in a couple of furmulas and I am wondering when/why I should use them. Thanks, PFAA "PCLIVE" wrote: If column A is using numbers as opposed to text, then you need to remove the quotes. =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) or =SUMPRODUCT(--(A2:A22=1188),--(B2:B22="Feb")) HTH, Paul -- "PFAA" wrote in message ... I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Thanks for your response - all I had to do what remove the quotations around
1188 and it worked. PFAA "The Mysterious J" wrote: I use a funky workaround for stuff like this. I write a third (can be hidden) column in which I combine the 2 values, usually with a carat as a separator, just in case the combined values could be ambiguous (e.g. 1 & 00 are 100, but so are 10 & 0). So... I could count the 1188 & Feb combination by adding column C and then asking: =COUNTIF(C1:C14,"1188^FEB") A B C 1188 Feb =A1&"^"&B1 1188 Feb =A2&"^"&B2 1188 Apr =A3&"^"&B3 1213 May =A4&"^"&B4 1188 May =A5&"^"&B5 1259 Mar =A6&"^"&B6 1188 Mar =A7&"^"&B7 0994 Jun =A8&"^"&B8 1188 Jun =A9&"^"&B9 0994 Jul =A10&"^"&B10 1188 Jun =A11&"^"&B11 0993 May =A12&"^"&B12 1188 Jun =A13&"^"&B13 1188 Feb =A14&"^"&B14 "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Thanks for your reply. But it didn't work until I removed the quotation marks
around 1188.... PFAA "Boss" wrote: =SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb")) works fine.. Boss "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Hi,
With regard to dates it's better to have a properly formatted date than text and to do that enter a date in the normal way 15/6/2008 and then format it to suit If you apply a custom format of MMM DD to a correctly formatted date then you get the result you want which would be JUN 15 for the example above. Now if you want to do a vlookup on that from another sheet then the formula is =SUMPRODUCT((Sheet1!A1:A20=1188)*(MONTH(Sheet1!B1: B20)=6)) Yoy could enter the dates as text in the format you want but it looks for an exact match and the risk of error increases. far better to do it correctly from the outset. Mike "PFAA" wrote: Me again...sorry. With regards to you suggestion of adding "MONTH". I would like to be able to enter dates in this format Mmm dd (i.e. Feb 14 or Mar 2 or Jul 31 etc.). Can it ready that date format and recognize the month? Can I do that? Also, can I enter this formula in Worsheet 2 and ask it calculate the Cells in Worksheet 1? Thanks, PFAA "Mike H" wrote: Hi, Sumproduct works like an array entered type formula but it doesn't need to be array entered. I'm glad that worked and thanks for the feedback. Mike "PFAA" wrote: Thanks. The first one worked! So I don't need to use an array? Why do all the instructions I found about multiple conditions tell me to use an array formula? Thanks, PFAA "Mike H" wrote: Hi, It's the quotes, try this =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) and if Feb is a correctly formatted date try this =SUMPRODUCT((A2:A22=1188)*(MONTH(B2:B22)=2)) Putting the number in quotes makes it a text string. Mike "PFAA" wrote: I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
A double minus is also known as a double urnary.
In this formula: =SUMPRODUCT(--(A1:A10 & D1:D10="1A")) this section: (A1:A10 & D1:D10="1A") returns a series of boolean (fancy word for: TRUE/FALSE) values ....which are not numeric to Excel. When an arithmetic operator (+,-,*,/) is applied to a boolean value, Excel converts TRUE to 1 and FALSE to 0. The standard convention is to use the Double-Minus (--) to convert the values. It works this way: TRUE=TRUE -TRUE = -1 --TRUE = 1 FALSE = FALSE -FALSE = 0 --FALSE = 0 Now, you could easily use 1*TRUE, but the Dbl-Minus indicates to knowledgable users that you are forcing a conversion and not trying to calculate something. So, In the formula, the TRUE/FALSE values are converted to 1's and 0's by the "--" and the SUMPRODUCT calculates the total. - explanation contribution thanks to Ron Coderre -- "PFAA" wrote in message ... thanks, when I removed the quotations it worked! What do the "--" represent? I've seen that in a couple of furmulas and I am wondering when/why I should use them. Thanks, PFAA "PCLIVE" wrote: If column A is using numbers as opposed to text, then you need to remove the quotes. =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) or =SUMPRODUCT(--(A2:A22=1188),--(B2:B22="Feb")) HTH, Paul -- "PFAA" wrote in message ... I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
SUMPRODUCT question
Wow. That's quite an answer. =) Thanks. I'm learning more today than I
wanted to. =) PFAA "PCLIVE" wrote: A double minus is also known as a double urnary. In this formula: =SUMPRODUCT(--(A1:A10 & D1:D10="1A")) this section: (A1:A10 & D1:D10="1A") returns a series of boolean (fancy word for: TRUE/FALSE) values ....which are not numeric to Excel. When an arithmetic operator (+,-,*,/) is applied to a boolean value, Excel converts TRUE to 1 and FALSE to 0. The standard convention is to use the Double-Minus (--) to convert the values. It works this way: TRUE=TRUE -TRUE = -1 --TRUE = 1 FALSE = FALSE -FALSE = 0 --FALSE = 0 Now, you could easily use 1*TRUE, but the Dbl-Minus indicates to knowledgable users that you are forcing a conversion and not trying to calculate something. So, In the formula, the TRUE/FALSE values are converted to 1's and 0's by the "--" and the SUMPRODUCT calculates the total. - explanation contribution thanks to Ron Coderre -- "PFAA" wrote in message ... thanks, when I removed the quotations it worked! What do the "--" represent? I've seen that in a couple of furmulas and I am wondering when/why I should use them. Thanks, PFAA "PCLIVE" wrote: If column A is using numbers as opposed to text, then you need to remove the quotes. =SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb")) or =SUMPRODUCT(--(A2:A22=1188),--(B2:B22="Feb")) HTH, Paul -- "PFAA" wrote in message ... I am trying to count based on multiple conditions. Here is a sample of my table and the formula. {=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))} A B 1188 Feb 1188 Apr 1213 May 1188 May 1259 Mar 1188 Mar 0994 Jun 1188 Jun 0994 Jul 1188 Jun 0993 May 1188 Jun 1188 Feb It returns a value of "0". Please help. Thanks, PFAA |
| All times are GMT +1. The time now is 10:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com