SUMPRODUCT in Excel (Table of Contents)
SUMPRODUCT in Excel
Sumproduct function in excel is used when we have 2 or more sets of values in the form of a table, and we need to calculate the multiplication or product of those numbers; simultaneously, we need to find the sum of those values. This complete process can be done by applying the Sumproduct function, which will give the sum and product of the selected array at the same time in a cell. It 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 a compulsory argument or parameter and the remaining n numbers of arrays as an 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 the third argument onwards range of arrays 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 the sum-product function.
- Square brackets always represent the optional arguments.
- 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
In the below-mentioned example, suppose you have the following data range: the table contains Pharma products, their quantity & price.
Here, Column D contains the Quantity of products in cells D8:D14; Column C contains Price in cells E8:E14; I need to find out the total sales. I need to multiply the quantity by price for each item and then add the SUMPRODUCT function is applied in an E15 cell to get total sales.
Select the cell E15 where the SUMPRODUCT function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “SUMPRODUCT” in the search for a function box, the SUMPRODUCT function will appear in select a function box. Double click on the 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 can be used with specific user-defined mathematical operators, instead of default multiplication operation, E.G. +, -, X, & /
Just comma is replaced in the SUMPRODUCT function with specific mathematical operators.
I have a data range in the above table where the table contains January & February month Sales of products. I need to find out the total sale; SUMPRODUCT the T function is 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
Let’s look out how the SUMPRODUCT function works with the conditional operator “=” equals to. In the below-mentioned example, the Data table (N9:P16) contains a region in column O & Subscribers in column P
Here Using the SUMPRODUCT function, I need to find out the total Subscribers in a south region only.
i.e. =SUMPRODUCT((O9:O16=”South”)*(P9:P16))Here in the first array. In region range, we 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
For brand names in column B, I need to find out the total characters in a specific range.
Prior to the SUBPRODUCT function, let’s check how the Len function works
LEN function returns the length of the specified string. The syntax for LEN function: =LEN(text) In the below mention 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
In this example. Let’s imagine a case where I have 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. 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
- All ranges 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 an excel example and downloadable excel templates. You may also look at these useful functions in excel –
