How Excel SUMPRODUCT Works: Examples and Practical Uses

Formulas & functions · Updated November 20, 2026

Excel’s SUMPRODUCT function multiplies corresponding values in one or more arrays, then adds the resulting products. For example, it can calculate a sales total from quantities and prices, or sum values only for rows that meet one or more conditions; its key requirement is that the arrays line up in size and shape.

What SUMPRODUCT does

SUMPRODUCT is a worksheet function that combines multiplication and addition. The name describes its basic operation: it forms products from corresponding positions in its input arrays and sums those products. An array here can be a range of cells, or an array of values produced by a calculation such as a comparison.

The function is useful whenever values in matching rows or columns need to be multiplied before being totaled. A familiar example is revenue: multiply each item’s quantity by its unit price, then add the row results. Instead of adding a separate multiplication formula for every row, SUMPRODUCT performs the whole calculation in one formula.

It is also used for conditional calculations. A test such as “is this row for the East region?” produces TRUE or FALSE values. SUMPRODUCT can treat those results as 1s and 0s, so rows that pass the test contribute to the total and rows that fail contribute nothing. That makes it a flexible tool for totals and counts, not just literal multiplication.

How Excel evaluates the function

Suppose the first array contains 2, 3, and 1, while the second contains 5, 4, and 10. Excel pairs values by position: 2×5, 3×4, and 1×10. It then adds the three products, giving 32. It does not multiply every value in the first range by every value in the second; it matches the first with the first, the second with the second, and so on.

With multiple arrays, Excel multiplies the corresponding entries across all of them before adding the results. If one array is a single range and another is a calculated set of TRUE/FALSE results, the same positional rule applies. This is why the arrangement of the data matters as much as the arithmetic.

In a normal SUMPRODUCT calculation, you do not need to enter the formula as a legacy array formula with Ctrl+Shift+Enter. Press Enter. The function handles the arrays internally in Microsoft 365 for Windows, as it did in earlier Excel versions that support SUMPRODUCT. This differs from some older array-formula techniques, which required a special key combination.

Basic example: total sales from quantity and price

Assume quantities are in B2:B4 and unit prices are in C2:C4. Enter:

=SUMPRODUCT(B2:B4,C2:C4)

For quantities 2, 3, and 1 and prices 5, 4, and 10, Excel calculates (2×5)+(3×4)+(1×10), which equals 32. The formula returns the same total you would get by calculating each row’s line total and summing those results, but it avoids adding a helper column.

This approach is convenient for a compact calculation, but a helper column can still be useful when people need to inspect each row’s contribution or when the workbook needs to show line totals. SUMPRODUCT is not automatically better; choose it when a direct total is clear and maintainable.

Use conditions to include selected rows

To sum sales for one region, multiply a condition by the sales values. Assume region names are in A2:A6 and sales amounts are in C2:C6:

=SUMPRODUCT((A2:A6="East")*C2:C6)

The comparison A2:A6=”East” produces one TRUE or FALSE result per row. In the multiplication, TRUE acts as 1 and FALSE as 0. A matching row therefore contributes its sales amount; a nonmatching row contributes zero. If the East rows contain 120 and 80, the result is 200.

You can add conditions by multiplying more tests together. Assume regions are in A2:A20, order values are in B2:B20, and the goal is to total East-region orders greater than 100:

=SUMPRODUCT((A2:A20="East")*(B2:B20>100)*B2:B20)

Each row contributes its order value only if both tests are TRUE. If a row fails either test, one of the condition terms is zero, so its product is zero. Parentheses make the tests and their multiplication easy to read and help avoid ambiguity.

The same pattern can count qualifying rows instead of summing amounts. Assume region names are in A2:A20 and amounts are in B2:B20:

=SUMPRODUCT((A2:A20="East")*(B2:B20>100))

There is no amount range at the end, so each row that meets both conditions contributes 1 rather than its sales value. The result is the number of matching rows. For straightforward conditional sums or counts, SUMIFS and COUNTIFS may be easier for another person to understand; SUMPRODUCT is especially handy when the logic needs arithmetic or more flexibility.

Weighted averages and date ranges

A weighted average gives some observations more influence than others. The numerator adds each value multiplied by its weight, and the denominator adds the weights. Assume scores are in B2:B4 and their weights are in C2:C4:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

For scores of 80, 90, and 70 with weights of 20%, 30%, and 50%, the weighted total is 16+27+35=78, and the weights total 100%. The result is 78. If weights are entered as 20, 30, and 50 instead, the same formula still returns 78 because the numerator and denominator scale together. Check that the weights are appropriate and that the denominator is not zero; dividing by a zero total weight produces a division error.

Date conditions work because Excel stores dates as serial numbers. To sum values for a region within a calendar year, use a start date inclusive and the next year’s start date exclusive. Assume region names are in A2:A100, dates in B2:B100, and values to sum in C2:C100:

=SUMPRODUCT((A2:A100="East")*(B2:B100>=DATE(2026,1,1))*(B2:B100<DATE(2027,1,1))*C2:C100)

