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.

- The SUMPRODUCT function in Excel (this blog)
- Uses of SUMPRODUCT: conditional summing
- 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*).

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:

**=SUMPRODUCT(Oscars,OwlyOscars)**

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.

- The SUMPRODUCT function in Excel (this blog)
- Uses of SUMPRODUCT: conditional summing
- Weighted averages using the SUMPRODUCT function