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.

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:

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…

Luckily, this can be overcome by a simple trick:

As 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.

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)

Answer:

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

where

How it works?

Again, a picture tells thousand words…

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)

Answer:

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

How it works?

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?

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

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)

Nothing special indeed… It instructs Excel to do the corresponding multiplication row by row, column by column:

Turning into:

Eventually resulting in a 2-D array that is ready for SUMPRODUCT…

Now the calculation is easy.  The following screenshot shows the steps:

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

• Calculate number of a specific day between two dates
• SUM Time in the end of text strings
• Make impossible possible
• CrossTab SUM with 3 variables

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