In Excel, SUMPRODUCT multiplies corresponding values in one or more arrays and adds the resulting products. It is especially useful for totals and conditional calculations, and in Microsoft 365 you can enter it as a regular formula—no Ctrl+Shift+Enter array-formula shortcut is needed.
What SUMPRODUCT means by an array
An array is a collection of values that Excel can process together. A worksheet range such as A2:A5 is an array of four cells; a calculation such as C2:C5*D2:D5 can produce an array of four products. SUMPRODUCT combines values in matching positions, then sums those results.
For example, if one range contains quantities and another contains prices, Excel pairs the first quantity with the first price, the second with the second, and so on. It does not multiply every quantity by every price. That distinction is why the ranges normally need the same number of rows and columns.
The function name describes its basic operation: sum the products. Its syntax is SUMPRODUCT(array1, [array2], ...). The first array is required; later arrays are optional. A single array is also valid, though it simply sums its numeric values. In practice, the function is most useful when multiplying corresponding values or when criteria create arrays of TRUE and FALSE results.
How the calculation works
Suppose quantities are in A2:A5 and unit prices are in B2:B5. The formula below multiplies each row’s quantity by its price and adds the four row totals. With quantities 2, 3, 4, and 1 and prices 5, 2, 3, and 8, the products are 10, 6, 12, and 8, for a total of 36.
With quantities in A2:A5 and prices in B2:B5, enter:
=SUMPRODUCT(A2:A5,B2:B5)
You can also put the multiplication inside the function. With the same layout, this is equivalent:
=SUMPRODUCT(A2:A5*B2:B5)
For everyday work, the comma-separated form makes the paired arrays clear. The second form can be handy when combining several calculations inside one argument. In either case, SUMPRODUCT performs the element-by-element operation and returns one total in the formula cell; it does not spill the individual products into neighboring cells.
Using criteria inside SUMPRODUCT
A comparison such as B2:B6=”East” evaluates each cell and creates an array of TRUE and FALSE values. Multiplying that result by numeric values makes TRUE behave as 1 and FALSE as 0. The rows that meet the criterion remain in the calculation; the other rows contribute zero. This is a common way to calculate a conditional total.
For example, assume regions are in B2:B6, units in C2:C6, and prices in D2:D6. Suppose the rows contain East, West, East, North, East; units 2, 3, 1, 5, 4; and prices 10, 7, 8, 2, 5. The East rows total 20 + 8 + 20, or 48. With that layout, use:
=SUMPRODUCT((B2:B6="East")*C2:C6*D2:D6)
Parentheses help show where the comparison array begins and keep the formula readable. Multiplication is doing two jobs here: it combines units and prices, and it filters out rows that do not match East. Text criteria should be enclosed in straight double quotes; a criterion can also refer to a cell, such as E1, so the region can be changed without editing the formula.
With the same layout and the criterion stored in E1, use:
=SUMPRODUCT((B2:B6=E1)*C2:C6*D2:D6)
Two conditions work in the same way. If B contains the region and A contains product names, for instance, multiply the two comparisons by the values to total only rows meeting both tests. Because both comparisons must be TRUE for a row to contribute, multiplication represents an AND condition.
With product names in A2:A6, regions in B2:B6, units in C2:C6, prices in D2:D6, and the desired product and region in F1 and G1, use:
=SUMPRODUCT((A2:A6=F1)*(B2:B6=G1)*C2:C6*D2:D6)
Counting matches and calculating averages
SUMPRODUCT can count rows that satisfy conditions by adding the resulting 1s and 0s. For example, if region names are in B2:B6, this counts the rows marked East. In the sample region list above, the result is 3.
With regions in B2:B6, enter:
=SUMPRODUCT(--(B2:B6="East"))
The double unary operator, written as --, converts TRUE to 1 and FALSE to 0. In many formulas, multiplication by another numeric array already performs that conversion, but the double unary makes the intention explicit when counting. For a straightforward count of one condition, COUNTIF is often easier to read; SUMPRODUCT is more useful when the count involves several arrays or more involved logic.
A weighted average is another natural use. It multiplies each value by its weight, adds those products, then divides by the total weight. If quantities are in A2:A5 and prices in B2:B5, using the sample values above, the weighted average price is 36 divided by 10, or 3.6. This differs from the simple average of the four prices because the quantities are not all equal.
With quantities in A2:A5 and prices in B2:B5, enter:
=SUMPRODUCT(A2:A5,B2:B5)/SUM(A2:A5)
Check that the weights have a meaningful total. If all weights are zero, the denominator is zero and Excel returns a division error. A weighted average also answers a different question from an ordinary average, so choose it only when the weights represent the relative importance or quantity of each value.
SUMPRODUCT compared with SUMIF and SUMIFS
SUMPRODUCT is flexible, but it is not automatically the best choice for every conditional total. For a simple sum based on one condition, SUMIF is usually shorter. For multiple conditions, SUMIFS is often easier to understand and can be more efficient on large ranges. For example, SUMIFS can sum a sales column where a region column equals East and a product column equals a chosen product without building multiplication arrays.
SUMPRODUCT is a good fit when the formula needs to multiply values row by row, calculate a weighted result, or combine criteria with arithmetic in one expression. It can also express calculations that are awkward to write as a basic SUMIFS formula. When a formula is shared with colleagues, favor the clearest function that handles the requirement; compactness alone is not a reason to choose SUMPRODUCT.
In Microsoft 365, dynamic-array functions such as FILTER can return matching rows for inspection, while SUMPRODUCT returns an aggregate number. Those functions answer different questions: use FILTER when you need the matching records themselves, and a conditional sum function or SUMPRODUCT when you need a single calculated result.
Common errors and ways to avoid them
- Ranges have different dimensions. If one array has five rows and another has four, the paired calculation cannot be aligned properly and may return
#VALUE!. Make each corresponding range the same height and width. - Full-column references slow the workbook. SUMPRODUCT may evaluate many cells, so references such as A:A can make a large workbook sluggish. Use bounded ranges or, where appropriate, Excel table columns that expand with the data.
- Text or blanks appear in numeric ranges. SUMPRODUCT treats nonnumeric entries in array arguments as zero, but unexpected text can still signal a data-quality issue. Check that quantities, prices, and weights are stored as numbers.
- An error exists in a referenced cell. Errors such as
#N/Ain a calculation range can flow through to the result. Find and resolve the source error rather than hiding it without understanding why it occurred. - A condition is typed incorrectly. Text criteria need quotes, while a cell reference used as the criterion does not. Check spelling, extra spaces, and whether the data uses consistent labels.
- The result is zero unexpectedly. Confirm that the criteria actually match the data, that the ranges line up row by row, and that values intended as numbers are not stored as text.
In Microsoft 365 for Windows, edit a formula in the formula bar or directly in the cell and press Enter. Unlike older array formulas, a normal SUMPRODUCT formula does not require Ctrl+Shift+Enter. On Mac, press Return to confirm the formula; the same no-special-array-entry rule applies. Excel for the web also supports SUMPRODUCT, though the exact keyboard behavior can depend on the browser and device.
Practical habits for readable formulas
Start by labeling the inputs clearly: criteria ranges, values to multiply, and any criterion cells. Keep each range aligned to the same records, and avoid mixing headers into the data. If a formula has several conditions, add line breaks while editing where useful, or use cell references for criteria so someone can change the inputs without rewriting the formula.
Tables can help keep source data organized and automatically include new rows in many formulas, but structured references can make a long SUMPRODUCT formula harder to read. For a workbook used by others, add a short note explaining what the total represents, especially for weighted averages or multi-condition calculations. Before relying on the result, test a small sample manually: verify which rows qualify and calculate a few products independently.
Frequently Asked Questions
Do I need Ctrl+Shift+Enter for SUMPRODUCT?
No. In current Excel for Microsoft 365, enter SUMPRODUCT as a regular formula and press Enter on Windows or Return on Mac. The function handles the array calculation internally.
Why does SUMPRODUCT return #VALUE?
A frequent cause is arrays with incompatible dimensions, such as a five-row range paired with a four-row range. Errors in referenced cells can also affect the result. Check range sizes and inspect the source data.
Can SUMPRODUCT use more than one condition?
Yes. Multiply each comparison array by the other conditions and by the numeric values to total rows where all conditions are met. Make sure every range covers the same records.
Does SUMPRODUCT multiply every value by every other value?
No. It pairs values by position: the first item in one array with the first in the next, then the second with the second, and so on. It sums those paired products.
When should I use SUMPRODUCT instead of SUMIFS?
Use SUMIFS for clear, conventional conditional sums. SUMPRODUCT is particularly useful when you need row-by-row multiplication, a weighted calculation, or a combination of criteria and arithmetic that is not as direct with SUMIFS.
Related
- Monthly Excel Budget Format: Build a Simple Template
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel by Highlighted Rows: Step-by-StepYes. In Excel for Microsoft 365 on Windows, select the entire table, open Data > Sort, set Sort…
- Excel VLOOKUP Multiple Values: Formulas That WorkTo return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With…
- Excel Character Count: Use the LEN FormulaTo count characters in an Excel cell, use the LEN function: if the text is in A2, enter…
- SUMIFS Excel Expression: Syntax and ExamplesThe SUMIFS Excel expression adds numbers that meet multiple conditions. Its basic form is =SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Lee"): this adds values…
- Excel SEQUENCE Shortcut: Fast Ways to Create a SeriesExcel has no dedicated keyboard shortcut for the SEQUENCE function. In Microsoft 365 or Excel 2021 and later,…
- VLOOKUP Between Two Excel Workbooks: A Simple GuideYes. In Excel for Microsoft 365 on Windows, VLOOKUP can search a table in another workbook by using…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.