Excel SUM vs SUMPRODUCT: Which Formula Should You Use?

Formulas & functions · Updated November 19, 2026

Use SUM to add numbers in a range; use SUMPRODUCT when you need to multiply corresponding values and add the results, or when conditions should determine which values are included. For a simple total, SUM is usually clearer; for conditional calculations or weighted totals, SUMPRODUCT can do more in one formula.

The basic difference

Both functions return a single number, but they are designed for different kinds of work. SUM adds values. SUMPRODUCT multiplies items in corresponding positions across one or more arrays, then adds those products. It can also use TRUE/FALSE tests as numeric filters, which makes it useful for calculations such as “total sales for the East region” without first making a separate filtered list.

The names can be misleading if you are new to Excel: SUMPRODUCT is not simply a complicated alternative to SUM. If the task is just to total a column, its extra capabilities are unnecessary. If each row has a quantity and a price, or if rows must meet multiple conditions, its row-by-row arithmetic can be a natural fit.

How SUM works

SUM adds numbers supplied as arguments. Most often, you give it a range, though it can also accept multiple ranges or individual numbers. It is the standard choice for totals, subtotals, and straightforward additions. With numbers in B2:B6, the formula is:

With sales amounts in B2:B6, this formula returns 350 when the amounts are 120, 80, 50, 70, and 30.

=SUM(B2:B6)

For referenced cells, SUM ignores blank cells and text, while adding numeric values. That makes it convenient when a column contains labels or has not yet been completely filled in. It does not, by itself, decide which rows qualify based on a separate column; for that, you would generally use a conditional function or build a suitable array calculation.

How SUMPRODUCT works

SUMPRODUCT takes arrays (often ranges), multiplies the values in matching positions, and adds those row-level products. For example, it can calculate a line total for every row from quantity times unit price, then return the sum of all line totals. The arrays should have matching dimensions; ranges with different numbers of rows or columns can cause an error.

With quantities in A2:A5 and unit prices in B2:B5, where the rows are 2 units at 10, 3 at 7, 1 at 12, and 4 at 5, use:

=SUMPRODUCT(A2:A5,B2:B5)

The result is 73: Excel calculates 2×10 + 3×7 + 1×12 + 4×5. You could instead create a separate line-total column and sum it, but SUMPRODUCT performs the multiplication and addition together. That can be useful when a compact formula is preferable, though a helper column may be easier to audit in a complex workbook.

Its syntax is SUMPRODUCT(array1,[array2],[array3],...). The first array is required; additional arrays are optional. With one array, it returns the sum of that array’s values. With several, it multiplies corresponding entries before summing. In current Excel, you enter it normally with Enter; it does not require the Ctrl+Shift+Enter keystroke used for some legacy array formulas.

Using SUMPRODUCT for conditions

A common use is summing values only for rows that meet a condition. A comparison such as A2:A6="East" produces TRUE or FALSE for each row. Multiplying that result by the sales amounts converts TRUE to 1 and FALSE to 0 in the calculation, so only matching sales contribute to the total.

With regions in A2:A6 and sales in B2:B6, use the following formula. Assume the rows are East 120, West 80, East 50, North 70, and East 30; the result is 200.

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

You can multiply multiple tests to require that all of them be true. With regions in A2:A6, sales in B2:B6, and categories in C2:C6, suppose the rows are East/120/Hardware, West/80/Service, East/50/Service, North/70/Hardware, and East/30/Hardware. To total sales where the region is East and the category is Hardware:

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

The result is 150, from the first and fifth rows. This is an AND condition: both tests must be true for a row to count. Parentheses help make each test’s role clear and prevent operator-order confusion. For a simple single condition, Excel’s SUMIF is often more readable; for multiple conditions, SUMIFS is usually worth considering before writing a longer SUMPRODUCT formula.

Weighted totals and averages

Another useful distinction is that multiplying corresponding values is part of SUMPRODUCT’s core behavior. A weighted score, for example, combines each score with its weight rather than treating every score equally. If scores are in A2:A4 and their weights are in B2:B4, with scores 80, 90, and 70 and weights 20%, 50%, and 30%, the weighted average is calculated by dividing the weighted sum by the sum of the weights:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

