Excel SUMPRODUCT vs PRODUCT: Differences, Examples, and Uses

Formulas & functions · Updated November 19, 2026

PRODUCT multiplies numbers together to return one product; SUMPRODUCT multiplies corresponding values in one or more arrays, then adds those products. Use PRODUCT for a result such as 2 × 3 × 4, and SUMPRODUCT for a total such as quantities multiplied by prices and summed across rows.

The core difference

The names are similar, but the functions answer different questions. PRODUCT asks, “What is the result of multiplying these numbers?” SUMPRODUCT asks, “What is the sum of the products formed by matching values across these lists?” The latter is also known as a dot product in mathematics when it combines two equally sized lists.

For example, if three rows contain quantities and unit prices, multiplying every quantity by its matching price and adding the row totals gives a sales total. That is a SUMPRODUCT task. Multiplying the three quantities themselves would give a PRODUCT result, but it would not calculate sales.

  • PRODUCT: multiply numbers, such as factors, dimensions, or growth multipliers.
  • SUMPRODUCT: calculate and add row-by-row products, such as quantity × price or weight × score.

Neither function is universally better. The right choice depends on whether you want one combined multiplication or a sum of paired multiplications.

How PRODUCT works

PRODUCT takes numbers or references to numbers and multiplies the numeric values together. It can take individual values, cell references, ranges, or a combination. The result is a single number. Multiplication order does not affect the result, so multiplying 2, 3, and 4 returns 24 regardless of their order.

With the values 2, 3, and 4 in A2:A4, the formula =PRODUCT(A2:A4) returns 24. The cell layout assumed here is A2 = 2, A3 = 3, and A4 = 4. The arithmetic is 2 × 3 × 4 = 24.

PRODUCT is useful when the result should be one factor built from several inputs. Examples include multiplying dimensions to calculate a volume, combining successive percentage multipliers, or calculating the combined effect of several coefficients. For instance, a 10% increase followed by a 5% increase can be represented by multiplying 1.10 by 1.05; the combined multiplier is 1.155, equivalent to a 15.5% increase, not 15%.

A zero in the numeric inputs makes the product zero. A negative input affects the sign: an odd number of negative factors produces a negative result, while an even number produces a positive result. PRODUCT also propagates errors in its arguments, so an error value in a referenced calculation can make the overall result an error.

How SUMPRODUCT works

SUMPRODUCT pairs values by their position in the supplied arrays. It multiplies the first value in one array by the first value in the other, then the second by the second, and so on. Finally, it adds all those row-level results. The arrays normally need to have the same dimensions; mismatched ranges can cause a #VALUE! error.

Suppose A2:A4 contains quantities 2, 3, and 4, while B2:B4 contains unit prices 10, 15, and 20. With quantities in A2:A4 and prices in B2:B4, =SUMPRODUCT(A2:A4,B2:B4) returns 145: 2 × 10 + 3 × 15 + 4 × 20 = 20 + 45 + 80 = 145.

This is different from multiplying all six values together. SUMPRODUCT uses the position of each value to make three pairs, then adds the pairwise results. It can also accept more than two arrays: corresponding values from each array are multiplied together for each position, and those row products are summed.

SUMPRODUCT can perform this calculation directly, without first creating a separate column of row totals. It also works without entering the formula as a legacy array formula with Ctrl+Shift+Enter. In current Excel, press Enter after typing it. This makes it convenient for totals, weighted calculations, and conditional summaries.

Side-by-side examples

Consider a short list of three values: 2, 3, and 4. PRODUCT combines them into one multiplication, so it returns 24. If those values are quantities and the matching prices are 10, 15, and 20, SUMPRODUCT returns 145 instead. The different answers are not a contradiction; the functions are performing different operations on the data.

You can include a constant as an additional PRODUCT argument. With A2 = 2, A3 = 3, and A4 = 4, =PRODUCT(A2:A4,2) returns 48, because 2 × 3 × 4 × 2 = 48. This is useful for applying a fixed multiplier, though a direct multiplication formula may be easier to read when there are only a few factors.

A quick way to decide is to describe the calculation in words. If the instruction is “multiply these factors,” PRODUCT is a natural fit. If it is “multiply each row’s matching values and total the row results,” SUMPRODUCT is the closer match. A regular SUM of a helper column can also produce the latter result, but SUMPRODUCT keeps the calculation in one formula.

Using SUMPRODUCT with conditions

SUMPRODUCT is often used to total only rows that meet a condition. For example, suppose A2:A5 contains category names, B2:B5 contains quantities, and C2:C5 contains unit prices. The categories are Office, Travel, Office, and Office; the quantities are 2, 1, 3, and 4; and the prices are 5, 20, 6, and 2. With those values in the stated ranges, =SUMPRODUCT((A2:A5="Office")*B2:B5*C2:C5) returns 36. It includes only Office rows: 2 × 5 + 3 × 6 + 4 × 2 = 10 + 18 + 8 = 36.

