SUMPRODUCT in Excel: Examples, Criteria, and Tips

Formulas & functions · Updated November 22, 2026

A simple example of SUMPRODUCT in Excel is =SUMPRODUCT(B2:B5,C2:C5): if B2:B5 contains quantities and C2:C5 contains unit prices, the formula multiplies each row’s quantity by its price and adds the results. It is useful for totals, weighted averages, and calculations that include one or more conditions.

A basic SUMPRODUCT example

Suppose a small order list has quantities in column B and unit prices in column C. The four rows contain 3 items at $12, 2 at $8, 5 at $4, and 1 at $20. With quantities in B2:B5 and prices in C2:C5, enter this formula:

=SUMPRODUCT(B2:B5,C2:C5)

The arithmetic is 3 × 12 + 2 × 8 + 5 × 4 + 1 × 20, which equals 92. The formula therefore returns 92. Format the result cell as Currency if you want it displayed as a monetary amount; SUMPRODUCT returns a number and does not apply a currency format by itself.

This avoids adding a separate multiplication formula to every row and then summing a helper column. A helper column can still be useful when people need to inspect each line’s extended price, but SUMPRODUCT can calculate the same total directly.

How SUMPRODUCT works

SUMPRODUCT takes corresponding values from its arrays, multiplies them together, and adds the products. In the basic example, it pairs B2 with C2, B3 with C3, and so on. The ranges must line up: each quantity needs to be paired with the price from the same row.

The function name reflects this process: it sums the products. Its general form is SUMPRODUCT(array1, [array2], ...). The first array is required, and additional arrays are optional. In everyday use, two or more arrays are often supplied to multiply parallel lists. Excel’s function accepts multiple arrays, but every array must have the same number of rows and columns.

When a condition is included, SUMPRODUCT can also treat a TRUE/FALSE test as a set of 1s and 0s through multiplication. A matching row contributes its value; a nonmatching row is multiplied by zero and contributes nothing. This makes criteria-based calculations possible without adding a separate helper column.

Sum values that meet one condition

Imagine a sales list with region names in A2:A6 and sales amounts in C2:C6. The five rows are East $120, West $80, East $40, East $60, and West $35. To add sales for East only, use:

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

The comparison produces TRUE for the three East rows and FALSE for the two West rows. Multiplication converts those results into the effect of 1 and 0, so the calculation is 120 + 40 + 60. The result is 220. The text criterion must match the values in the region cells; extra spaces or inconsistent spelling can prevent a row from matching.

You can also use the same pattern to count matches. With region names in A2:A6, this formula counts the East entries:

=SUMPRODUCT(--(A2:A6="East"))

The two minus signs, often called the double unary, convert TRUE and FALSE into 1 and 0. There are three East entries, so the result is 3. For a simple count, COUNTIF is usually shorter; this pattern is useful when the count needs to be combined with other array calculations.

Apply multiple conditions

Conditions can be multiplied together so a row contributes only when every test is true. Suppose A2:A6 contains regions, B2:B6 contains product names, and C2:C6 contains sales. The rows are East/Widget/$120, West/Widget/$80, East/Cable/$40, East/Widget/$60, and West/Cable/$35. To total sales for East Widget orders, enter:

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

Only the first and fourth rows meet both conditions, so the result is 120 + 60, or 180. Multiplying the two tests means both must be true for the row’s sales amount to remain in the total. This is the equivalent of an AND condition.

For an OR condition, adding two tests can include rows that meet either one. Take care if a row could meet both tests: adding the tests would count that row twice. If the alternatives can overlap and should be counted only once, use a distinct logic pattern or choose a function and criteria setup that matches the intended rule.

Calculate a weighted average

A weighted average gives each value influence in proportion to its weight. For example, a unit price bought in a larger quantity should contribute more to the average purchase price than a unit price bought once. With quantities in B2:B5 and unit prices in C2:C5, the formula is:

=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)

Using the earlier quantities and prices, SUMPRODUCT returns 92 and SUM returns 3 + 2 + 5 + 1, or 11. The result is 92 ÷ 11, approximately 8.3636. Format the result to the desired number of decimal places, such as $8.36. This is not the same as taking the simple average of the four prices, because the quantities differ.

