SUMPRODUCT Formula

The article in this webpage describes how to use the SUMPRODUCT formula in Excel with meaning, syntax, detailed explanation and examples.

What is SUMPRODUCT Formula?

The SUMPRODUCT formula in Excel returns the sum of the products of corresponding arrays or ranges. The SUMPRODUCT formula in Excel is a part of Math and Trig formulas.

The SUMPRODUCT formula in Excel is used to calculate the weighted average of a given array. It multiplies the corresponding values of a specified array or ranges and returns the sum of the products. The default calculation of SUMPRODUCT formula is multiplication, but addition, subtraction or division can also be done.

The SUMPRODUCT replaces the commas after the arrays with the arithmetic operators (*, /, +, -). After all the operations are performed, the results are summed in this formula. You should use parentheses to group the array or ranges and control the order of arithmetic operations.

This SUMPRODUCT formula is very flexible with many uses. It is a versatile formula that can be used for a variety of tasks, such as calculating weighted averages, analyzing datasets, and performing complex mathematical operations. In case you are getting the error in the formula, check the examples and notes shown in this tutorial to fix the error easily.

How to use SUMPRODUCT Formula in Excel with Examples?

If you’ve ever found yourself grappling with large datasets and searching for an efficient way to perform calculations across multiple ranges, then the SUMPRODUCT formula is your secret formula.

In this tutorial page, we’ll demystify the SUMPRODUCT formula and show you how to wield its capabilities like a pro. Whether you’re a seasoned spreadsheet wizard or a beginner just dipping your toes into the world of Excel, this tutorial will break down the SUMPRODUCT formula into simple, easy-to-understand steps.

Get ready to supercharge your Excel skills as we explore the versatility of the SUMPRODUCT formula and uncover its potential to streamline your data analysis tasks. Let’s dive in and discover how this formula can become your go-to tool for multiplying and summing values with ease!

SUMPRODUCT Syntax

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

The arguments for SUMPRODUCT formula are:

• array1 (required argument) – This is the first range whose values you want to multiply and then add.
• [array2], [array3],…  (optional arguments) – This is the subsequent ranges (from 2 to 255) whose values you want to multiply and then add.

Important Notes

• Array or Ranges: In SUMPRODUCT formula, all the array arguments or ranges must have the same dimensions. The arrays or ranges within the SUMPRODUCT can be of different sizes, but they must have the same number of rows and columns. The individual arrays or ranges, and even mathematical operations within the arrays can be used. Otherwise it will return as #VALUE! Error.

• Weighted Average: The SUMPRODUCT is commonly used to calculate the weighted average by multiplying each value by its corresponding weight and then summing the results.

• Mathematical Operators: Besides the multiplication, you can use other operators within the arrays or ranges, allowing for versatile calculations.

• Numeric Values: The SUMPRODUCT formula accepts only the numeric values. If there are any non-numeric values in the given array or ranges, then those are treated as Zero (0).
• Entire Column or Row: It is best practice to avoid full row or column references (such as A:A or A:B) in the SUMPRODUCT formula. For example, =SUMPRODUCT(A:A,B:B) will multiply the 1,048,576 cells in column A by the 1,048,576 cells in column B and then it will add. The maximum rows or columns depend on which Excel version you are using.
• One Array: There must be minimum one array in SUMPRODUCT formula. If there is only one array in the SUMPRODUCT formula then it will sum all the values in the array. It will work similar to SUM formula in Excel.
• Maximum Arrays: The latest Excel version can handle upto 255 array arguments in SUMPRODUCT formula. Whereas in old excel versions, the maximum number of arguments that SUMPRODUCT used to handle was 30.
• Multiple Criteria: You can use the SUMPRODUCT formula in Excel with multiple criteria by multiplying different conditions within the arrays or ranges.
• Conditional Sum: The SUMPRODUCT in Excel is often used for conditional summing without using the array formulas. For example, the SUMPRODUCT formula in Excel with condition is used in one column to to sum the values in another column.
• SUMPRODUCT Alternatives: Even though the SUMPRODUCT is powerful, in some cases, other functions like SUMIFS, XLOOKUP, SORTBY or other array formulas might provide more straightforward solutions easily.

• Array Comparison: You can use the SUMPRODUCT for array comparison, and it will return the count of matching elements. For example, counting the number of values greater than a specified limit.

• Empty Cells: The empty cells or the cells containing text are treated as zero(0) in the SUMPRODUCT calculation.

SUMPRODUCT Examples

Example 1 (Basic Sumproduct)

=SUMPRODUCT(A1:A2, B1:B2)

This formula multiplies each corresponding pair of values in cells A1 to A2 and B1 to B2, then adds up the results. In this SUMPRODUCT formula example, if A1=2, A2=3, B1=4, B2=5, then the result would be (A1*B1) + (A2*B2) = (2*4) + (3*5) = 8 + 15 = 23.

Example 2 (Weighted Average)

=SUMPRODUCT(A1:A2,B1:B2)/SUM(B1:B2)

