Locking horns over Sumproduct

[teylyn]

In another forum that cannot be named, I happened upon this almost 4 year old discussion about SUMPRODUCT. I almost put it in the joke thread, but reconsidered.

The insiders here will hopefully enjoy the tit for tat, especially when they recognise the key ("tosh") poster's identity. Enjoy

**************************************************
Author: P***, Date: 09.07.2006 at 11:32AM PDT
Title: And again SUMPRODUCT Problems...
Question: Dear Experts,
Something basic I still did not understand using the SUMPRODUCT Formula.....
I'm using this formula and the result is 0, although it shouldn't. What is wrong?
If the first condition is true and if the second condition is true and if the third condition is true, I would like to have a number as a result telling me that in the month September in cell Statistics!$B$5 the car "Sprinter" in cell "Statistics!$A7" was 3 times at the location "Car-Wash-Alarm-Neste".
thanks for help
regards
Nils
=SUMPRODUCT((Entry!$B$6:$B$994=Statistics!$B$5),(Entry!$G$6:$G$994="Statistics!$A7"),(Entry!$D$6:$D$994="Car-Wash-Alarm-Neste"))
------------------------------------------------------------------------
Author: m***, Date: 09.07.2006 at 11:35AM PDT
m***:
Nils,
Remove the quotes around "Statistics!$a7", it is looking for that as a literal string instead of the contents of that cell.
Matt
------------------------------------------------------------------------
Author: patrick***, Date: 09.07.2006 at 12:22PM PDT
Nils,
Try:
=SUMPRODUCT((Entry!$B$6:$B$994=Statistics!$B$5)*(Entry!$G$6:$G$994="Statistics!$A7")*(Entry!$D$6:$D$994="Car-Wash-Alarm-Neste"))
Patrick
------------------------------------------------------------------------
ID:17474630 Author: L***, Date: 09.07.2006 at 01:52PM PDT
Nils:
Matt noticed the quotes and Patrick added the asterix. The result of both solutions comes to.
=SUMPRODUCT((Entry!$B$6:$B$994=Statistics!$B$5)*(Entry!$G$6:$G$994=Statistics!$A7)*(Entry!$D$6:$D$994="Car-Wash-Alarm-Neste"))
Jaes
------------------------------------------------------------------------
Author: g***, Date: 09.08.2006 at 01:44AM PDT
g***:
I'm pretty sure you don't need the multiplication operators (alos known as *). Just removing the quotes should do it.
------------------------------------------------------------------------
Author: L***, Date: 09.08.2006 at 08:20AM PDT
gbentley:
Hmmm, I always use them.
Jaes
------------------------------------------------------------------------
Author: BobP, Date: 09.08.2006 at 09:25AM PDT
g***:
Some sort of operator to coerce the conditional tests to a numeric that SUMPRODUCT can work on is absolutely necessary. It doesn't have to be *, it could be ---, +0,^1,*1 or whatever, but you can't just ditch tehm.
------------------------------------------------------------------------
Author: patrick***, Date: 09.08.2006 at 01:10PM PDT
g***:
SUMPRODUCT works by evaluating each test in turn. If the test is true then the value of that test is 1 if not true the value of that test is zero. SUMPRODUCT then multiplies (if * is used) the results of the tests together and sums the total of the results. Thus if one of the tests is untrue then the value or all the tests on that row is zero and so nothing is added to the total.
If you are using SUMPRODUCT just to multiply arrays together then only a comma is Ok but if you want to have multiple tests then the results of those tests must be multiplied together - thus the need for the * between each test.
In other words it all depends on how you are using SUMPRODUCT.
Patrick
------------------------------------------------------------------------
Author: L***, Date: 09.08.2006 at 02:15PM PDT
Patrick:
Thank you for that quite excellent explanation. I wondered how it worked. That makes perfect sense.
Jaes
------------------------------------------------------------------------
Author: BobP, Date: 09.08.2006 at 05:00PM PDT
Sorry patrick***, but that is tosh.
If the test is true, then the result is not 1 but TRUe, if it is not, the result is FALSE. And there is not absolute need for multiplying because that is exactly what the PROFDUCT part of SUMPRODUCT part does. The necessity of some mathematical operator is to coerce the array of TRUE/FALSE values that are returned from the c onditional tests to an array of 1/0 values, that CAN be multiplied and summed.
If you want a proper explanation of the evolved use of SUMPRODUCT, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.
------------------------------------------------------------------------
Author: m*** Date: 09.08.2006 at 05:11PM PDT
Bob,
My train of thought: "Hey this guy is linking to xldynamic, I wonder if Bob knows about this. Wait a minute ..." I saw your username earlier but as Bob isnt a very uncommon name I didn't think to put 2 and 2 together as to who you were (I do seem to remember seeing you in a question a while back -- after looking I see it was one of Joanne's questions). Is this cross posted somewhere? In any case, welcome back!
Matt
------------------------------------------------------------------------
Author: BobP, Date: 09.08.2006 at 05:15PM PDT
Hi Matt,
I had almost forgotten this place, drawn back by a other post I saw somewhere else. Not my favourite forum as it is web based, and I am drawn to UseNet like a moth to a flame :-).
Hey, and congrats on the Cell Master.
Regards
Bob
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 12:31AM PDT
BobP,
It's sad that you clearly have little idea how a computer works. If someting is TRUE then it is treated a being equal to 1 and zero if it is false. Computers only work with zeros and 1s - nothing else.
Equally, you have not bothered to try out SUMPRODUCT with conditional tests using just commas as the separators. I have and it does not work. Other operators are needed to make it work.
So may I say that your dismissal of some simple experience and knowledge is misplaced and simply discourteous. I suggest that you have a look at this link for more on SUMPRODUCT:
http://www.vbaexpress.com/forum/showthread.php?t=1317
It's back to the drawing baorad for you I'm afraid.
Patrick
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 12:36AM PDT
BobP,
And from the site that you quote but obviously haven't read it says:
"But as this page is about SUMPRODUCT, you would expect that we could use that function in this case, and we can. The solution for the number of Fords sold in June using this function is
=SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")).
The value is obtained with
=SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")*(C1:C10))
Good luck.
Patrick
------------------------------------------------------------------------

