PDA

View Full Version : SUMPRODUCT Step By Step - Part 1

Bob Phillips
04-12-2009, 01:16 PM
SUMPRODUCT Basics

Overview

In its native form, SUMPRODUCT is used to multiply each element of 2 or more arrays and sum the individual products. As an example, suppose that we have the following table

A B
1 1 4
2 2 5
3 3 6

We can use

=SUMPRODUCT(A1:A3,B1:B3)

which returns 32. This is calculated as 1*4+2*5+3*6.

This can be extended to more arrays, but with either 2 or more arrays, it is relatively simple in this form. One thing to take note of at the time is that although it SUMs and it PRODUCTs, there is not a * or a + in sight, these operations are intrinsic to SUMPRODUCT. But ... notice the comma, separating the array to be multiplied (semi-colon for our continental colleagues). Remember this detail, it will be touched on again later.

SUMPRODUCT Is SUMIF

Simple so far, but SUMPRODUCT can be 'tricked' into being a conditional function, like SUMIF, but more like SUMIF on steroids. This is what we will in cover more detail in the following discussions.

Let us assume we have some data in A2:A20. We'll start by considering a simple conditional test, such as

=IF(A2="test",1,0)

We could add this formula to B2, copy down as far as B20, then simply sum B2:B20. This would count how many items in A2:A20 are equal to "test". Of course, we could do a simple COUNTIF(A2:A20,"test"), but we will ignore this as we will get much smarter over the course of the discussions.

If we enter =A2:A20="test" in B2, we get #VALUE!. But, and just trust me on this for now, we will actually have an array of TRUE/FALSE values in B2, we just don't see them. (If we array-entered this formula, we would get either TRUE or FALSE, depending on whether A2 was "test" or not - if this means nothing to you, do not worry about it for now).

Now extend this to evaluate that array with SUMPRODUCT, that is

=SUMPRODUCT(A2:A20="test"),

and we get 0, even if there are some values of "test" within the range A2:A20. So SUMPRODUCT doesn't help then? Well yes it can, but we have to do a bit more work yet.

As it stands so far, with the formula =SUMPRODUCT(A2:A20="test") we are trying to sum an array of values of TRUE and FALSE. For obvious reasons, SUM, and by inference SUMPRODUCT, likes to add numbers, so we need to force this array of TRUE and FALSE values into an array of 1s and 0s for the SUM to get a meaningful result. Why 1 and 0, why not 23 or 76 say? As we are simply trying to count the number of cells in A2:A20 equal to the value "test", adding 1 to our total where this is TRUE is effectively counting those matching cells.

Fortunately, there is an easy way to get where we want to be. Under the covers, Excel treats TRUE as a 1, and FALSE as a 0. If we perform some arithmetic operation on those values, we can coerce the array of TRUE/FALSE into an array of 1/0. One way is to multiply the resultant array by 1, that is

=SUMPRODUCT((A2:A20="test")*1)

which effectively multiples every test by 1, giving 1 or 0, and stores these values in the resultant array.

Another way is to add 0

=SUMPRODUCT((A2:A20="test")+0)

which adds 0 to every test, again giving 1 or 0, and stores these values in the resultant array.

There are many other ways, but for now we will just cover one more, the double unary.

=SUMPRODUCT(--(A2:A20="test"))

This negates every test, giving an array of -1s and 0s, and negates it again, giving an array of 1s and 0s. The single unary has the effect of coercing TRUE and FALSE to a number, the second switches the -1s to 1s, thereby not messing up our results.

Summary

We have learnt two things here. The first is that we can include a conditional test with SUMPRODUCT, and this returns an array of values that can be acted upon further. The second thing is that we can coerce the conditions into numbers that can be summed by using an arithmetic operator, such as the double unary, *1, or +0. This is the fundamental principle of all SUMPRODUCT solutions.

So far, so good, but also so simple. We have only shown how to count items matching a given condition within an array, nothing that needs this powerful beast called SUMPRODUCT ... yet.

Next time

We will extend into multiple conditions.

Feel free to make comments on this post, it will help for further posts.

Simon Lloyd
04-12-2009, 11:25 PM
Bob, excellent start and so simple to absorb!

=SUMPRODUCT(--(A2:A20="test"))

This negates every test, giving an array of -1s and 0s, and negates it again, giving an array of 1s and 0s. The single unary has the effect of coercing TRUE and FALSE to a number, the second switches the -1s to 1s, thereby not messing up our results.
Now i understand the use of the unary, can you tell me, as we are SUMming an array for an instance of text does that mean we have no use for the unary if SUMming numbers?, if we do use the unary (i assume we do really as you explained it changes the results from true/false to 0's & 1's) what is it's effect on numbers say if we were looking for -1 etc?

Bob Phillips
04-13-2009, 12:46 AM
Simon,

In the examples shown so far, the coercion was/is needed on the conditional test. As such, it is not material whether the test is for text or for numbers. SO if A2:A20 contained numbers, and we wanted to count those > 10, we would still use some coercion operator, such as

=SUMPRODUCT(--(A2:A2>10))

Is that what you were asking?

Simon Lloyd
04-13-2009, 02:23 AM
Simon,

In the examples shown so far, the coercion was/is needed on the conditional test. As such, it is not material whether the test is for text or for numbers. SO if A2:A20 contained numbers, and we wanted to count those > 10, we would still use some coercion operator, such as

