Microsoft Excel

10 minute read

How to Use the Excel SUMPRODUCT Function

Claudia Buckley

Claudia Buckley


Do you know the SUMPRODUCT function in Excel

If not, what you don’t know can cost you in terms of time and effort, because Excel SUMPRODUCT is a powerful, but somewhat less-used function. In many cases, it allows you to do tasks you’re probably doing quite often now, but much more quickly.

Purpose of SUMPRODUCT 

By default, the SUMPRODUCT function multiplies values within ranges or arrays, then finds the sum of those products. However, the formula can be modified to perform addition, subtraction, or division operations instead.

SUMPRODUCT is unlike most other functions because it behaves like an array function without having to press Control + Shift + Enter (CSE). Array functions perform multiple calculations on a value or values within a row or column, all in one entry.


Download your free sumproduct practice file!

Use this free Excel sumproduct file to practice along with the tutorial.


Syntax

To use the default operation (multiplication), the syntax is:

=SUMPRODUCT(array1, [array2], [array3], ...)

Arguments

  • Array1 is the first range or array to be multiplied.
  • Array2 is the second range or array to be multiplied. Array2 and all subsequent arrays are optional.

Return Value

SUMPRODUCT returns a single value which is the sum of the product of the arrays used in the formula.

Basic use

The basic use of SUMPRODUCT is illustrated in the example below.

Excel sumproduct functionTo calculate the amount spent on the grocery items above, the usual way to perform this manually would be to expand each row by multiplying B2 by C2, B3 by C3, and so on, placing the results in column D. Once the amount paid for each product has been calculated, we would then find their total.

Excel simplifies this entire process with SUMPRODUCT. The same multiplication operations are performed in the background: ($1.16 * 5), ($2.50 * 10), ($5.18*100), ($0.88 * 20). But the results are stored in memory, and then added together to get the grand total of $566.40. 

Remarks

  • If only one array is used, SUMPRODUCT will simply add all the values in the array.
  • Arrays must be of the same size for the SUMPRODUCT function to work. 
  • If any array argument contains non-numeric values, they will be treated as zeros.
  • It isn’t a good idea to use SUMPRODUCT with full column references (for example, A:A or B:B). Since Excel columns have over one million rows, you’d be asking Excel to scan through all those rows, which unnecessarily slows down the performance of your worksheet.

Perform other arithmetic operations

To use SUMPRODUCT to perform division, addition, or subtraction on arrays separate each argument with the appropriate arithmetic symbol (/, +, -) for the operation required. After all the operations have been performed, the results are summed as usual.

For example:

=SUMPRODUCT(A1:A3/B1:B3)

will divide the value in A1 by the value in B1, the value in A2 by the value in B2, and the value in A3 by the value in B3, and add the results.

Excel sumproduct function - arithmetic operationsPerforming a subtraction operation would apply the same principle.

Excel sumproduct function - arithmetic operationsOf course, if other arrays are introduced, it’s a good idea to use parentheses so that the operations are performed in the desired order. 

For example:

=SUMPRODUCT(A1:A3-B1:B3*B1:B3)

produces a different result from SUMPRODUCT((A1:A3-B1:B3)*B1:B3)

Excel sumproduct function - arithmetic operationsIn the above formula, Excel performed the multiplication operation first, following the PEMDAS (Parentheses – Exponents – Multiplication – Division – Addition – Subtraction) order of operations rule.

The multiplication of the arrays B1:B3 and B1:B3 was carried out first, then the resulting values were subtracted from A1:A3, to return a value of 18.

Excel sumproduct function - arithmetic operationsWhen the formula is written as above, Excel performs the subtraction equation within parentheses first, then multiplies the resulting values by the B1:B3 array, giving a result of 48.

Calculate weighted average

A fairly common application of SUMPRODUCT is to calculate the weighted average using the following format: SUMPRODUCT(values, weights) / SUM(weights)

Values may be assigned certain weights based on their priority, or the “weight” given to values which total 100%.

In the following example, various weights have been assigned to each test to determine the final score for that subject.

