This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

In this blog

Our example: 20 fairly random films
Background information: range names
What the SUMPRODUCT function does

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.