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.
The SUMPRODUCT function in Excel
This blog attempts to explain exactly how the SUMPRODUCT function works, as well as giving lots of advanced tips on its use.
You can download the workbook used for this blog here.
Our example: 20 fairly random films
For this blog, we'll use this example of 20 more-or-less random films (note to American readers - a film is a quaint English word for a movie).
The 20 films used for this example.
Background information: range names
Throughout this blog, I've assumed that someone has created a range name for each column. You can create range names yourself as follows. First select all of the data (but excluding the subtotals):
First select the data, including the column headings.
Now press SHIFT + CTRL + F3 to create range names:
Make sure you only have Top row selected, then select OK.
You can now click on the arrow next to the Name Box to test whether your ranges have been created:
Here we've selected the Released range as an example, which includes all of the data in column C apart from the title.
You can use SUMPRODUCT without range names but, as always in Excel, they make everything easier to understand and use.
What the SUMPRODUCT function does
The SUMPRODUCT function multiplies two blocks of data together cell by cell, and calculates the sum of the total.
Mathematicians know this as the scalar product of two vectors (although they also don't go to many good parties ...).
Consider the coloured column below:
The total rating for each film is the number of Oscars won, multiplied by the Wise Owl rating. On this basis The Matrix shows (correctly) as the best film ever made.
The figure 151 at the bottom is composed of the following numbers:
- 4 * 5 (the product of the two ratings for A Beautiful Mind); plus
- 2 * 7 (the product of the two ratings for Apollo 13); plus ...
- ... plus ...
- 11 * 0 (the product of the two ratings for Titanic).
Another way to calculate this total without needing to include column G above is as follows:
Here's the formula entered into an Excel cell:
The formula multiplies pairs of cells and sums them to get the total rating for all films.
Now that you know what the SUMPRODUCT function does, let's look at a couple of examples of how you might use it, beginning with conditional summing.