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.

  1. The SUMPRODUCT function in Excel (this blog)
  2. Uses of SUMPRODUCT: conditional summing
  3. Weighted averages using the SUMPRODUCT function

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

Our 20 films

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

Data selected including column headings

First select the data, including the column headings.

 

Now press SHIFT + CTRL + F3 to create range names:

Creating range names from selection

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:

Selecting a named range

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:

Total rating column

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:

=SUMPRODUCT(Oscars,OwlyOscars)

Here's the formula entered into an Excel cell:

SUMPRODUCT formula for two ranges

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.

This blog has 0 threads Add post