Excel SUMPRODUCT Shortcut: Formula, Uses, and Examples

Excel has no dedicated keyboard shortcut for SUMPRODUCT: enter the formula in a cell and press Enter. For example, with quantities in B2:B6 and prices in C2:C6, use =SUMPRODUCT(B2:B6,C2:C6); if the rows contain 2 and $5, 3 and $7, 1 and $12, 4 and $3, and 2 and $6, the result is $67 (10 + 21 + 12 + 12 + 12).

What “SUMPRODUCT shortcut” means

People searching for an “Excel SUMPRODUCT shortcut” may mean a keyboard command, a quick way to insert the function, or a shorter way to multiply values and total them. The distinction matters: SUMPRODUCT is a worksheet function, not a command with its own dedicated key combination. You enter it as a formula, and Excel calculates the result.

If you want help inserting a function, on Windows you can press Shift+F3 to open the Insert Function dialog. You can also type =SUMPRODUCT( in a cell and select the ranges with the mouse or keyboard. In Excel for Mac, use Formulas > Insert Function if you prefer the function dialog; keyboard mappings can vary with the keyboard and system settings. Neither method is a special SUMPRODUCT-only shortcut.

Another shortcut people sometimes confuse with SUMPRODUCT is Alt+=, which inserts an AutoSum formula in many Excel contexts. AutoSum is intended to add a range; it does not automatically create a row-by-row multiplication formula. For a total such as quantity times unit price, SUMPRODUCT is the direct tool.

How the function works

SUMPRODUCT multiplies corresponding items in one or more arrays, then adds the products. In the opening example, it pairs B2 with C2, B3 with C3, and so on. The ranges must have matching dimensions: five quantities can pair with five prices, but a five-row range cannot pair with a six-row range.

The function can accept multiple arrays separated by commas. It can also work with expressions that create arrays of values, which is what makes it useful for conditional calculations. In Microsoft 365 for Windows, ordinary SUMPRODUCT formulas are confirmed with Enter. You do not need Ctrl+Shift+Enter, even when the formula performs array calculations. That is a common misconception based on older Excel array formulas.

For clarity and reliable results, use ranges of the same shape and keep the formula’s inputs easy to inspect. A formula can return a plausible number even if the wrong rows are included, so check that each pair of values belongs together and that the selected ranges cover all intended records.

Calculate a total from paired values

One of SUMPRODUCT’s most practical uses is calculating an extended total without adding a helper column. Suppose B2:B6 contains item quantities and C2:C6 contains the corresponding unit prices. With the example values from the opening paragraph, the formula =SUMPRODUCT(B2:B6,C2:C6) returns $67.

This is equivalent to calculating each row’s quantity multiplied by its price, then adding those results. A helper column can make the row calculations visible, which may be preferable when people need to audit each line. SUMPRODUCT is convenient when the intermediate products are not needed elsewhere.

It is also useful for totals such as hours multiplied by hourly rates, units sold multiplied by margins, or monthly usage multiplied by a per-unit charge. The important condition is that the two arrays line up row by row. If a quantity is paired with the wrong price because one range starts on a different row, Excel can calculate the formula without warning, but the total will be wrong.

Sum values that meet criteria

SUMPRODUCT can sum a range only where a related range meets a condition. With regions in A2:A7 and sales amounts in B2:B7, assume the six rows are East $120, West $80, East $150, West $90, East $60, and West $200. The following formula returns $330 by adding the three East amounts: =SUMPRODUCT((A2:A7="East")*B2:B7).

The comparison creates TRUE or FALSE results for the region range. Multiplication converts those results into 1s and 0s, so qualifying sales remain in the total and other rows contribute zero. This pattern can be extended to more than one condition. With regions in A2:A7, categories in B2:B7, and amounts in C2:C7, suppose the rows are East/Hardware/$100, East/Service/$50, West/Hardware/$80, East/Hardware/$75, West/Service/$60, and East/Service/$40. This formula returns $175 for East Hardware: =SUMPRODUCT((A2:A7="East")*(B2:B7="Hardware")*C2:C7).

For simple conditional totals, SUMIFS is often easier to read and is purpose-built for summing by criteria. With the same regions in A2:A7 and sales amounts in B2:B7, the East total is $330 with =SUMIFS(B2:B7,A2:A7,"East"). SUMPRODUCT is particularly helpful when the calculation involves multiple array operations that are awkward to express with SUMIFS.

Calculate a weighted average

A weighted average gives some values more influence than others. With scores in B2:B5 of 80, 90, 70, and 100, and corresponding weights in C2:C5 of 1, 2, 1, and 2, use =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). The weighted total is 530, the weights add to 6, and the result is 88.333…, or about 88.33 when displayed to two decimal places.