Excel sumproduct function - weighted averageWithout SUMPRODUCT, this problem would likely be solved by multiplying each score by its respective weight and then adding those results to arrive at the overall grade. 

Using the SUMPRODUCT format stated above reduces those calculations to a single calculation in Excel.

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

Excel sumproduct function - weighted average

Advanced uses

SUMPRODUCT can be used in creative ways to do what other functions can do. This is particularly useful when some of these functions aren’t available in the version of Excel you’re using. One example is the UNIQUE function.

The UNIQUE function in Excel is designed to either count the number of distinct values in an array, or it can count the number of values appearing exactly once.

The idea of ‘distinct’ values essentially answers the question, “How many different values are referred to in this list, removing duplicates?”

However, since the UNIQUE function is only available in Excel 365 and Excel online, here is a workaround, using SUMPRODUCT.

Count distinct values in Excel 

The SUMPRODUCT can be used to count distinct values with the format:

=SUMPRODUCT(1/COUNTIF(range, range))

Using this format to count the number of distinct city names from the list of 17 cities in the range below, we would enter:

=SUMPRODUCT(1/COUNTIF(B2:B18, B2:B18))

Excel sumproduct function - count distinct valuesThe result returned is that there are 11 distinct city names. A manual check reveals that this is true, but let’s break the formula down to see how Excel got that result. We’ll start with the inner section of the formula, COUNTIF.

COUNTIF(B2:B18, B2:B18)

As you may already know, we use the COUNTIF function to find out how many times each individual value appears in the specified range. By selecting the range B2 to B18 as both the reference range and the criteria range, we are asking Excel to count how many times each value appears in the array. 

The result in our example would be: {2,1,3,3,1,1,1,3,1,3,2,3,2,2,1,1,3}. Excel stores this array in its memory, and uses that to process the section of the formula which says:

1/COUNTIF(B2:B18, B2:B18)

This simply divides the number 1 by each value from the COUNTIF result in turn. If you take a moment to think about this, what will happen is that values within the array that are equal to 1 will return a value of 1 when divided by 1. 

However, values that are equal to 2 will return a value of 0.5 when divided by 1. Therefore, both values which appear twice in the data set would total 1. This accomplishes our task of counting each distinct value only once.

The SUMPRODUCT portion of the formula:

SUMPRODUCT(1/COUNTIF(B2:B18, B2:B18))

then looks at the results as an array to be added, thereby returning a result of 11 — the total number of all the distinct values within the original data set.

Using SUMPRODUCT to unlock other functions

The array-type functionality of SUMPRODUCT can be used to help ordinary functions to do extraordinary things.

For example, the LEN function, used to count the number of characters in a text string, can only be used for a single cell reference. But what if the values to be counted were spread across several cells, such as in the case below?

Excel sumproduct function - LEN functionThe ordinary way to count the number of characters in this range might be to use the LEN function in cells B1, B2, B3, and B4 and then total those numbers.

LEN(A1)

Excel sumproduct function - LEN functionYou might even think of accomplishing the same thing by using a single entry, like this:

=SUM(LEN(A1),LEN(A2),LEN(A3),LEN(A4))

Excel sumproduct function - LEN FunctionBut did you think of using SUMPRODUCT like this:

=SUMPRODUCT(LEN(A1:A4))

Excel sumproductCombining SUMPRODUCT with LEN allows you to enter a range of cells, which is not a feature of the standard LEN function.

The result of each cell is added since this functionality is built into SUMPRODUCT. This brings the total to 97 without having to use Control + Shift + Enter or Control + Enter.

Learn more

These are just a few examples of how the Excel SUMPRODUCT function can help you get even more out of Excel. 

Check out our Excel courses to learn more problem-solving techniques in Excel. Here are some recommendations:

Free Excel crash course

Learn Excel essentials fast with this FREE course. Get your certificate today!

Start free course
Claudia Buckley

Claudia Buckley

Claudia is a content writer and course instructor at GoSkills. If she's not at work, she's probably tackling a genealogy project.

No comments

LoginSign up