This formula calculates the weighted average of values in range from A1 to A2, where the weights are given by the corresponding values in B1 to B2. It’s useful when you want to find a weighted average of a set of numbers. In this SUMPRODUCT example, if A1=2, A2=3, B1=4, B2=5, then the result would be 23 / 9 = 2.56.

Example 3 (Conditional Sumproduct)

=SUMPRODUCT((B5:B8>5)*(C5:C8))

This formula sums the product of values in C5 to C8 where the corresponding value in B5 to B8 is greater than 5. The expression (B5:B8>5) creates an array of TRUE and FALSE values, acting as a condition.

Example 4 (Sumproduct with Multiple Criteria)

=SUMPRODUCT((B5:B8>5)*(C5:C8>10),(C5:C8))

This SUMPRODUCT formula sums the product of values in C5 to C8 where the corresponding value in B5 to B8 is greater than 5 and the corresponding value in C5 to C8 is greater than 10.

Example 5 (Counting Sumproduct)

=SUMPRODUCT(–(B5:B8=”Apple”),(C5:C8))

This Excel SUMPRODUCT formula counts the sum of values in C5 to C8 where the corresponding value in B5:B8 is “Apple”. The double negative (– ) is used to convert TRUE/FALSE to 1/0. If you will remove the double negative (– ), then it will return as Zero (0).

Example 6 (Countif and Multiple Criteria in Sumproduct)

=SUMPRODUCT((B5:B8=”Apple”)*(C5:C8>9))

This Excel SUMPRODUCT formula counts the number of occurrences where both conditions are met: B5:B8=”Apple” and C5:C8>9.

If you will remove the greater than (>) sign from the second condition (C5:C8>9), then it will return the sum instead of count.

Example 7 (Sumproduct with Date in a Range)

=SUMPRODUCT((B5:B8>=DATE(2025,1,1))*(B5:B8<=DATE(2025,12,31)),C5:C8)

This Excel SUMPRODUCT formula calculates the sum of values in C5:C8 corresponding to dates falling within the range of 01 January 2025 to 31 December 2025.

Example 8 (Sumproduct with ABS)

=SUMPRODUCT(ABS(A1:A3), B1:B3)

This Excel SUMPRODUCT formula calculates the sum the product of absolute values in A1:A3 and corresponding values in B1:B3. In this SUMPRODUCT formula example, if A1=2, A2=-3, A3=-4, B1=5, B2=6 and B3=7, then the result would be (A1*B1) + (A2*B2) + (A3*B3) = (2*5) + (3*6) + (4*7) = 10 + 18 + 28 = 56. Due to the combination of SUMPRODUCT with ABS formula, it is ignoring the negative signs in cell A2 and B2, and considering all values as positive for sum product.

Example 9 (Sumproduct with Transpose)

=SUMPRODUCT(A1:A3,TRANSPOSE(B1:D1))

In this example of SUMPRODUCT formula, it calculates the sum the product of absolute values in A1:A3 and corresponding values in B1:D1 which is in horizonal form. In this SUMPRODUCT formula example, if A1=2, A2=3, A3=4, B1=6, C1=6 and D1=7, then the result would be (A1*B1) + (A2*C1) + (A3*D1) = (2*5) + (3*6) + (4*7) = 10 + 18 + 28 = 56.

Example 10 (Sumproduct with Offset)

=SUMPRODUCT(A1:A3,OFFSET(B1:B3,2,0))

In this example of SUMPRODUCT formula, it calculates the sum the product of values in A1:A3 with corresponding values in B3:B5. In this example of SUMPRODUCT in Excel, due to the OFFSET formula and row 2 in the argument, it considers from the row 3 for the calculation. It is calculated as (A1*B3) + (A2*B4) + (A3*B5) = (2*7) + (3*8) + (4*9) = 14 + 24 + 36 = 74.

FAQs

What is Meaning of SUMPRODUCT in Excel?

The SUMPRODUCT in Excel means two main things: first it multiplies numbers from two lists together, and then adds up all the results. This is handy for tasks like finding weighted averages, total sales, or any time you need to multiply and add up values at the same time.

How to Fix #VALUE! Error in SUMPRODUCT Formula?

If the SUMPRODUCT formula is not working and resulting to #VALUE! Error then refer the below 2 solutions to fix the SUMPRODUCT formula easily:

• Keep all of the array arguments in the same dimension. For example, if the array1 refers to a cell range of 4 rows (A1 to A4), then array2 also correspond to the similar cell range (B1 to B4) in the SUMPRODUCT formula. In short, both arrays should have the same starting and ending row numbers.
• Avoid text or text format in one or more cells in the range, because the SUMPRODUCT formula accepts only numeric values.

Conclusion

The SUMPRODUCT formula in Excel is a useful tool for doing calculations with multiple criteria. It lets you add up the results of multiplying corresponding values in different lists or ranges that meet certain conditions. With just a few steps, you can use SUMPRODUCT to analyze and summarize data in your spreadsheets effectively. Whether it’s sales numbers, inventory, or any other data, SUMPRODUCT helps you extract insights efficiently. So, next time you have complex calculations in Excel, try using SUMPRODUCT to make your tasks easier and get precise results.

Subscribe Rath Point's official YouTube channel to know the latest updates, tips, and tricks.