This includes dates from January 1, 2026, through December 31, 2026, including times on those dates if the cells contain timestamps. The exclusive upper boundary avoids accidentally leaving out records later on December 31. Replace the dates with the year relevant to the data.

Range rules and data considerations

For a positional calculation, the arrays should have matching dimensions. Two ranges with the same number of rows and columns are the safest choice. If the arrays have incompatible dimensions, SUMPRODUCT returns a #VALUE! error rather than guessing how to pair them. Keep ranges aligned to the same first and last data rows.

  • Blanks and numbers: Blank cells generally contribute zero in these calculations. Numeric values are used as numbers, so check that quantities, prices, and weights have not been imported as text.
  • Text and errors: Non-numeric entries supplied as array values are treated as zero by SUMPRODUCT, but an error value in a referenced range can cause the formula to return an error. Arithmetic performed inside an array expression can also produce its own errors.
  • Dates: Real Excel dates are numeric serial values and can be compared with date boundaries. Text that merely looks like a date may not behave as expected.
  • Criteria text: A condition such as A2:A20=”East” compares the cell contents with that text. Watch for unexpected spaces or inconsistent spellings in imported data.

SUMPRODUCT can be calculation-heavy when applied to very large ranges, particularly when each row includes several conditions. Avoid referencing entire columns such as A:A in complex SUMPRODUCT formulas; that can make Excel evaluate more than a million rows per column. Use ranges limited to the actual data, or consider Excel tables and formulas designed for the task.

Common mistakes and troubleshooting

A frequent mistake is mismatching range sizes—for example, using A2:A100 for a condition but C2:C99 for the values. Align the start and end rows, then confirm that each range covers the same records. If the formula returns an unexpectedly small total, inspect the condition values and check for extra spaces, misspellings, or numbers stored as text.

Another source of confusion is forgetting that a condition must be converted into a numeric contribution. In a SUMPRODUCT formula, multiplying the condition by another array performs that conversion: TRUE becomes 1 and FALSE becomes 0. Writing a comparison as a separate argument without combining it with the values can lead to results that do not reflect the intended filter. Parentheses around each comparison make the logic easier to verify.

Do not assume SUMPRODUCT automatically ignores every problem in the data. An error such as #N/A in a referenced array can pass through the calculation. Find and correct the underlying error, or deliberately handle it in the source data or formula. Also verify the result with a small sample: calculate a few row products by hand and compare their sum with the function’s output.

For workbook readability, use clear headings and consistent data types, and keep criteria in visible cells when users may need to change them. A formula with a cell reference for the region can be easier to maintain than a fixed text condition, especially in a report used for several regions. Document unusual logic so a later editor can tell why a row is included.

SUMPRODUCT compared with other Excel functions

For a simple product total, SUMPRODUCT is concise and avoids a separate line-total column. Multiplication followed by SUM is another route if the products already exist in a helper column. That layout can be more transparent in financial or operational reports where row-level calculations need to be reviewed.

For ordinary conditional totals, SUMIFS is often a more direct choice, and COUNTIFS is designed to count rows meeting criteria. These functions express the intent explicitly and may be easier to maintain when conditions are conventional. SUMPRODUCT is valuable when conditions must be combined with calculations, when the desired logic is awkward to express with a standard criteria function, or when a weighted calculation is needed.

In Microsoft 365, dynamic-array formulas offer other ways to work with arrays, but SUMPRODUCT remains useful when the desired output is one scalar total. It does not spill a set of row-by-row answers; it returns a single result. If the goal is to inspect intermediate values, a helper column or a formula that returns an array may be more appropriate.

Frequently Asked Questions

Do I need Ctrl+Shift+Enter for SUMPRODUCT?
No. Enter a SUMPRODUCT formula with Enter in Microsoft 365 for Windows. It is not a legacy array formula that requires Ctrl+Shift+Enter.

Why does SUMPRODUCT return #VALUE!?
Check that the arrays have compatible dimensions and that the formula does not perform invalid arithmetic. Also inspect the referenced cells for existing errors. Mismatched range sizes are a common cause.

Can SUMPRODUCT count cells that meet conditions?
Yes. Multiply condition tests together and use the resulting TRUE/FALSE values as the array to sum. Each row meeting every test contributes 1, so the result is a count.

Does SUMPRODUCT work with text criteria?
Yes. A comparison such as a region range equal to “East” creates a TRUE/FALSE result for each row. Text values in the range should be consistent; extra spaces or different spellings can prevent an intended match.

Is SUMPRODUCT better than SUMIFS?
Neither is universally better. SUMIFS is usually clearer for a standard conditional sum, while SUMPRODUCT can handle weighted calculations and more involved array logic. Prefer the formula that communicates the calculation accurately and is easiest to maintain.

Can SUMPRODUCT use whole-column references?
It can, but whole-column references may slow complex calculations because Excel must evaluate many cells, including empty ones. Bounded ranges or a well-structured table are usually more efficient.

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.