Updated May 2, 2023
SUMPRODUCT in Excel
The 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. To apply the Sumproduct function, you can simultaneously calculate the sum and product of a selected array 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 you want to multiply and then add.
Optional argument:
- [array2]: The second range or array will be multiplied and 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 and 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 the Price in cells E8:E14; I need to find out the total sales. I need to multiply the quantity by the price for each item and then add the SUMPRODUCT function applied in an E15 cell to get total sales.
Select 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, and the SUMPRODUCT function will appear in the select a function box. Double-click on the SUMPRODUCT function.
A dialog box appears where arguments (array1,[array2]) for SUMPRODUCT function need 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 operations, E.G. +, -, X, & /
Just a comma is replaced in the SUMPRODUCT function with specific mathematical operators.
I have a data range in the above table containing January & February 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 a 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 at 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
Using the SUMPRODUCT function, I need to find only the total Subscribers in a south region.
i.e. =SUMPRODUCT((O9:O16=”South”)*(P9:P16))Here in the first array. In the region range, we need to add conditional operator “=” equals to, i.e. = “South” because we need to find out the total Subscribers in the south region
The conditional operator adds up the subscribers in the south region.
SUMPRODUCT searches the region “South” in the defined range & will only do the SUM for the values of the ‘south’ region & 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 the total characters in a specific range.
Before the SUBPRODUCT function, let’s check how the Len function works
LEN function returns the length of the specified string. The syntax for the LEN function: =LEN(text) In the below-mentioned example, I need to find out the length of the string for the word “ITECH ANALYTICS”, Here the LEN function is applied in cell “G21” by using the 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 on the 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 product cost in my shop. Column H contains the price in cells H23:H32, and Column I contain the 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.
- The Sumproduct function treats any non-numeric values in the range or array 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, an Excel example, and downloadable Excel templates. You may also look at these useful functions in Excel –