=SUMPRODUCT(--(A2:A2>10))

Is that what you were asking?In a way yes (although i pretty much new the answer before i posted), however i was more interested as to whether the coercion was affected by the numbers that were already -1, 1 and 0.

Like:
=SUMPRODUCT(--(A2:A2=-1))

Bob Phillips
04-13-2009, 02:35 AM
No, it is not affected, because the conditional test still returns TRUE or FALSE, EVEN when the values being tested for are -1, 1, or 0. It is the test that needs coercing, not the values (this is a common misconception that you often see in SP formulae, like so

=SUMPRODUCT(--(A2:A20="x"),--(B2:B20="y"),--(C2:C20))

But that will be covered more in part 2 or 3.

Simon Lloyd
04-13-2009, 07:11 AM
Thanks for the detailed explanation! :)

Zack Barresse
04-13-2009, 09:39 AM
The way I always looked at it, Simon, was if the array in question was a condition of mine, i.e. it would return a boolean result, it would need coercion. If it needs coercion, you need either the *1, +0 or --. Thus if the array doesn't need coercion, then no mathematical coercion is necessary. Not sure if that helps..

Simon Lloyd
04-13-2009, 11:58 AM
Thanks Zack (oops Lawrence!), i understood we could do without using the unary i was just curious to find out if the coercion of the number -1 to -1 by the first unary caused a problem of any kind....evidently not, but i do thank you for your take on the quandry :)

swaatacba
10-22-2009, 05:09 AM
Under the covers, Excel treats TRUE as a 1, and FALSE as a 0. If we perform some arithmetic operation on those values, we can coerce the array of TRUE/FALSE into an array of 1/0. One way is to multiply the resultant array by 1, that is

=SUMPRODUCT((A2:A20="test")*1)

which effectively multiples every test by 1, giving 1 or 0, and stores these values in the resultant array.

How sensible, but I always thought that the numeric values of TRUE and FALSE were 0 and -1 respectively. How did I manage such a basic misconception? Where within Micorsoft's help system does it provide such vital information?

10-22-2009, 07:16 AM
How sensible, but I always thought that the numeric values of TRUE and FALSE were 0 and -1 respectively. How did I manage such a basic misconception? Where within Micorsoft's help system does it provide such vital information?

About SUMPRODUCT() ? or about the numeric values of true and false? If the former, nowhere. If the latter it's just standard, not MS specific. See. (http://www.google.com/search?hl=en&source=hp&q=Numeric+Values+of+true+and+false&btnG=Google+Search&aq=f&oq=&aqi=)

Zack Barresse
10-22-2009, 06:05 PM
Of course, when dealing with VBA it's different as well. False is still = 0, but True returns to -1. This should show it (in your Immediate Window):
?"True = " & clng(true) & ", False = " & clng(false)

The bum part about Microsoft's SUMPRODUCT() (of course a reason for this forum) is that formula was never really meant to do what its main uses are for. Part of the beauty of keeping things open-ended, even if it is unintentional. :)

Bob Phillips
10-23-2009, 05:28 AM
How sensible, but I always thought that the numeric values of TRUE and FALSE were 0 and -1 respectively. How did I manage such a basic misconception? Where within Micorsoft's help system does it provide such vital information?

In most computer languages (all of the ones that I know), including VBA, TRUE is -1, but Excel treats it as 1 for some reason. Actually it treats any non-zero value as TRUE, but an actual TRUE can be coerced to 1.

Bob Phillips
10-23-2009, 05:32 AM
Where within Micorsoft's help system does it provide such vital information?

It doesn't, nor does it explain how to use SUMPRODUCT in this way.

I tell this story often, but it is enlihtening I feel. I was in Redmond at Microsoft one year, and I met up with an Excel help technical writer. This guy had no idea that you could use SUMPRODUCT in this way, it was all news to him.

Bob Phillips
10-23-2009, 05:35 AM
The bum part about Microsoft's SUMPRODUCT() (of course a reason for this forum) is that formula was never really meant to do what its main uses are for.

I don't think that is bum at all. If it had been designed to do (some of these things) you can bet your life it would be far more limited than it is now (just like SUMIF is) precisely because constraints would be designed in.

And of course, it is not actually anything about SUMPRODUCT per se, all of these things (and more) can be done with array SUM formulae.

Zack Barresse
10-24-2009, 12:41 PM
True, it is quite helpful, didn't mean it in any derogatory way. ;)

Bob Phillips
10-25-2009, 10:57 AM
True, it is quite helpful, didn't mean it in any derogatory way. ;)

I should hope so too, you know you can't diss SP with me :devil2:

Zack Barresse
10-26-2009, 11:28 AM
Bob, I would never! :beerchug:

BobBarker
02-25-2011, 12:02 PM
Heh this is cool stuff. Well written guide but I can see it being a confusing subject for complete newbs to this topic. Jumping to the next thread. Hope I'll have more to say there :)

mellowest
03-15-2012, 02:50 AM
Seriously, the best SUMPRODUCT guide I have seen. Totally had an "ah ha" moment while sitting on the loo.

Well done boys.

Bob Phillips
03-15-2012, 06:42 AM
I hope you didn't take your computer in there.

shrivallabha
03-22-2012, 08:46 PM
I hope you didn't take your computer in there.
:mbounce:

skburnwal
09-12-2015, 12:08 PM