SUMPRODUCT in Excel: How It Works & Examples

Formulas & functions · Updated November 22, 2026

The SUMPRODUCT function in Excel multiplies corresponding values in two or more arrays and then adds the results, returning a single number. For example, =SUMPRODUCT(A2:A5, B2:B5) multiplies each value in A2:A5 by its matching value in B2:B5 and sums those products. It is most commonly used for weighted totals, conditional sums without array formulas, and counting rows that meet multiple criteria.

What SUMPRODUCT Actually Does

SUMPRODUCT is a built-in Excel function that works on arrays of numbers. Its basic syntax is:

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

Each array must have the same number of rows and columns. The function multiplies the first element of array1 by the first element of array2, then the second by the second, and so on, and finally adds all those products together. If you give it only one array, it simply sums the numbers in that array—like a faster SUM.

For example, with quantities in A2:A5 (2, 3, 4, 5) and unit prices in B2:B5 (10, 15, 20, 25), =SUMPRODUCT(A2:A5, B2:B5) returns 2*10 + 3*15 + 4*20 + 5*25 = 20 + 45 + 80 + 125 = 270.

Common Uses: Weighted Averages and Totals

The most straightforward use is calculating a weighted total. Suppose you have a list of products with quantities sold (column A) and price per unit (column B). =SUMPRODUCT(A2:A100, B2:B100) gives total revenue without needing a helper column for each row’s subtotal.

Weighted averages are another classic. If you have exam scores in B2:B5 and the weight of each exam in C2:C5 (with weights summing to 1), the weighted average is =SUMPRODUCT(B2:B5, C2:C5). If the weights are percentages that sum to 100%, divide by 100: =SUMPRODUCT(B2:B5, C2:C5)/100.

Using SUMPRODUCT for Conditional Sums and Counts

SUMPRODUCT can count or sum rows that meet multiple conditions, often replacing the older SUMIFS or COUNTIFS when you need more flexibility. The trick is to convert TRUE/FALSE comparisons into 1/0 by using double unary minus (--) or by multiplying the conditions directly.

For example, with sales in B2:B10 and region in A2:A10, to sum all sales for “West”: =SUMPRODUCT(--(A2:A10="West"), B2:B10). The -- turns TRUE to 1 and FALSE to 0. Then SUMPRODUCT multiplies each 1/0 by the sales value and sums, effectively adding only the West rows.

To count rows where region is “West” and sales are over 100: =SUMPRODUCT(--(A2:A10="West"), --(B2:B10>100)). This multiplies two arrays of 1/0 values and sums, giving the count of rows meeting both conditions.

How to Choose: SUMPRODUCT vs SUMIFS vs COUNTIFS

Excel now has dedicated functions for conditional sums and counts: SUMIFS and COUNTIFS. They are usually faster and easier to read. Use them when your conditions are simple comparisons (equal, greater than, etc.) on ranges of the same size.

Use SUMPRODUCT when:

  • You need to multiply values from multiple arrays before summing (weighted totals).
  • You need to apply conditions that involve calculations, not just direct comparisons (e.g., sum where A2:A10 * B2:B10 > 100).
  • You want to avoid entering a formula as an array formula (with Ctrl+Shift+Enter) in older Excel versions.
  • You need to count or sum based on conditions that reference other arrays of the same size, not just a single range.

For most straightforward conditional sums, SUMIFS is clearer and faster. Example: =SUMIFS(B2:B10, A2:A10, "West") does the same as the SUMPRODUCT example above but is easier to read.

Real-World Example: Sales Report

Assume you have a table with:

  • A2:A100 = Product Category (e.g., “Electronics”, “Clothing”)
  • B2:B100 = Units Sold
  • C2:C100 = Unit Price
  • D2:D100 = Region (“North”, “South”)

To get total revenue for Electronics in the North region:

=SUMPRODUCT(--(A2:A100="Electronics"), --(D2:D100="North"), B2:B100, C2:C100)

This works because the two condition arrays (each 1/0) multiply with the units and price arrays. Only rows where both conditions are TRUE contribute their product (units × price) to the sum. If you had 10 Electronics rows in North with units 2,3,4 and prices 100,200,300, the result would be 2*100 + 3*200 + 4*300 = 200 + 600 + 1200 = 2000.

Common Problems and How to Fix Them

SUMPRODUCT often trips people up with a few recurring issues:

  • #VALUE! error: This usually means the arrays have different sizes. Check that all ranges have the same number of rows and columns.
  • Text in numeric arrays: If any cell contains text instead of a number, SUMPRODUCT treats it as 0. That might be fine, but if you expect a number, fix the underlying data.
  • Logical values not converting: If you write =SUMPRODUCT(A2:A10="West", B2:B10) without the double unary, Excel may return 0 or an error because TRUE/FALSE are not automatically multiplied. Always use -- or multiply by 1.
  • Whole-column references: Using A:A works but can slow down calculation because SUMPRODUCT processes all 1,048,576 rows. Limit ranges to the actual data area.
  • Array size mismatch in older Excel: If you are using Excel 2019 or earlier, SUMPRODUCT with conditions works fine, but you cannot use dynamic arrays like SEQUENCE inside it unless you have Microsoft 365.

Performance and Alternatives

SUMPRODUCT is not the fastest function for large datasets. For a few thousand rows it is fine, but for hundreds of thousands of rows, SUMIFS and COUNTIFS are significantly faster because they are optimized. If you have Microsoft 365, you can also use SUM with the new dynamic array functions like FILTER: =SUM(FILTER(B2:B100, (A2:A100="West")*(D2:D100="North"))) returns the sum of B values for matching rows. That is often clearer and just as fast.

For weighted totals, you could also use SUMPRODUCT or a helper column. A helper column with =B2*C2 and then =SUM(E2:E100) is often easier to audit, but SUMPRODUCT avoids the extra column.

Frequently Asked Questions

Can SUMPRODUCT handle text values? Not directly. It multiplies numbers. If you have text in the arrays, it treats them as 0. For counting text occurrences, use COUNTIF or COUNTIFS instead.

Why does my SUMPRODUCT return 0 even though there are matches? The most common cause is missing the double unary (--) on logical conditions. Without it, TRUE/FALSE are not converted to 1/0, so the multiplication yields 0. Also check that your ranges are the same size.

Can I use wildcards like * or ? in SUMPRODUCT? No. SUMPRODUCT does not support wildcards. For partial text matching, use SUMIFS with wildcards or use ISNUMBER(SEARCH(...)) inside SUMPRODUCT, but that is more complex.

Is SUMPRODUCT an array formula? It is an array function, but you do not need to press Ctrl+Shift+Enter. It handles arrays internally. In older Excel versions, it was a workaround for array formulas, but now you can use dynamic arrays in Microsoft 365.

Can I use SUMPRODUCT across multiple sheets? Yes, but each range must be the same size. For example, =SUMPRODUCT(Sheet1!A2:A5, Sheet2!B2:B5) multiplies corresponding cells from different sheets. However, if you need to sum across sheets with conditions, SUMIF or SUMIFS with 3D references may be simpler.

What is the difference between SUMPRODUCT and SUM? SUM simply adds all numbers in a range. SUMPRODUCT multiplies corresponding elements first, then adds. If you give SUMPRODUCT a single array, it behaves like SUM. But the real power is in the multiplication step.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.