The comparison A2:A5="Office" creates a set of TRUE or FALSE results. In this arithmetic expression, those results act as 1 or 0: a matching row is included, and a nonmatching row contributes zero. The quantity and price for each row are then multiplied before the results are added.

For multiple conditions, multiply additional comparisons into the expression. For example, a formula might require both a category match and a date test. Make sure the condition ranges line up with the value ranges and contain the same number of rows. For readability and easier maintenance, keep the ranges aligned and use clear headings in the worksheet. If a condition formula becomes difficult to audit, helper columns or a PivotTable may be easier for another person to understand.

Choosing the right function

Choose based on the shape of the calculation, not just the size of the data. PRODUCT is straightforward when all listed factors should be multiplied together. SUMPRODUCT is appropriate when each row represents a separate contribution that should be calculated and added.

  • Choose PRODUCT to multiply a list of independent factors into one result.
  • Choose SUMPRODUCT to multiply aligned values row by row and sum the results.
  • Choose SUM when the values are already calculated and only need adding.
  • Consider a helper column if you need to inspect or explain each row’s intermediate product.

A common related question is whether SUMPRODUCT is just PRODUCT followed by SUM. Not exactly: PRODUCT multiplies the numbers it receives into one combined result. SUMPRODUCT forms a separate product at each corresponding position, then sums those products. Replacing one with the other changes the mathematical operation.

For weighted averages, SUMPRODUCT is commonly paired with SUM. If B2:B4 contains weights 2, 3, and 5, and C2:C4 contains scores 80, 90, and 70, then with weights in B2:B4 and scores in C2:C4, =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4) returns 78. The weighted total is 2 × 80 + 3 × 90 + 5 × 70 = 780, and the total weight is 10, so 780 ÷ 10 = 78. This formula assumes the weights sum to a nonzero value.

Common mistakes and troubleshooting

The most frequent mistake is choosing a function based on its name rather than the desired result. If a SUMPRODUCT formula returns a total that seems unexpectedly large or small, check whether the ranges are paired as intended. If a PRODUCT result is unexpectedly zero, inspect its numeric inputs for a zero value.

  • Unequal range sizes: SUMPRODUCT arrays should have matching dimensions. Check both the first and last row in each range.
  • Text or blanks in numeric data: PRODUCT ignores text and logical values found in referenced ranges, and ignores empty cells. SUMPRODUCT treats nonnumeric entries in its arrays as zero. A number stored as text can therefore behave differently from a true numeric value, so check imported data types.
  • Errors in source cells: an error in a referenced calculation can flow into the result. Find and correct the source error rather than hiding it automatically.
  • Unexpected signs: review negative inputs and confirm that the number of negative factors matches the expected sign.
  • Wrong ranges: verify that each row’s quantity is paired with the price from that same row, and that headings are excluded.

PRODUCT treats numbers typed directly as arguments differently from nonnumeric entries found inside a referenced range: direct text representations of numbers and logical values can be counted, while text and logical values in a reference or array are ignored. To avoid confusing results, keep inputs numeric and put criteria logic in a clearly written SUMPRODUCT formula. Excel’s separators can also depend on regional settings; formulas here use commas as argument separators, as requested, while some installations display semicolons.

Performance and compatibility

PRODUCT and SUMPRODUCT are established Excel functions and are available in Microsoft 365 for Windows. The formulas described here work in Excel for Mac and Excel for the web as well; the main differences are interface details, not the arithmetic. SUMPRODUCT does not require a special array-entry shortcut in current Excel.

For a normal-sized worksheet, both functions are usually simple to calculate. Very large ranges, especially repeated calculations over entire columns combined with complex conditions, can increase recalculation work. Use ranges that cover the actual data where practical, and avoid building a complicated formula when a helper column, Excel table, or PivotTable would make the workbook easier to review. If you use a helper column, label it so readers can see what each intermediate value represents.

Frequently Asked Questions

Does SUMPRODUCT multiply or add?
It does both: it multiplies values at matching positions across its arrays, then adds the resulting products. For a simple total of existing values, use SUM instead.

Can PRODUCT add numbers?
No. PRODUCT multiplies its numeric inputs. To add values, use SUM. To multiply paired values and then add those results, use SUMPRODUCT.

Do SUMPRODUCT ranges have to be the same size?
They should have matching dimensions so Excel can pair each value correctly. Ranges with different dimensions can return #VALUE!.

Does SUMPRODUCT need Ctrl+Shift+Enter?
No. In current Excel for Microsoft 365, enter the formula normally with Enter. The function handles the array calculation without legacy array entry.

Can SUMPRODUCT replace a weighted-average formula?
Yes, it can calculate the weighted total, which you divide by the sum of the weights. Check that the weights are meaningful for the calculation and do not sum to zero.

Why does PRODUCT ignore text in a cell range?
PRODUCT counts numeric values in referenced ranges and ignores text, logical values, and empty cells there. If a value should affect the result, convert it to a genuine number and check for import or formatting issues.

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.