|
SUMPRODUCT - Bob Phillips
Bob, I am studying your wonderful article on sumproduct. I have this:
A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
I am sure Bob will not mind if I answer:
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) - SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Or in shorter form
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")-(A1:A10="Ford")*(B1:B10="June")) -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Yes, it does, Don. Problem is, there are only 9 cars. So the answer of 11
is not correct for the question as posed. James "Don Guillett" wrote in message ... Don't 4 fords and 7 Junes=11 ? -- Don Guillett Microsoft MVP Excel SalesAid Software "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Perfect! Thanks, Bernard. Both give the correct answer. Now, this is
something for me to mull over! James "Bernard Liengme" wrote in message ... Or in shorter form =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")-(A1:A10="Ford")*(B1:B10="June")) -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Zone,
There is an example that explains this, it is because the June Fords get double counted. There is also a way to exclude the double-counting =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))-SUMPRODUCT(--(A1:A10="Ford"),--(B1:B10="June")) I have been posting an improvement on this recently of =SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0)) But I have a better way, which the page is currently due an update to include, which is =SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June"))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
How about this that assumes that all blanks in a are filled
=MIN(COUNTA(a2:a11),SUMPRODUCT((a2:b11={"Ford","Ju ne"})*1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Zone" wrote in message ... Yes, it does, Don. Problem is, there are only 9 cars. So the answer of 11 is not correct for the question as posed. James "Don Guillett" wrote in message ... Don't 4 fords and 7 Junes=11 ? -- Don Guillett Microsoft MVP Excel SalesAid Software "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Thanks, Bob! I'm sure you were making the point that sumproduct could be
used to get the total instances of cars that were either Fords or sold in June at that point in the article. But I got stuck on the literal meaning of "cars that were either Fords or sold in June". Now I can go back to studying your article! Many thanks, James "Bob Phillips" wrote in message ... Zone, There is an example that explains this, it is because the June Fords get double counted. There is also a way to exclude the double-counting =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))-SUMPRODUCT(--(A1:A10="Ford"),--(B1:B10="June")) I have been posting an improvement on this recently of =SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0)) But I have a better way, which the page is currently due an update to include, which is =SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June"))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Zone" wrote in message ... Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
One other way you could write it:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0)) "Zone" wrote: Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Oops - I didn't read all of Bob's post. Never mind then.
"JMB" wrote: One other way you could write it: =SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0)) "Zone" wrote: Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
You didn't notice that Bob posted that same formula 5 hours ago?<bg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMB" wrote in message ... One other way you could write it: =SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0)) "Zone" wrote: Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
Nope - and I was wondering why he didn't because that's more than likely
where I got the idea. I read his post, but didn't scroll down enough to see the rest of it (until I re-read it just to be sure). "Ragdyer" wrote: You didn't notice that Bob posted that same formula 5 hours ago?<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMB" wrote in message ... One other way you could write it: =SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0)) "Zone" wrote: Bob, I am studying your wonderful article on sumproduct. I have this: A B 1 Make Month 2 Ford June 3 Ford June 4 Ford May 5 Ford June 6 Renault June 7 Renault June 8 BMW June 9 BMW May 10 BMW June The question is, how many cars were either Fords or were sold in June? I used =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) expecting to get 8. Yet, I get 11. I was expecting + to work like logical or, but it worked like arithmetic plus. Did I miss something? Or, how can I fix this to get the expected answer? James |
SUMPRODUCT - Bob Phillips
"Bob Phillips" wrote...
.... But I have a better way, which the page is currently due an update to include, which is =SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June")) ) .... Better how? It uses an extra level of nested function calls. There are shorter array formulas. =COUNT(1/((A1:A10="Ford")+(B1:B10="June"))) |
SUMPRODUCT - Bob Phillips
Oh goodie, Harlan woke up grumpy for a change ...
I think it is better because it is more intuitive IMO, and one extra function call is rarely going to make any difference. As to your array alternative, I quote ... True, but I meant in the sense of avoiding unnecessary follow-up and responses when the OP failed to enter your SUM formula as an array formula. Care to check the frequency of OPs failing to catch the 'array formula' caveats in responses they receive? 'Course if you like the unnecessary addition back & forth . . . Guess who said that? But I suppose you will see that as an insult in your inimitable way. Bob "Harlan Grove" wrote in message ... "Bob Phillips" wrote... ... But I have a better way, which the page is currently due an update to include, which is =SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June") )) ... Better how? It uses an extra level of nested function calls. There are shorter array formulas. =COUNT(1/((A1:A10="Ford")+(B1:B10="June"))) |
SUMPRODUCT - Bob Phillips
"Bob Phillips" wrote...
.... I think it is better because it is more intuitive IMO, and one extra function call is rarely going to make any difference. You once wrote something like that about using N(..) in place of adding 0, multiplying by 1 or double negating boolean arrays. Not much use of that idiom anymore. As to your array alternative, I quote ... .... Note I didn't SUGGEST its use, only said it's shorter, which is demonstrably true. There are still some occasions when reducing file size and recalc time are usefule if not necessary. |
SUMPRODUCT - Bob Phillips
"Harlan Grove" wrote in message ... "Bob Phillips" wrote... You once wrote something like that about using N(..) in place of adding 0, multiplying by 1 or double negating boolean arrays. Not much use of that idiom anymore. How nice, that you should follow my postings so closely, with impeccable (you think!) recall. Note I didn't SUGGEST its use, only said it's shorter, which is demonstrably true. There are still some occasions when reducing file size and recalc time are usefule if not necessary. Yes you did, you said ... Should have used =SUMPRODUCT(--D2:D6) |
SUMPRODUCT - Bob Phillips
"Bob Phillips" wrote...
"Harlan Grove" wrote in message "Bob Phillips" wrote... You once wrote something like that about using N(..) in place of adding 0, multiplying by 1 or double negating boolean arrays. Not much use of that idiom anymore. How nice, that you should follow my postings so closely, with impeccable (you think!) recall. It was several years ago, it started off with an argument between us about the relative merits of *1 and +0. You mentioned using N(..) for clarity at some point. I just can't find it in the Google Groups archive. Note I didn't SUGGEST its use, only said it's shorter, which is demonstrably true. There are still some occasions when reducing file size and recalc time are usefule if not necessary. Yes you did, you said ... Should have used =SUMPRODUCT(--D2:D6) In what other thread? Because in my first response in this thread I wrote: "Better how? It uses an extra level of nested function calls. There are shorter array formulas. =COUNT(1/((A1:A10="Ford")+(B1:B10="June")))" I included a brief quote from what you wrote, but that's all. So which other unrelated thread are you pulling this 'Should have used . . .' comment from and attributing to me completely out of the context of this thread? |
| All times are GMT +1. The time now is 06:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com