SUMPRODUCT in Excel

SUMPRODUCT in Excel

The SUMPRODUCT function in Excel is a powerful and versatile tool that can perform various calculations based on multiple criteria. In this article, we will explain what the SUMPRODUCT function does, how to use it, and some examples of its applications.

What is the SUMPRODUCT function?

The SUMPRODUCT function multiplies the numbers in the specified arrays, and returns the sum of those products. An array is a range of cells supplied as an argument to the SUMPRODUCT function. A product is the output of the multiplication of two numbers.

The syntax of the SUMPRODUCT function is simple and straightforward:

=SUMPRODUCT (array1, [array2], [array3], …)

The function can take up to 255 arrays as arguments, but they must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

For example, if we have two arrays of numbers in cells A1:B3 and C1:D3, we can use the SUMPRODUCT function to multiply them and sum up the results like this:

=SUMPRODUCT (A1:B3,C1:D3)

The formula will return 142, which is the sum of these products:

(210) + (412) + (614) + (816) + (1018) + (1220)

If we only supply one array as an argument, the SUMPRODUCT function will simply sum up the items in the array. For example:

=SUMPRODUCT (A1:B3)

The formula will return 42, which is the sum of these numbers:

2 + 4 + 6 + 8 + 10 + 12

How to use the SUMPRODUCT function

The SUMPRODUCT function can be used for various purposes, such as:

• Calculating weighted averages
• Counting cells that meet multiple criteria
• Summing cells that meet multiple criteria
• Performing logical tests with arrays
• Performing arithmetic operations with arrays

Calculating weighted averages

A weighted average is an average that takes into account the relative importance or frequency of each value. For example, if we have a list of grades and their corresponding weights, we can use the SUMPRODUCT function to calculate the weighted average like this:

=SUMPRODUCT (B2:B6,C2:C6) / SUM (C2:C6)

The formula will multiply each grade by its weight, sum up the products, and divide by the sum of the weights. The result is 82.5, which is the weighted average of the grades.

Counting cells that meet multiple criteria

The SUMPRODUCT function can also be used to count cells that meet multiple criteria, by using logical expressions inside the arrays. For example, if we have a list of orders and we want to count how many orders were made by Texas customers for red products, we can use this formula:

=SUMPRODUCT ( – (A2:A11 = “TX”), – (B2:B11 = “red”))

The formula will compare each cell in column A with “TX” and return TRUE or FALSE. The double negative operator (–) will convert TRUE to 1 and FALSE to 0. Similarly, the formula will compare each cell in column B with “red” and return 1 or 0. Then, the SUMPRODUCT function will multiply these two arrays and sum up the results. The final result is 3, which is the number of orders that meet both criteria.

Summing cells that meet multiple criteria

Similarly, the SUMPRODUCT function can be used to sum cells that meet multiple criteria, by multiplying an array of values by an array of logical expressions. For example, if we want to sum up the sales amount for orders made by Texas customers for red products, we can use this formula:

=SUMPRODUCT (C2:C11, – (A2:A11 = “TX”), – (B2:B11 = “red”))

The formula will multiply each cell in column C by 1 or 0 depending on whether the corresponding cells in columns A and B meet both criteria. Then, the SUMPRODUCT function will sum up these products. The final result is 400, which is the total sales amount for orders that meet both criteria.

Performing logical tests with arrays

The SUMPRODUCT function can also be used to perform logical tests with arrays, by using comparison operators or other functions inside the arrays. For example, if we want to check whether all values in an array are positive, we can use this formula:

=SUMPRODUCT ( – (A1:A10 > 0)) = COUNTA (A1:A10)

The formula will compare each cell in column A with zero and return 1 or 0. Then, the SUMPRODUCT function will sum up these numbers. If all values are positive, the sum will be equal to the count of non-empty cells in column A. Otherwise, it will be less than that.

Performing arithmetic operations with arrays

The SUMPRODUCT function can also be used to perform arithmetic operations with arrays, by replacing the commas separating the arrays with the arithmetic operators. For example, if we want to calculate the difference between two arrays of numbers, we can use this formula:

=SUMPRODUCT (A1:A10 - B1:B10)

The formula will subtract each cell in column B from the corresponding cell in column A, and sum up the results. This is equivalent to using the SUM function with an array formula, but without entering the formula with Ctrl + Shift + Enter.

Examples of the SUMPRODUCT function

Here are some more examples of how the SUMPRODUCT function can be used in Excel:

• To calculate the total commission for each salesperson based on their sales and commission rate, use this formula:

=SUMPRODUCT (B2:B11,C2:C11,D2:D11)

• To calculate the average price per unit sold for each product, use this formula:

=SUMPRODUCT (C2:C11,D2:D11) / SUMIF (B2:B11,B2,C2:C11)

• To calculate the percentage of orders that were delivered on time, use this formula:

=SUMPRODUCT ( – (E2:E11 = “Yes”)) / COUNTA (E2:E11)

• To calculate the standard deviation of an array of numbers, use this formula:

=SQRT (SUMPRODUCT ((A1:A10 - AVERAGE (A1:A10))^2) / COUNT (A1:A10))

Conclusion

The SUMPRODUCT function in Excel is a useful and flexible tool that can handle various calculations based on multiple criteria. It can multiply and sum arrays, count and sum cells that meet multiple criteria, perform logical tests and arithmetic operations with arrays, and more. By understanding how the SUMPRODUCT function works, you can use it to solve many problems in Excel. For more information and examples of the SUMPRODUCT function, you can refer to these sources¹²³⁴.

Tweet