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

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 ( ).

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

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() 'good if conditions return 1/0Conditions, sum_range

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.

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?

LikeLike

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,

LikeLike

Thanks MF. Why do you remain a mystery?

LikeLiked by 1 person

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

LikeLike

=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)

LikeLiked by 1 person

Git it. Thaks a ton.

LikeLike

Why do you think I remain a mystery?? 🤔

LikeLike

Becoz I don’t find your name anywhere!

LikeLike

Pls have a look at the About 😃

LikeLike

Ok. Thanks.

LikeLike

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.

LikeLike

That is a very good suggestion! Thank you David 😃

LikeLike

Now I notice that there are three ((( after — but only two (( after SIGN.

WIll the formula still work, (with one less (?

LikeLike

I was typing using my phone on the train……. 😅

Try to figure that out. You should be able to do that.

LikeLike

Yes, I’ll.

LikeLike