SUMPRODUCT function in Excel: Calculates the sum of corresponding values

The SUMPRODUCT function is an extremely useful function when you have to deal with a lot of data numbers in Microsoft Excel. Here are the things you need to know about the SUMPRODUCT function in Excel .

Microsoft Excel is a great spreadsheet software. Its benefits are undeniable, especially when it comes to calculation functions. In this article, let's learn about the SUMPRODUCT function in Excel with TipsMake.

As the name suggests, the SUMPRODUCT function is a combination of the SUM and PRODUCT functions in Excel. This function will return the sum of the products of the corresponding ranges or arrays. The default operator here is multiplication, but it can also add, subtract, and divide.

What is the SUMPRODUCT function?

The SUMPRODUCT function in Excel is used to calculate the sum of the products of numeric elements in an array or multiple arrays, different from the PRODUCT function which is used to calculate the product of values.

An array on Excel will be a series of data with consecutive values that the user ranges on the data table. The SUMPRODUCT function is available on all versions of Excel, with similar usage. The detailed usage of the SUMPRODUCT function on Excel will be guided by Network Administrator in the article below.

SUMPRODUCT function syntax

The SUMPRODUCT function has the syntax =SUMPRODUCT(array1, [array2], [array3], …) .

In there:

Array1: the first array argument that the user wants to multiply the numbers and sum. This argument is required.
Array2, Array3: are optional array arguments, in the range from 2 to 255.

The arguments in each array must be the same size and have the same number of elements. In case the elements in the array are not numeric, the SUMPRODUCT function will assume that they are 0 by default.

Notes when using the sumproduct function in Microsoft Excel

The array arguments must have the same dimension. Otherwise, SUMPRODUCT returns the #VALUE value. For example, =SUMPRODUCT(C2:C10,D2:D5) would return an error because the ranges are not of the same dimension.
SUMPRODUCT processes non-numeric array items if they are zeros.
For best results, SUMPRODUCT should not be used with full column references. Consider =SUMPRODUCT(A:A,B:B), which multiplies 1,048,576 cells in column A by 1,048,576 cells in column B before adding them.

Using the basic SUMPRODUCT function in Excel

We will calculate the number of book sales of a store in the example table below. In which, the table gives the number of each type of book and the price of each type in thousands of dong.

SUMPRODUCT function in Excel: Calculates the sum of corresponding values Picture 1

We will enter the calculation formula =SUMPRODUCT(C2:C4,D2:D4) and press Enter to execute the calculation formula.

SUMPRODUCT function in Excel: Calculates the sum of corresponding values Picture 2

The user will then have the store's revenue results for selling the total number of books given above.

SUMPRODUCT function in Excel: Calculates the sum of corresponding values Picture 3

Using the SUMPRODUCT function with conditions

In Excel there is no SUMPRODUCT function combined with IF function, but you can change the formula to calculate the sum of products with conditions.

1. Calculate the sum of similar values

We need to calculate the price of the Vietnamese Book product in the data table.

SUMPRODUCT function in Excel: Calculates the sum of corresponding values Picture 4

Based on the SUMPRODUCT function formula, we have the required formula =SUMPRODUCT(B2:B6*C2:C6*(A2:A6="Vietnamese Books")) , which means that for Vietnamese Books, Excel will multiply the unit price by the corresponding quantity. If other values are encountered, the unit price will be multiplied by 0.

SUMPRODUCT function in Excel: Calculates the sum of corresponding values Picture 5

The result is the total price of the Vietnamese Book product below.

SUMPRODUCT function in Excel: Calculates the sum of corresponding values Picture 6

2. Calculate the total price of books in quantities less than 30.

When the number of books is less than 30, Excel multiplies by the corresponding price, and if it is greater than 30, multiply by 0.

The specific formula for the above condition is =SUMPRODUCT((B2:B6<=30)*(B2:B6)*(C2:C6))

As a result we get the total price of books <30.

Some errors of the SUMPRODUCT function in Excel

Error result is 0

The result error is 0 due to many reasons, it can be because the selected array contains values that the function cannot calculate such as logical functions, text, empty values,.

Then the user needs to change the array of values containing text to an array of values containing numbers.

#VALUE Error

The error occurs when your array arguments are not of the same size.

We will need to double check and correct the array argument in the formula to the same size.

Jessica Tanner

Update 26 May 2025

You should read it

May be interested

VARP function - Functions for calculating variance based on an entire population in Excel
varp function: the function calculates the variance on the entire set, the arguments of the function can only be arrays or references, where the logical values and presented as numbers when typing directly are counted.
DB function - The function calculates the depreciation of assets with specific maturity in Excel
the following article details the db function - the function of depreciation of assets with specific maturity with the use of the method of calculating the declining balance by fixed level.
MODE.MULT function - The function returns a vertical array of the most common values in Excel
mode.mult function: the function returns a vertical array of the most frequently occurring values, or repeating values in an array or data range. support functions from excel 2010 onwards. syntax: mode.mult ((number1, [number2], ...)
How to use the SUMIF function in Excel
the sumif function in excel is a function used to compute values in a specified range. the sumif function can be used for summing cells based on the date, data and text that are connected to the specified area.
GROWTH function - The function calculates the exponential growth value using existing data in Excel
growth function: the function calculates the exponential growth value using existing data. syntax: growth (known_ys, [known_xs], [new_xs], [const])
NPER function - The function calculates the period number of an investment in Excel
calculating interest rates for securities are usually calculated by period. so if you are a stock player, please know this calculation offline to be able to actively calculate the interest received. the following article shows how to use the nper function to calculate the number of periods for an investment.
How to use the SUBTOTAL function in Excel
the subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values in excel
DATEDIF function - The function calculates the number of days, months, years between 2 dates in Excel
datedif function: the function performs the calculation of number of days, months, years between 2 dates in excel. functions are useful in formulas for calculating age numbers. syntax: datedif (start_date, end_date, unit)
TBILLPRICE function - The function calculates the value of treasury bonds by the $ 100 face value in Excel
the following article details the meaning and usage of the tbillprice function - the function of calculating the value of treasury bonds at $ 100 face value in excel.
Coupdays function - Calculates the number of days in a coupon period and contains the settlement date in Excel
the following article details how to use the coupdays function in excel. function meaning: the coupdays function calculates the number of days in the coupon period and contains the settlement date. syntax: coupdays (settlement, maturity, frequency, [basis]).