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