This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

A blog showing how to use the SUMPRODUCT function Part two of a three-part series of blogs
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

A blog showing how to use the SUMPRODUCT function

Part two of a three-part series of blogs

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

In this blog

Our example of conditional totalling
Without SUMPRODUCT (what's happening behind the scenes)
Syntax of the SUMPRODUCT function - and why it doesn't work at first
Two solutions
Using wildcards for text filtering

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:

Showing number of films for different criteria

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)

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 TRUEs and FALSEs, 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)

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)

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.

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

Some other pages relevant to the above blogs include:

Microsoft Excel classes

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Uses of SUMPRODUCT: conditional summing

Our example of conditional totalling

Without SUMPRODUCT (what's happening behind the scenes)

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

Two solutions

Using wildcards for text filtering

Head office

London

Manchester