Check that the weights add up to a nonzero value. If all quantities are zero, the denominator is zero and Excel returns a division-by-zero error. A weighted average is only meaningful when the weights and the values represent the quantities you intend to compare.

Use date criteria carefully

SUMPRODUCT can filter dates using comparisons, provided the cells contain real Excel dates rather than text that merely looks like a date. Suppose A2:A6 contains dates and B2:B6 contains amounts: January 3, 2026 ($40); January 15 ($55); February 1 ($30); January 31 ($25); and December 31, 2025 ($10). To total January 2026, use:

=SUMPRODUCT((A2:A6>=DATE(2026,1,1))*(A2:A6<DATE(2026,2,1))*B2:B6)

The formula includes dates on or after January 1 and before February 1. It returns 40 + 55 + 25, or 120. Using a less-than test for the first day of the next month includes every time on January 31 if the cells contain date-and-time values too. Excel stores dates as serial numbers, which is why date comparisons work in this calculation.

SUMPRODUCT compared with SUM and SUMIFS

SUM is the straightforward choice when you simply need to add a range. SUMPRODUCT is useful when values need to be multiplied row by row before being totaled, or when criteria must be applied within a calculation.

For a basic total based on one or more criteria, SUMIFS is often easier to read. With regions in A2:A6 and sales in C2:C6, the East total can be written as:

=SUMIFS(C2:C6,A2:A6,"East")

This returns 220 for the example above. SUMIFS is designed to sum a range using criteria ranges, while SUMPRODUCT is especially flexible when the calculation involves multiplying arrays or combining conditions with arithmetic. Prefer the simpler function when it expresses the task clearly; a shorter formula is often easier to maintain.

SUMPRODUCT can be convenient for a calculation that would otherwise need an intermediate column. On the other hand, formulas that use several long arrays and nested tests can become hard to audit. A helper column, SUMIFS, or a PivotTable may be a better fit if other workbook users need to see or summarize the steps.

Common errors and practical checks

If SUMPRODUCT returns #VALUE!, first check that all arrays have matching dimensions. For example, B2:B20 and C2:C19 do not have the same number of rows. Also check for errors already present in the referenced cells, because an error in an input can flow through to the result.

Other useful checks include:

  • Confirm that each row in one range corresponds to the same record in every other range.
  • Use consistent range endpoints. Avoid pairing a full-column reference with a short range in the same calculation.
  • Check for leading or trailing spaces in text criteria and source values.
  • Verify that dates are stored as Excel dates and amounts are numeric, not text.
  • Test a small section of the formula or inspect the source rows when the result looks unexpectedly high or low.

SUMPRODUCT normally does not require Ctrl+Shift+Enter in current Excel; press Enter to enter the formula. The function is available in Microsoft 365 for Windows, and the same formula approach works in Excel for Mac. Depending on regional settings, Excel may display semicolons rather than commas as formula separators; the formulas here use comma separators.

Frequently Asked Questions

What is a simple example of SUMPRODUCT in Excel?

With quantities in B2:B5 and unit prices in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5). It multiplies each row’s quantity by its price and adds the four products.

Can SUMPRODUCT work with text criteria?

Yes. A comparison such as (A2:A6="East") creates a TRUE/FALSE test that can be multiplied by a numeric range. Make sure the criterion matches the source text as stored.

Do I need Ctrl+Shift+Enter for SUMPRODUCT?

No. In Microsoft 365, enter a SUMPRODUCT formula normally with Enter. It does not need the legacy array-formula key combination.

Why does SUMPRODUCT return #VALUE?

A common cause is arrays with different dimensions, such as a five-row range paired with a four-row range. An error in an input cell can also cause an error in the result.

Is SUMPRODUCT better than SUMIFS?

Neither is always better. SUMIFS is generally clearer for adding values that meet ordinary criteria, while SUMPRODUCT is useful for row-by-row multiplication and calculations that combine criteria with other array operations.

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.