[teylyn]

*** forum software has a character limit per post, here's part II***
Author: patrick***, Date: 09.09.2006 at 01:46AM PDT
p.s. I am unsubscribing from this thread as I cannot be bothered to argue the toss with BobP any longer on this simple matter - I have better things to do!
------------------------------------------------------------------------
Author: BobP, Date: 09.09.2006 at 02:31AM PDT
patrick*** said:
>> It's sad that you clearly have little idea how a computer works. If someting is TRUE
>> then it is treated a being equal to 1 and zero if it is false. Computers only work with
>> zeros and 1s - nothing else.
I am afraid it is clear that I have a lot more understanding than you do my friend. In Excel TRUE is not TREATED as being equal to 1, the numeric equivalent (note that phrase, it is key) of TRUE is 1. In other words, to get it to be 1 you have to coerce it as I said earlier, which is what the mathematical operators do. If you think that TRUE is equal to 1, enter TRUE into a range of cells and SUM them. By your theory you should get the number of TRUEs, in reality you will get 0.
In programming languages, it actually is -1 that equates to the Boolean TRUE, but for
some reason Excel equates it to positive 1, which is why you need a double unary, not a single.
patrick*** said:
>> Equally, you have not bothered to try out SUMPRODUCT with conditional tests using
>> just commas as the separators. I have and it does not work. Other operators are
>> needed to make it work.
I most certainly have, and I know it doesn't work if you don't use some method to coerce the TRUE/FALSE array(s). But it does work if you use some method, and as I said it does not have to be *.
Again I quote you when you said
... If you are using SUMPRODUCT just to multiply arrays together then only a comma is Ok but if you want to have multiple tests then the results of those tests must be multiplied together - thus the need for the * between each test.
... which is the tosh that I refer to. I can show you a million SUMPRODUCT formulae without a * in sight, it is not needed to multiply as you said it was, but if used it is used to get arrays of 1's and 0's for SUMPRODUCT to work on because TRUE is not a 1 (1 is the numeric equivalent of TRUE, but it needs to be coerced to that numeric equivalent).
Interestingly, the article you quote in VBAX doesn't use a * either, so again I miss the point you think you are making.
Just try this
=SUMPRODUCT(--(rng1="value1"),--(rng2=10))
Not a * in sight. Are you telling me that doesn't work? Be smart before saying yes, and test it.
If you are going to expound, expound correctly or accept the correction of erroneous statements.
patrick*** said:
>> And from the site that you quote but obviously haven't read it says:
Again you are wrong. I have read that page, quite a few times. In fact, if I recall correctly I wrote it.
patrick*** said:
>>"But as this page is about SUMPRODUCT, you would expect that we could use
>> that function in this case, and we can. The solution for the number of Fords
>> sold in June using this function is
>> =SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")).
>> The value is obtained with
>> =SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")*(C1:C10))
You will have to explain the point you are making here, all I see is a simple SUMPRODUCT formula.
patrick*** said:
>> So may I say that your dismissal of some simple experience and knowledge
>> is misplaced and simply discourteous. I suggest that you have a look at this
>> link for more on SUMPRODUCT:
>> http://www.vbaexpress.com/forum/showthread.php?t=1317
>> It's back to the drawing baorad for you I'm afraid.
Sorry, but when you give wrong information as if it is gospel I am going to correct you. That is what I did. Unfortunately you did not have the sense to read what I said properly, read the paper I suggested properly, not try a few experiments. If you had, you might understand it better.
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 04:58AM
BobP - Simply risible! - Patrick
------------------------------------------------------------------------
Author: BobP, Date: 09.09.2006 at 05:32AM PDT
I thought you unsubscribed?
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 06:13AM PDT
I did, but I laughed so much when I read your diatribe I decided to let you know.
------------------------------------------------------------------------
Author: BobP, Date: 09.09.2006 at 06:42AM PDT
But if you unsubscribed, you couldn't have read it.
So pray humour me, and tell me ANYTHING I said that is wrong.
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 06:58AM PDT
BobP,
You have your way of dealing with people here by saying something is 'tosh' when you disagree with it. So be it. To date that's not been the way on the Excel forum here.
I honestly cannot be bothered to debate with you as to how many fairies can dance on the head of a SUMPRODUCT pin. I know what works and why it works - you consider that tosh - well that's your prerogative. I have other more entertaining and interesting things to do with my time.
Just why I laugh is the silliness of your arguments. In your wisdom perhaps you'd like to enter =TRUE()*5 into a cell and then explain why the answer is 5 as opposed to the zero that you appear to believe should be the result.
Patrick
------------------------------------------------------------------------
Author: P***, Date: 09.09.2006 at 07:28AM PDT
Dear Experts,
looks like my question was an interesting one. :-) :-(
Thank you very much for the rich feedback, comments and ideas about the SUMPRODUCT! I will now try once again to solve my question with all I learned here.
I decided to give the points to BobP for the very helpful link.
BUT, thanks to everybody, especially Patrick who helped me already lot's of times!! No reason to blame such an expert. I'm sure everybody here has his experiance is to goal to help so I don't understand what for to blame each other...
however thanks all of you
Regards
Nils
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 07:35AM PDT
Nils - Fully understood - thanks - Patrick
------------------------------------------------------------------------

[teylyn]

*** part III ***
Author: BobP, Date: 09.09.2006 at 09:13AM PDT
Patrick,
You seem determined to not read what I say. I know that TRUE*5 is not 0, I never said it would be. I said put TRUE in a range of cells, say A1:A5 and then sum those cells. If TRUE were 1 as you claimed, that would return 5, but it doesn't, it reurns 0.
I know that TRUE*5 returns 5, but importantly I also know why, and it is because the * coerces the TRUE to its numeric equivalent. I said earlier that that phrase is very important, it is the crux of the matter. It may be fairies dancing on a pinhead to you, it is correctness and accuracy to me.
I say something is tosh when it is tosh, not just because I disagree. You should chill-out, that is very mild comment, and it reflects upon what was written, not necessarily on your Excel skill or you personally as you seem to think. I have had many worse things direcetd at me in my career, and I either argue it strongly as you have done, or I acknowledge that the comentatoir is correct and I was wrong.
But you are wrong here mate, plain and simple. You may well understand it correctly and just have phrased it badly, or you may not properly
understand (I think it is the latter). Either way, you would do better to listen to what is said rather than immediately raising your hackles, and seek to increase that knowledge. Most of your arguments have been directed at things I didn't say, or you mis-read, and you even threw my own paper back at me as an argument.
I am sure you will be pleased to hear that I have now said everything I have to say on this topic <g>.
------------------------------------------------------------------------
Author: patrick***, Date: 09.09.2006 at 11:45AM PDT
BobP,
I knew this discussion was futile because you insist on pedantry. What you have said, sadly serves no purpose whatsoever. Your pedantry is exemplified by this:
"I know that TRUE*5 returns 5, but importantly I also know why, and it is because the * coerces the TRUE to its numeric equivalent. I said earlier that that phrase is very important, it is the crux of the matter. It may be fairies dancing on a pinhead to you, it is correctness and accuracy to me."
That is old stuff. Please tell me something new. Likewise =5--TRUE() is 6 so what. I don't need you to tell me why.
One moment you say that commas are enough and the next moment you say you need a logical operator to coerce TRUE to become 1. It seems you want it both ways. Unlike you I have been helping people here with SUMPRODUCT very satisfactorily for a while so I guess I actually do know how it works. I do not pretend to know it all - far from it. However I really don't think this discussion is going anywhere. You have not thrown light on anything useful that I can use and that I didn't already know. I'll leave you to dance on that pinhead!
BFN
Patrick
------------------------------------------------------------------------
Author: BobP, Date: 09.09.2006 at 01:46PM PDT
Well, you can't leave it alone, so why should I.
Again I ask, rather than just insult me, tell me ANYTHING I said that is wrong.
Answering questions here is not the whole world, some of us live elsewhere and actually explain things correctly, not with some pseudo-mythology.
My paper on SUMPRODUCT states ...
There is no situation that I know of whereby a solution using -- could not be achieved somehow with a '*'. Conversely, if using the TRANSPOSE function within SUMPRODUCT, then the '*' has to be used.
If you understand SUMPRODUCT so well as you think you do, explain to this to me why this is os.
If you can actually talk the talk technically speaking, go ahead, if not, shut the f#*! up.
------------------------------------------------------------------------
Author: by***, Date: 09.09.2006 at 02:00PM PDT
Although it can be both instructive and entertaining (both qualities true in this situation) when two experts who know a lot lock horns--it is not good when the discussion involves invective. I have therefore deleted a number of comments.
by***
------------------------------------------------------------------------
Author: fan***, Date: 09.09.2006 at 02:24PM PDT
Matt... when you put 2 and 2 together, did you use *, ---, +0, ^1, *1, or a different operator?
------------------------------------------------------------------------
Author: BobP, Date: 09.09.2006 at 02:27PM PDT
Apologies to the group, I tried to be reasonable, but I feel that my arguments were totally ignored and my colleague was intent on just telling me I was a fool, when I feel his knowledge in this area is lacking.
I would suggest that I was careful to convey my feelings fully without invective, but I will refrain in future from even this small attempt at humour.
------------------------------------------------------------------------
Author: m***, Date: 09.09.2006 at 02:43PM PDT
Nigel - It could really be any of those, you forgot the + and , ( =sum(2,2) )
Bob - Humor is what gets us through the day, I hope this doesn't stop you. Look for the very oldest open thread here in this topic area (and for its predecessor), you'll see most everyone here likes to kick back. We do all have our off days though, I'm guessing thats what happened here.
------------------------------------------------------------------------
Author: fan***, Date: 09.10.2006 at 12:51AM PDT
Don't blame Bob for just sticking to the answer. The forums that are usually his playground may not accept humo[u]r as part of the process as we do here. I have tried a few others in the past, and find ***-site the most appropriate for my style of answering. The others frown upon anything other than facts.
On that subject, I blame mathematics for my divorce... my wife put 2 & 2 together.
[No responses needed about why she didn't use =CONCATENATE()].

BFN,
fp.
------------------------------------------------------------------------
Author: BobP, Date: 09.10.2006 at 02:39AM PDT
There was no humour in that guy's response, just making spurious arguments about a subject he clearly doesn't properly understand.

[Domski]

Ha ha, I met Bob at an Excel gig in London last year. Can't imagine him getting angry over something like this but definitely someone who would stick to their guns when questioned about something they clearly have a far greater understanding of.

Dom

[DonkeyOte]

Didn't NBVC used to go by the handle "Patrick" ?

j/k !!!

[NBVC]

Hmmmmm. Am I like that?

[DonkeyOte]

No - I was just thinking of someone who knows what they're talking about but who wouldn't take offence at the comment
(it's a pretty limited list... again j/k!)

[Domski]

The best way I've seen to wind Bob up is to start a conversation about american football and throw in some comparisons with proper football (aka soccer) and rugby.

Stand back and watch the sparks fly!!!

Dom

[romperstomper]

That is one of my favourite ever questions at that 'unnamed site' and still makes me laugh even though I've read it numerous times.
I'll refrain from commenting about either of the protagonists lest I say something I shouldn't.