What is SUMPRODUCT in Excel: Complete Guide

Microsoft Office Suite is one of Microsoft’s most popular software package offerings, with applications for word processing, spreadsheets, and other functions. A part of the Office Suite, Microsoft Excel is a widely used data storage and analysis program. It is a spreadsheet program with various functions and formulas that enables users to perform calculations and analyses on numerical data. 

Unlike its word processor counterpart (Microsoft Word), Excel can be quite challenging to navigate and grasp, mainly because of the numerous formulas, functions, and features it offers.

This article will explore the SUMPRODUCT function in Excel and its uses in simplifying numerical data analysis.

Learn data science courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Explore our Popular Data Science Courses

What is the SUMPRODUCT function/SUMPRODUCT formula in Excel?

The SUMPRODUCT function in Excel returns the summation of products of corresponding arrays or ranges. We can use the function to multiply two or more arrays together and get the sum of products. An array or range in Excel is a collection of selected cells, either rows or columns of values or a combination of rows and columns of values. SUMPRODUCT in Excel is a highly versatile function. Although its default operation is multiplication, we can also use it for addition, subtraction, and division. 

Syntax of the SUMPRODUCT Function in Excel

The syntax for basic use of the Excel SUMPRODUCT function for the default operation (multiplication) is as follows:

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

Example: 

=SUMPRODUCT(B2:B5, C2:C5)

Syntax arguments

The first array argument (array1) is mandatory in the syntax and includes the components we want to multiply then add. However, [array2], [array3], and so on are optional and have values we want to multiply and then add. 

Using other arithmetic operators

If we want to perform other arithmetic operations apart from multiplication, use SUMPRODUCT as usual and replace the commas between the array arguments with the arithmetic operators (+, -, *, /) we wish to use.

Example of SUMPRODUCT Function in Excel

Below is an example showing the basic use of the Excel SUMPRODUCT function:

Source

How to use the SUMPRODUCT function in Excel?

We’ll look at some examples to understand how to use the Excel SUMPRODUCT function. 

Example 1

Suppose we have the following data:

We want to find out the total amount spent. So, we will use the SUMPRODUCT function as follows:

The SUMPRODUCT function performs the following calculation: (10*20) + (20*10) + (15*12) + (5*25) + (10*20) + (6*5) = 935

Example 2

Now, consider the following data:

We want to find out the total sales in the north region. So, we will use the SUMPRODUCT function as follows:

Here, the double negative sign (–) in the syntax converts the TRUE and FALSE values into 0s and 1s. 

Below is a virtual representation of the two arrays as processed by the SUMPRODUCT function if we do not use the double negative signs:

The first array contains the TRUE or FALSE values resulting from the argument B1:B7=”North,” and the second array includes the values of C1:C7. Thus, the SUMPRODUCT function multiplies each item in the first array with the corresponding item in the second array. 

The SUMPRODUCT function will return zero in this state because it treats the TRUE and FALSE values as zeroes. So, we need to convert the items in the first array into numeric values( 0s and 1s). Hence, we use the double negative signs that treat TRUE and FALSE as 1 and 0, respectively.

The result is as follows:

Example 3

Look at the following data:

Here, we will use the SUMPRODUCT function to calculate the weighted average where each value has been assigned a weight (in this case, the quantity). The SUMPRODUCT formula for calculating the weighted average is as follows:

The result is:

Using the SUMPRODUCT Function to Calculate Specific Character Occurences in a Range

Apart from purely numeric calculations, we can also use the SUMPRODUCT formula in Excel to calculate the occurrence of specific characters in a range of cells. The general syntax for it is:

=SUMPRODUCT(LEN(rng)-LEN(SUBSTITUTE(rng,txt,””)))

In the syntax above, rng represents the range of cells containing words, and txt represents the character we want to count. 

Consider the following example:

To count the total number of the character “a”, we will use the syntax:

=SUMPRODUCT(LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,”a”,””)))

The result:

Here, SUBSTITUTE takes away all the “a”s from the text in each cell of the range, and then LEN finds out the text length without the “a”s. The number is then deducted from the initial text length with “a”s.

Since we are using the SUMPRODUCT function, the above calculation results give an array with one item (a number) in each cell of the range. Thus, we have an array of character counts with one count in every cell. The SUMPRODUCT function then adds the numbers in the list and returns the total for all the cells in the range.

Since SUBSTITUTE is a case-sensitive function, it will match the case while performing calculations. So, if we want to count both the lower and uppercase instances of a specific character, we will modify the syntax to convert the text to uppercase before the substitution happens. The modified syntax will be:

=SUMPRODUCT(LEN(rng)-LEN(SUBSTITUTE(UPPER(rng),TXT,””)))

Using the SUMPRODUCT Function to Count Specified Words in a Range

We can use the following SUMPRODUCT formula to count the occurrence of a specific word in a range of cells. The generic syntax is:

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,””)))/LEN(txt))

In the syntax, rng represents the cell range we want to check, and txt is the word or substring we wish to count.

Consider the following example:

To calculate the total count of the word “Jill,” we will use the syntax:

=SUMPRODUCT((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,D2,””)))/LEN(D2))

The result:

SUBSTITUTE removes the substring (Jill) from the initial text for every cell in the array, and then LEN computes the text length minus the substring. The number is then deducted from the initial text length to get the number of characters removed by SUBSTITUTE. The function then divides the number of removed characters by the length of the substring to get the number of times the substring appeared in the initial text (this is the list of items/array).

The SUMPRODUCT function finally adds up all the items in the array to return the total instances of the substring in the range of cells.

Points to Remember While Using the SUMPRODUCT Function

1. The array arguments in the SUMPRODUCT function must have the same dimensions. If not, the function returns the #VALUE! error. An example has been shown below. Here, the ranges (B2:B8, C2:C7) are not the same.

2. The SUMPRODUCT function treats non-numeric array components as if they were zeroes.

3. The SUMPRODUCT function returns the same result as the SUM function on applying a single range.

4. The SUMPRODUCT function accepts up to 255 arguments in Excel 2016, 2013, 2010, and 2007 versions and up to 30 in earlier ones.

5. Logical test components inside arrays create TRUE and FALSE values. Thus, converting them into numeric values (0s and 1s) is common.

Read our popular Data Science Articles

Learn Excel with upGrad Data Analytics Certificate Program

Are you a working professional looking for a break in the data analytics field? Then here’s your chance to learn and train with upGrad’s Data Analytics Certificate Program powered by Fullstack Academy.

Highlights of the 9-month blended program (live+online):

• 200+ learning hours
• Fullstack Academy live training
• Certificate from Caltech
• Tableau and AWS certification preparation
• 1:1 career mentorship sessions
• Peer learning and industry networking
• Comprehensive coverage of data analytics tools and programming languages (including Excel)

Sign up today to book your seat!