SUMPRODUCT in Excel (Table of Contents)
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 –
