BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

The SUMPRODUCT function is one of the hardest ones to understand in Excel, but it's also pretty useful. This blog gives clear examples of how to use SUMPRODUCT for conditional sums, weighted averages and the like.

- The SUMPRODUCT function in Excel
- Uses of SUMPRODUCT: conditional summing (this blog)
- Weighted averages using the SUMPRODUCT function

Posted by Andy Brown on 01 November 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Uses of SUMPRODUCT: conditional summing

One of the main reasons to use **SUMPRODUCT** is to calculate conditional totals
based on data, according to a set of criteria.

## Our example of conditional totalling

Suppose that you want to be able to calculate the total number of Oscars for films which:

- Were certificate
**15**, released in or after the year**2000**and not made by**Universal Pictures**; or - Were certificate
**12A**, released in or after**2005**and not made by**Warner Brothers**; or - Were certificate
**U**and released in or after**2002**.

You may have many other combinations of criteria you want to test! You want to create a formula and copy it down to fill the coloured cells below:

We'll enter a formula in
**D2** and copy it down.

Note that you could use the **SUMIFS **function to solve this
problem, but it would be messy and difficult to create and understand.

## Without SUMPRODUCT (what's happening behind the scenes)

Here's how the **SUMPRODUCT** function will calculate the number
7 in the first row below:

The total number of Oscars for certificate
**15 **films not made by **Universal Pictures
**from **2000** onwards is 7.

The function will calculate for each film:

- Whether it was certificate
**15**; - Whether it was released on or after
**2000**; and - Whether it was not made by
**Universal Pictures**.

You can imagine the results by colouring the cells which meet the conditions:

Colouring the cells satisfying the criteria for each column clearly shows that only
**Black Hawk Down **and **Gladiator **satisfy all 3 of the criteria. The sum of their Oscars is 2 + 5 = 7.

This is what **SUMPRODUCT** does - but it does it separately for
every row of conditions. It's exhausting just thinking about it!

## Syntax of the SUMPRODUCT function - and why it doesn't work at first

The **SUMPRODUCT** function can multiply and sum as many arrays
as you like:

You can have up to 30 arrays - enough for most people!

In our case, we want the arrays to be columns of 0s and 1s, according to whether a condition has been satisfied:

Here we're showing a 0 whenever a condition is not true, or a 1 whenever it is false.

This formula would be entered as below:

You'd insert this formula in the selected cell then copy it down.

So the **SUMPRODUCT **function would be:

**=SUMPRODUCT(**

(Certificate=A2),

(Released>=B2),

(Studio<>C2),

Oscars)

(Certificate=A2),

(Released>=B2),

(Studio<>C2),

Oscars)

That is, create 3 arrays of 0s and 1s according to whether:

- the certificate is the one in cell
**A2**; - the year released is after the year entered in cell
**B2**; and - the studio isn't the one entered in cell
**C2**.

However, this doesn't work! The reason is that instead of returning 1s
and 0s, the arrays return **TRUE**s and **FALSE**s,
and these aren't always treated as 1 and 0 in formulae. There are two
possible solutions.

## Two solutions

The first solution is to force Excel to treat the results returned for each
cell of our arrays as 1 and 0 (rather than **TRUE** or **
FALSE**), by converting each to a negative number and then converting it
back again! The formula now is:

**=SUMPRODUCT(**

--(Certificate=A2),

--(Released>=B2),

--(Studio<>C2),

Oscars)

--(Certificate=A2),

--(Released>=B2),

--(Studio<>C2),

Oscars)

Notice the two minus signs in front of each logical condition, to ensure that the results are treated as 1s and 0s.

An alternative solution would be to create a single formula to sum, by missing out the commas and effectively creating a single argument giving a (complicated) single logical condition:

**=SUMPRODUCT(**

(Certificate=A2)*(Released>=B2)*(Studio<>C2)*Oscars)

(Certificate=A2)*(Released>=B2)*(Studio<>C2)*Oscars)

Which of these two solutions you prefer is up to you!

## Using wildcards for text filtering

Suppose that you want to find all of the films which were made by **
20th Century Fox**. Here are 3 ways to do this using **
SUMPRODUCT**:

The functions are explained in more detail below.

What these three conditions test, in turn, is shown in the following table:

Criteria | Tests |
---|---|

Start |
Whether each cell in the Studio range begins with the text
entered in cell B2 (ie 20th). |

End |
Whether each studio ends with the text entered in cell B3 (ie
Fox). |

Middle |
Whether each studio contains the text entered in cell B4 (ie
the word Century). The function works by searching within
the studio name for the text entered, and testing whether this returns an error
because the text isn't found. |

Note that as far as this owl can tell there is no way to use wildcards such
as * (to represent a series of characters) or ? to represent a single character,
although these can be used for functions like **SUMIF**.

Having looked at one use of the **SUMPRODUCT** function, I'll
now take a brief look at another - using it to give weighted averages.

- The SUMPRODUCT function in Excel
- Uses of SUMPRODUCT: conditional summing (this blog)
- Weighted averages using the SUMPRODUCT function