This differs from the ordinary average of the four scores, which gives every score equal influence. Here, the scores with weight 2 count twice as much as those with weight 1. The weights do not have to add up to 100, but the denominator must represent the total of the weights used. If the weights sum to zero, the division cannot produce a meaningful weighted average.

When to choose SUMPRODUCT instead of another formula

Use the simplest function that clearly expresses the calculation. For a plain total, SUM is easier to understand. For criteria-based totals, SUMIFS is commonly clearer. SUMPRODUCT is a good fit when you need to multiply corresponding values, combine several conditions in one expression, or perform a calculation that benefits from array operations.

Choose SUM to add a single range without row-by-row multiplication or criteria.
Choose SUMIFS for a conventional sum based on one or more criteria ranges.
Choose SUMPRODUCT for paired multiplication, weighted calculations, or conditions combined with arithmetic.
Choose a helper column when showing each row’s intermediate result will make the workbook easier to check or maintain.

There is no performance or readability benefit in using SUMPRODUCT merely because it is available. A straightforward SUMIFS formula may be more familiar to another workbook user. Conversely, several nested calculations may be more compact in SUMPRODUCT. Prefer clarity over minimizing the number of characters in a formula.

Common errors and ways to avoid them

A mismatched range size is a frequent cause of an error. Check that every array covers the same number of rows and columns, and that they begin and end on the intended records. For example, pairing B2:B20 with C3:C21 shifts the matches by one row even though the ranges have equal lengths; Excel may return a number, but it will pair the wrong items.

Pay attention to data types as well. Numbers imported as text, inconsistent labels such as “East” and “East ”, and unexpected blanks can affect a calculation. If a result seems off, inspect the source cells and verify that text labels match exactly. In a conditional formula, make sure criteria are written as intended and that the amount range corresponds to the same records.

Avoid using entire-column references in complex SUMPRODUCT calculations unless there is a good reason. A formula that evaluates a million-row column can require much more work than one limited to the actual data. Use a realistic range, or, for a growing dataset, consider placing the data in an Excel table and using structured references where suitable.

Finally, distinguish a formula error from a wrong answer. A size mismatch may produce an error, but an off-by-one range can return a believable total. Verify a few individual row products or compare the result with a small manual calculation before relying on a critical report.

Entering and checking the formula

To use SUMPRODUCT in Excel for Microsoft 365 on Windows, select the result cell, type the formula beginning with an equals sign, select or type the ranges, close the parenthesis, and press Enter. Use commas between separate function arguments in standard English-language Excel formula syntax. If Excel is configured for a locale that uses different list separators, its regional settings may affect the separator displayed in formulas.

After entering the formula, inspect the result cell and the formula bar. If a result is displayed as currency or a percentage, that is cell formatting; formatting changes how a number appears, not the calculation itself. To show more decimal places, use the number-format controls on the Home tab. If the displayed result is not what you expect, check the values, criteria, range boundaries, and cell format before replacing the formula.

Frequently Asked Questions

Is there a keyboard shortcut for SUMPRODUCT in Excel?
No. Excel does not assign SUMPRODUCT its own dedicated keyboard shortcut. Type the function as a formula and press Enter. Shift+F3 on Windows opens the Insert Function dialog, but it is not exclusive to SUMPRODUCT.

Do I need Ctrl+Shift+Enter for SUMPRODUCT?
No. In Microsoft 365, enter a SUMPRODUCT formula with Enter. SUMPRODUCT is designed to process its arrays as part of the function and does not require the legacy Ctrl+Shift+Enter array-entry step.

What does SUMPRODUCT do in Excel?
It multiplies corresponding values in arrays and adds the products. It can also combine conditions with array calculations, which allows it to total values only for rows that meet specified criteria.

Why does my SUMPRODUCT formula return an error?
Check that the arrays have matching dimensions and that any arithmetic expressions use compatible data. Also inspect imported text values and the formula’s parentheses and separators. A range that is merely shifted may return a number rather than an error, so check alignment too.

Is SUMPRODUCT better than SUMIFS?
Not in every case. SUMIFS is often clearer for ordinary totals based on criteria. SUMPRODUCT is useful when you need paired multiplication, weighted calculations, or more involved array logic. Choose the formula that is easiest to verify and maintain.

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.