## 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?

### 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

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

### `=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… 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 ( ).

### `=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? ```=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)` Nothing special indeed… It instructs Excel to do the corresponding multiplication row by row, column by column: 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

How do you use SUMPRODUCT?  Please share with us by leaving comments. An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , . Bookmark the permalink.

### 15 Responses to The amazing SUMPRODUCT

1. Sandeep Kothari says:

Superb!
will you please demonstrate this “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.”
What SIGN is David referring?

Like

• MF says:

Hi Sandeep,
SIGN is a function that returns positive number as 1, zero as 0, negative number as -1.
You may find more about the SIGN function here:
https://support.office.com/en-us/article/sign-function-109c932d-fcdc-4023-91f1-2dd0e916a1d8
Cheers,

Like

• Sandeep Kothari says:

Thanks MF. Why do you remain a mystery?

Liked by 1 person

• Sandeep Kothari says:

I followed the link & understood the SIGN function. Will be graeful if demonstrate its usage with an example in SUMPRODUCT function.

Like

• MF says:

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

For the above, try to change to
=SUMPRODUCT(SIGN
(
(Sales_Manager=\$A\$14)+(Item=\$B\$14)
),
Units)

Liked by 1 person

• Sandeep Kothari says:

Git it. Thaks a ton.

Like

• MF says:

Why do you think I remain a mystery?? 🤔

Like

• Sandeep Kothari says:

Becoz I don’t find your name anywhere!

Like

• MF says:

Pls have a look at the About 😃

Like

• Sandeep Kothari says:

Ok. Thanks.

Like

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

• MF says:

That is a very good suggestion! Thank you David 😃

Like

• Sandeep Kothari says:

Now I notice that there are three ((( after — but only two (( after SIGN.
WIll the formula still work, (with one less (?

Like

• MF says:

I was typing using my phone on the train……. 😅
Try to figure that out. You should be able to do that.

Like

• Sandeep Kothari says:

Yes, I’ll.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.