The result is 81: (80×0.2 + 90×0.5 + 70×0.3) divided by 1. This example assumes the weights are stored as numeric percentages and total 100%. If the weights do not add to 100%, dividing by their sum still gives a normalized weighted average, provided the sum is not zero. In a formula like this, check that the weights are numeric and that the denominator cannot be zero.

Which function should you choose?

Choose the simplest function that matches the calculation. A straightforward total is easier to understand and maintain with SUM. For a conditional total, try SUMIF or SUMIFS when their criteria-based syntax fits. Choose SUMPRODUCT when you need row-by-row multiplication, multiple array calculations, or a conditional calculation that is awkward to express with the dedicated functions.

  • Use SUM for adding a range of amounts, hours, quantities, or other numeric values.
  • Use SUMPRODUCT for quantity-by-price totals, weighted sums, and calculations that multiply corresponding arrays.
  • Use SUMIF for a total based on one criterion, and SUMIFS for a total based on several criteria, when their straightforward syntax covers the task.
  • Use a helper column when showing each row’s intermediate result will make the workbook easier for other people to check.

In Microsoft 365 for Windows, functions such as FILTER can also return matching rows for further calculations, but this is a different approach: it creates a dynamic array result rather than simply returning a conditional total. It is available in Microsoft 365 and Excel 2021 or later. For a workbook shared with users of older Excel versions, check function compatibility before replacing a more widely supported formula.

Common errors and ways to avoid them

A frequent cause of trouble with SUMPRODUCT is arrays that do not line up. If one range is A2:A20 and another is B2:B19, Excel cannot pair every item consistently. Use ranges with the same start and end rows, and check that criteria ranges cover exactly the same records as the values being summed.

Other issues are less obvious. Numbers stored as text may not behave like numeric values in arithmetic, and errors in referenced cells can flow through to the result. In SUMPRODUCT, raw nonnumeric entries in array arguments are treated as zero, but it is safer not to depend on that when formulas or mixed data types are involved. Check the source cells, especially after importing data from a website or text file.

  • Keep ranges limited to the relevant data rather than using entire columns in a large SUMPRODUCT calculation. It may need to evaluate many cells, which can slow recalculation.
  • Confirm that quantities, prices, and weights are real numbers, not text that only looks numeric.
  • Use parentheses around each condition in a multi-condition expression, and verify that the criteria text matches the worksheet values, including spaces.
  • Test a small sample by calculating a few matching row products manually. This can expose a wrong range or a condition that includes too many rows.

For a normal range total, using SUM instead of SUMPRODUCT is not just a style preference: the simpler formula communicates the intent more directly. Conversely, replacing a conditional SUMPRODUCT with a plain SUM will not make Excel apply the condition automatically; the condition has to be represented by an appropriate function or calculation.

Frequently Asked Questions

Is SUMPRODUCT better than SUM?

No. Each function is suited to a different task. SUM is usually better for a basic addition because it is direct and easy to read. SUMPRODUCT is useful when values need to be multiplied by corresponding values or filtered through array conditions.

Can SUMPRODUCT add a range without multiplying it?

Yes. With a single array argument, SUMPRODUCT sums the values in that array. However, if the task is simply to total a range, SUM is normally the clearer choice.

Does SUMPRODUCT need Ctrl+Shift+Enter?

No. In current Excel, including Microsoft 365 for Windows, enter a SUMPRODUCT formula normally and press Enter. Ctrl+Shift+Enter is associated with certain legacy array formulas, not the usual way to enter SUMPRODUCT.

Should I use SUMPRODUCT or SUMIFS for conditions?

For a conditional sum that fits its criteria arguments, SUMIF or SUMIFS is often more readable. SUMPRODUCT is a flexible alternative when the calculation also needs array arithmetic or a combination of tests that is inconvenient to express with those functions.

Why does SUMPRODUCT return an error?

Check that all arrays have the same dimensions, then inspect the referenced cells for errors or unexpected data types. A mismatched range size is a common cause; a source-cell error can also appear in the final result. Limiting ranges to the same rows and testing the formula on a small sample can help locate the problem.

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.