The amazing SUMPRODUCT

Advanced use of SUMPRODUCT for multi-conditional SUM

In the previous posts, we talked about the basic of SUMPRODUCT, the behaviors of logical  values (i.e. TRUE/FALSE) when they are put into basic mathematics operations.  These set the foundation to understand the advanced use of SUMPRODUCT for multi-conditional SUM.

Indeed, the construction is simple:

=SUMPRODUCT(Conditions, sum_range)

What we need to be careful is to set the conditions correctly.

In short, multiplying arrays of TRUE/FALSE would return a single array of 1/0 simulating “AND” condition; while adding arrays (with a little trick) simulates an “OR” conditions.

Excel Tips - logic gate 1

Want more examples to illustrate?

You may download a sample File to follow through.

AND conditions

To simulate “AND” conditions into SUMPRODUCT is quite straight forward:Excel Tip - The amazing SUMPRODUCT 1

Simply multiplying the conditions together would do.  As only one single incidence of False (0) would give you a product of False(0).  Put it into other words, you will only get a result of TRUE (1) only if  all conditions are TRUE (1).

OR conditions

However, we cannot simply adding arrays of conditions for “OR” conditions.  The following screenshot illustrate the reason: Double counting…

Excel Tip - The amazing SUMPRODUCT 2

Luckily, this can be overcome by a simple trick:

Excel Tip - The amazing SUMPRODUCT 2.1As you see, the result of (B10:B13 + D10:D13) is an array of {2;1;1;0}.  The simple trick is to wrap it with a logical test >0,

=--((array1 + array2)>0)  gives
=--({2;1;1;0}>0)  gives
=--(TRUE;TRUE;TRUE;FALSE)  gives
={1;1;1;0} instead of {2;1;1;0}

which will return an array of 1/0 only, which avoid the double counting issue.

Excel Tip - The amazing SUMPRODUCT 2.2

Make sense?

Well… this is only the introduction of this post.  Are you ready to dive into details?

Let’s practise with more examples:

Multi-conditional SUM on a vertical table

Example 1:  How many units of Pumpkin is sold by Jack?  (AND condition)

Excel Tip - The amazing SUMPRODUCT 3.1

Answer:

=SUMPRODUCT((Sales_Manager=$A$14)*(Item=$B$14),Units)

where

Sales_Manager =$A$2:$A$10
Item =$B$2:$B$10
Units =$C$2:$C$10

How it works?

Again, a picture tells thousand words…
Excel Tip - The amazing SUMPRODUCT 3.2

In short,

  • (Sales_Manager=$A$14)*(Item=$B$14) are the conditions (array1);
    when the conditions are met, SUM the corresponding value from Units (array2)

Tip: To make the conditions more “readable”, enclose each condition with a pair of brackets ( ).

Example 2:  What is the units sold by Jack or is Pumpkin ?  (OR condition)

Excel Tip - The amazing SUMPRODUCT 4.1.PNG

Answer:

=SUMPRODUCT(--(((Sales_Manager=$A$14)+(Item=$B$14))>0),Units)

How it works?

Excel Tip - The amazing SUMPRODUCT 4.2.PNG

The logic is basically the same as Example 1.  Just that we need extra steps to avoid double counting issues mentioned before.

In short,

  • –(((Sales_Manager=$A$14)+(Item=$B$14))>0) are the conditions (array1); when the conditions are met, SUM the corresponding value from Units (array2)

Tip: We may skip the double negative part if you are familiar with SUMPRODUCT… Get the hint at the end of this post

 

Multi-conditional Cross-tab SUM

Example 3:  How many units of Potato are sold by Scrump in Feb?

Excel Tip - The amazing SUMPRODUCT 5.1.PNG

Answer:

=SUMPRODUCT((Sales_Manager=$A$14)*(Item=$B$14)*(Month=$C$14),Sum_range)
Simply multiplying the extra condition on the row (i.e. Month=$C$14)

where

Sales_Manager =$A$2:$A$10
Item =$B$2:$B$10
Month =$C$1:$E$1
Sum_range =$C$2:$C$10

How it works?

First, we need to understand how a vertical array is multiplied by a horizontal array.

(Sales_Manager=$A$14)*(Item=$B$14)*(Month=$C$14)

Excel Tip - The amazing SUMPRODUCT 5.2.1.PNG

Nothing special indeed… It instructs Excel to do the corresponding multiplication row by row, column by column:Excel Tip - The amazing SUMPRODUCT 5.3

Turning into:Excel Tip - The amazing SUMPRODUCT 5.3.1.PNG

Eventually resulting in a 2-D array that is ready for SUMPRODUCT…Excel Tip - The amazing SUMPRODUCT 5.4.PNG

Now the calculation is easy.  The following screenshot shows the steps:Excel Tip - The amazing SUMPRODUCT 5.5.PNG

In short,

  • (Sales_Manager=$A$14)*(Item=$B$14)*(Month=$C$14) are the conditions (array1); when the conditions are met, SUM the corresponding value from Sum_range (array2)

IMPORTANT: The arrays fed into the SUMPRODUCT must be of equal size!

HINT:

Do you remember… Only array1 is required by SUMPRODUCT, while array2 to array255 are optional?

Meaning that we can combine everything into a single array by replacing the “,” with “*”.

=SUMPRODUCT(Conditions*sum_range) 'good if conditions return TRUE/FALSE
gives the same result as 
=SUMPRODUCT(Conditions, sum_range) 'good if conditions return 1/0

As if there are different ways to convert TRUE/FALSE into 1/0.  It’s up to you which way you find most comfortable with.

Up to now, I hope you have a better understanding on how SUMPRODUCT perform multi-conditional SUM (and COUNT even though I did not talk about it explicitly).  If you have understood the logic so far, you should be able to take SUMPRODUCT to the next level.

Previous blog posts involving advanced use of SUMPRODUCT

How do you use SUMPRODUCT?  Please share with us by leaving comments.

Advertisements
This entry was posted in Formula and tagged , . Bookmark the permalink.

2 Responses to The amazing SUMPRODUCT

  1. David N says:

    You can also simply use the SIGN function as a wrapper on any OR summation to avoid double counting. SIGN turns any positive number into 1 and leaves any zero as 0.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s