Excel SUMPRODUCT: How to Use It and Fix Common Errors

Errors & fixes · Updated November 20, 2026

To get SUMPRODUCT working in Excel, make sure the ranges you want to multiply have matching dimensions, then enter a formula such as =SUMPRODUCT(B2:B6,C2:C6) and press Enter. SUMPRODUCT multiplies corresponding values in the ranges and adds those products, making it useful for weighted totals and calculations with conditions. You will need a worksheet with clearly organized data and numeric values in the ranges you want to calculate.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The basic SUMPRODUCT function works in current versions of Excel.
  • A worksheet with the values you want to multiply arranged in corresponding rows or columns.
  • Matching range sizes: for example, two ranges that both cover rows 2 through 6.

Step-by-Step

  1. Arrange the data in matching rows. Put each item’s quantity in one column and its unit price in another. Keep each item’s values on the same row, and put headings in row 1. For example, column A can contain product names, column B quantities, and column C prices. Avoid inserting a total or note in the middle of the data range.
  2. Choose where the result should appear. Click an empty cell outside the data, such as E2, and type the formula there. Keeping the result out of the source ranges helps prevent accidental circular references, in which a formula includes its own cell in the calculation.
  3. Enter a basic SUMPRODUCT formula. With quantities in B2:B6 and corresponding unit prices in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6) in E2 and press Enter. Excel multiplies B2 by C2, B3 by C3, and so on, then adds the five products. The comma separates the two array arguments; it does not mean the ranges are added separately.
  4. Check the arithmetic with a small example. Suppose the five quantity-price pairs are 3 and 12, 2 and 8, 5 and 4, 1 and 25, and 4 and 6. SUMPRODUCT calculates 36 + 16 + 20 + 25 + 24, so the result is 121. If your worksheet uses those values but returns another number, inspect the ranges and individual cells before changing the formula.
  5. Use a single range when you only need a sum of its values. SUMPRODUCT can also add the values in one range. With amounts in B2:B6, this formula returns their total: =SUMPRODUCT(B2:B6). For a simple total, the SUM function is usually easier to read, but this form can be useful when you later add conditions or other arrays.
  6. Add a condition when you need a filtered total. Suppose A2:A6 contains a region for each row, B2:B6 contains units sold, and C2:C6 contains unit prices. To total revenue only for rows marked East, enter =SUMPRODUCT((A2:A6="East")*B2:B6*C2:C6) in an empty cell. The comparison creates TRUE or FALSE results; multiplication treats those results as 1 or 0, so only East rows contribute to the total. The formula still multiplies each row’s units by its price.
  7. Test each part if the conditional calculation looks wrong. Check that the region text is spelled exactly as it appears in the data, and that all three ranges start and end on the same rows. A condition such as East will not match a cell containing West. If the workbook has extra spaces in imported text, clean the source values or use a helper column to standardize them.
  8. Confirm the result and save the workbook. Compare a few rows manually, especially if the result supports a report or decision. Format the result cell as Number or Currency if needed; number formatting changes how a value appears, not the underlying calculation. Save the file once the formula and source ranges are correct.

Common Mistakes

Using ranges with different dimensions: The corresponding arrays must have compatible shapes. For example, B2:B6 and C2:C5 do not describe the same five rows. Depending on the formula and Excel version, incompatible dimensions can produce an error or an unexpected result. Make the starting and ending rows match across every array.

Including the result cell in a source range: If a formula is in E2, do not include E2 in one of its input ranges. A formula that refers to itself can trigger a circular reference warning or calculation problems. Place the result outside the source data and check the range references carefully.

Assuming visible values are numeric: Data imported from a website or text file may contain numbers stored as text. Some calculations may coerce numeric text, but inconsistent entries, currency symbols, spaces, or other text can stop the expected arithmetic. Select suspicious cells and inspect their values in the formula bar. Convert the source data to real numbers before relying on the result.

Multiplying whole columns unnecessarily: References such as B:B and C:C can make a workbook slower, particularly when combined with conditions or many SUMPRODUCT formulas. Use ranges limited to the actual data, such as B2:B500 and C2:C500, or use an Excel Table so the data range can expand as rows are added.

Forgetting that conditions need to be included in the calculation: Typing a condition in a separate cell does not automatically filter SUMPRODUCT. The condition must be part of the formula, as in the East example, or handled by another suitable function. Also, text used in a condition must match the source values; capitalization is generally not significant for ordinary Excel text comparisons, but spelling and extra spaces still matter.

Expecting a formatted value to change the math: Showing a result with two decimal places only changes its display. It does not round the stored value used in later calculations. If rounding is part of the requirement, use a rounding function deliberately rather than relying on cell formatting.

