560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
One of the main reasons to use SUMPRODUCT is to calculate conditional totals based on data, according to a set of criteria.
Suppose that you want to be able to calculate the total number of Oscars for films which:
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.
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:
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!
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:
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!
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.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street