Programs

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:

SUMPRODUCT Function in Excel

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:

sum product examples

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

SUMPRODUCT Function in Excel example 2

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

sum product example

Example 2

Now, consider the following data:

Sum product example

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

sum product examples

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:

sum product examples

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.

Sum Product Examples

The result is as follows:

Sum product examples

Example 3

Sum Product Example

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:

Sumproduct Example

The result is:

Sum product examples

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:

Sumproduct example

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

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

sumproduct examples

The result:

sumproduct examples

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:

sum product examples

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

sum product examples

The result:

sum product examples

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.

sum product examples2. The SUMPRODUCT function treats non-numeric array components as if they were zeroes.

sum product examples

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

sum product examples4. 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!

Is there a limit on SUMPRODUCT in Excel?

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

What is the difference between SUMIFs and SUMPRODUCT?

While SUMPRODUCT calculates the total value of the products from multiple arrays or ranges of the same dimensions, SUMIFS sums cells that meet various criteria and occur in a single range. Thus, SUMPRODUCT is mathematical calculation-based, whereas SUMIFS is more logic-based

Why does SUMPRODUCT return value?

same dimension or if one or more cells in the selected range contain text.

 

Want to share this article?

Prepare for a Career of the Future

Leave a comment

Your email address will not be published. Required fields are marked *

Leave a comment

Your email address will not be published. Required fields are marked *

×
Get Free career counselling from upGrad experts!
Book a session with an industry professional today!
No Thanks
Let's do it
Get Free career counselling from upGrad experts!
Book a Session with an industry professional today!
Let's do it
No Thanks