SUMPRODUCT Function in Excel

SUMPRODUCT in Excel (Table of Contents)

• SUMPRODUCT in Excel
• SUMPRODUCT Formula in Excel
• How to Use SUMPRODUCT Function in Excel?

SUMPRODUCT in Excel

• SUMPRODUCT function: it’s used as a worksheet function in excel. It’s an in-built function in excel that is categorized under math/trig function.
• It can be integrated with other excel functions.
• It initially multiplies then adds the values of the input arrays.
• It comes handy for calculating data with multiple criteria, to compare data in multiple ranges & to analyze data of multiple arrays.

Definition

Returns the sum of the products of arrays or corresponding ranges

 i.e. It multiplies the range of array or corresponding cells and returns the sum of a products

SUMPRODUCT Formula in Excel

The SUMPRODUCT Formula in Excel is as follows:

 

Here the first array i.e. array1 is compulsory argument or parameter and remaining n numbers of arrays as optional argument i.e. [array2],[array3],…)

Compulsory argument:

array1: is the first range or array that you want to multiply, and then add.

Optional argument:

[array2]: The second range or array will be multiplied then added.

[array3]: From third argument onwards range or arrays that will be multiplied & then added. 

Note:  

• The maximum number of arrays that can be entered is 255 &
• A minimum number of arrays should be 2 in sumproduct function.
• The optional arguments are always represented by square brackets.
• Array or Ranges of cells must have the same number of columns or rows. 

How to Use SUMPRODUCT Function in Excel?

This SUMPRODUCT is very simple easy to use. Let us now see how to use the SUMPRODUCT Function in Excel with the help of some examples.

Example #1 – Basic use of  SUMPRODUCT Function

In the below-mentioned example, suppose you have the following data range where the table contains Pharma products, its quantity & price

Here, Column D contains Quantity of product in cells D8:D14, Column C contains Price in cells E8:E14, I need to find out the total sales

For that, I need to multiply the quantity by price for each item and then add

SUMPRODUCT function is applied in an E15 cell to get total sales

Select the cell E15 where SUMPRODUCT function needs to be applied.

Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “SUMPRODUCT” in the search for a function box, SUMPRODUCT function will appear in select a function box. Double click on SUMPRODUCT function.

A dialog box appears where arguments (array1,[array2]) for SUMPRODUCT function needs to be filled or entered. i.e. =SUMPRODUCT(D8:D14,E8:E14)

Here, SUMPRODUCT internally has multiplied each element of both the range of cells or arrays and then added it to one another

i.e. =D8*E8 + D9*E9 + D9*E9 + D10*E10 + D11*E11 + D12*E12 + D13*E13 + D14*E14

Here, SUMPRODUCT returns the output or result value i.e. 42873

Example #2 – SUMPRODUCT Function along with mathematical operators

SUMPRODUCT can be used with specific user-defined mathematical operators, instead of default multiplication operation E.G.  +, -, X, & /

Just comma is replaced in SUMPRODUCT function with specific mathematical operators

In the above table, I have data range where the table contains January & February month Sales of products.

I need to find out the total sales, SUMPRODUCT function in applied in cell I15

i.e. =SUMPRODUCT(H9:H13+I9:I13)  Here Just comma is replaced with mathematical operators i.e. addition symbol (+)

Here, SUMPRODUCT internally has added each element of both the range of cells or arrays

It returns the output value as 1417 i.e. Total sales of both months

Example #3 – SUMPRODUCT Function along with Criteria i.e. with operators

Let’s look out how the SUMPRODUCT function works with the conditional operator “=” equals to

In the below-mentioned example, Data table (N9:P16) contains a region in column O & Subscribers in column P

Here Using SUMPRODUCT function, I need to find out total Subscribers in a south region only

i.e. =SUMPRODUCT((O9:O16=”South”)*(P9:P16))Here in the first array. In region range, need to add conditional operator “=” equals to i.e. = “South” because we need to find out, total Subscribers in the south region

The subscribers of the south region get added up as a result of this conditional operator

SUMPRODUCT search the region “South” in the defined range & will only do the SUM for the values of ‘south’ region only & It returns the output value as 3541 i.e. Total number of subscribers in the south region

Example #4 – SUMPRODUCT Function with Excel Function

For brand names in column B, I need to find out total characters in a specific range.

Prior to SUBPRODUCT function, let’s check how Len function works

LEN function returns the length of the specified string. The syntax for LEN function: =LEN(text)   In the below mention an example, I need to find out the length of string for the word “ITECH ANALYTICS”, Here the LEN function is applied in cell “G21” by using formula =LEN(F21).

It will return an output value as 15

LEN function integrated with the SUMPRODUCT function

Now, let’s combine SUBPRODUCT FUNCTION along with LEN, it calculates the length of all the characters in the data range or array (Brand name) by cell wise and then adds them all

It returns the output value as 43

Example #5 – SUMPRODUCT with sum function to calculate a weighted average

In this example. Let’s imagine, a case where I have a data of inventory of products at a shoe shop. The cost of various products such as red tape, Reebok, Nike, Adidas, Fila, & other brands, with their corresponding quantity, is available in the data range. Here I need to calculate the average cost of a product in my shop. The column H contains price in cells H23:H32 and Column I contain quantity in cells I23:I32.

The formula OR syntax to calculate the required weighted average will be

=SUMPRODUCT (price, quantities) / SUM(quantities)

i.e. =SUMPRODUCT(H23:H32, I23:I32)/SUM(I23:I32)

The OUTPUT value or result will give the average cost of all the shoe products in that shop is

Things to Remember About the SUMPRODUCT Function

• All range or arrays must have the same number of rows and columns.
• If there is no numeric value in the range or array, these values will be treated as 0.
• If the range or arrays in the formula should have the same number of rows and columns, otherwise it will return #VALUE! Error.
• Sumproduct does not allow wildcard characters, wildcard characters (?, * ) cannot be used in the SUMPRODUCT Function in Excel.

Recommended Articles

This has been a guide to SUMPRODUCT Function in Excel. Here we discuss the SUMPRODUCT Formula in excel and how to use SUMPRODUCT Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

1. Use of EVEN Function in Excel
2. SUMIF Function in Excel – Ms Excel
3. Guide to HLOOKUP Function in Excel
4. INDIRECT Function in Excel