562 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 one 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.
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.
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.
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.
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:
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.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street