Tips

  • Build the formula with short, bounded ranges first. After it works, extend the ranges to cover the rest of the data.
  • Use consistent data types in each input column. Keep quantities numeric, prices numeric, and category labels as text rather than mixing notes into calculation cells.
  • For multiple conditions, multiply each comparison by the value ranges inside SUMPRODUCT. Make sure every comparison and value range covers the same rows. Add one condition at a time so you can identify which part changes the result.
  • Use an Excel Table when your list grows regularly. Select the data and choose Insert > Table; structured references can make formulas easier to maintain as rows are added. Check the resulting formula and table column names if you adapt a formula to a table.
  • Press F2 in Excel for Windows to edit the selected formula in its cell, then use the arrow keys to inspect references. On many Mac keyboards, use Control+U to edit a cell; the exact key behavior can depend on macOS keyboard settings. Excel for the web lets you select the cell and edit its formula in the formula bar.
  • SUMPRODUCT does not need Ctrl+Shift+Enter in current Excel. Press Enter after typing the formula. This is true in Excel for Microsoft 365, Excel for Mac, and Excel for the web.
  • Use cell references for values that may change, rather than typing changing quantities directly into a formula. That keeps the calculation traceable and easier to update.

Troubleshooting

The formula shows #VALUE!: Check each input range for error values such as #N/A or #VALUE!, since an error in a referenced calculation can flow through to the result. Also check that arrays have compatible dimensions and that arithmetic expressions are not trying to multiply incompatible data. Correct the underlying errors or isolate the problematic rows to find the source.

The result is zero: Confirm that the referenced cells contain the values you expect and that a condition actually matches at least one row. Check for extra spaces, different labels, or text-formatted numbers. Click a source cell and look at the formula bar; a number displayed in a cell can still be stored as text. Convert text values to numbers using Excel’s available conversion options or a clean helper column, then update or recalculate the formula.

The answer is too large or too small: Verify that each row pairs the correct quantity with the correct price, and that you have not included extra rows. Check for duplicated source rows and confirm that the selected price is a unit price rather than an already extended line total. Compare one or two row products by hand before checking the entire dataset.

Excel displays the formula instead of a result: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, edit the formula, and press Enter again. In Windows, Ctrl+` toggles Show Formulas; on many Mac keyboards, Control+` does the same, though the key may require a different keyboard layout. You can also check the Formulas tab for the Show Formulas control.

The result does not update after data changes: Check whether calculation is set to manual. In Excel for Windows, open Formulas > Calculation Options and select Automatic. Excel for Mac has the calculation setting on the Formulas tab as well. In Excel for the web, recalculation is generally automatic. If necessary, press F9 on Windows to recalculate; on a Mac, use the equivalent recalculation key shown for your keyboard and Excel version.

Excel rejects the formula as you type it: Check that the formula begins with an equals sign, its parentheses are balanced, and the function name is spelled SUMPRODUCT. Excel normally uses commas between arguments in the settings described here. Some regional settings use semicolons instead; if Excel flags the comma, use the list separator configured for your system. Do not change the decimal separator and list separator without considering other workbook formulas.

Frequently Asked Questions

What does SUMPRODUCT do in Excel?

It multiplies corresponding values in one or more arrays and adds the resulting products. For instance, it can multiply quantities by prices row by row to produce a total revenue figure without a separate multiplication formula for every row.

Why does SUMPRODUCT return zero?

The referenced values may be blank, text rather than numbers, or excluded by a condition that matches no rows. Check the actual source cells, range boundaries, and condition text. A zero can also be a correct result if all matching row products are zero.

Do SUMPRODUCT ranges have to be the same size?

Yes, keep the arrays aligned and the same shape for a dependable calculation. Two vertical ranges should cover the same number of rows, and each row’s values should correspond to each other. Do not pair a five-row quantity range with a six-row price range.

Can SUMPRODUCT count rows that meet conditions?

Yes. A condition comparison produces TRUE or FALSE values, which can be treated as 1 or 0 in a SUMPRODUCT calculation. Make sure the condition ranges align, and test the condition against a few rows so you know which ones should count.

Should I use SUMPRODUCT or SUMIFS?

For straightforward totals based on one or more criteria, SUMIFS is often easier to read. SUMPRODUCT is useful when a calculation needs row-by-row multiplication, or when you want to combine conditions and calculations in one expression. Choose the formula that makes the workbook easiest to check and maintain.

Does SUMPRODUCT work in Excel for Mac and Excel for the web?

Yes. SUMPRODUCT is available in current Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The basic formula syntax is the same, although keyboard shortcuts, regional separators, and some interface details can differ.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.