Excel’s SUMPRODUCT contains one required array argument and optional additional arrays; it multiplies corresponding entries across the arrays, then adds those products. It is useful for totals, weighted averages, and conditional calculations, often without adding helper columns.
What the SUMPRODUCT arguments contain
SUMPRODUCT is a function that accepts arrays: groups of values, commonly supplied as worksheet ranges. The first array is required. You can provide further arrays to multiply against it, position by position. An array might be a range such as A2:A5, or a calculation that produces an array of values.
The general pattern is to give SUMPRODUCT one or more arrays separated by commas. With one array, it adds the values in that array. With two arrays, it multiplies the first value in one by the first value in the other, repeats that operation for each position, and totals the results. With three or more arrays, it multiplies the corresponding values from every array before adding each product.
- Array1: the required first group of values.
- Array2 and later arrays: optional groups multiplied positionally with the first array.
- Calculated arrays: expressions such as comparisons can create arrays of TRUE and FALSE values that SUMPRODUCT can use in a calculation.
The word “array” does not mean that you must create a special array object. Ordinary worksheet ranges work. SUMPRODUCT also evaluates array calculations directly, so in current Excel you do not need to confirm the formula with Ctrl+Shift+Enter.
How the calculation works
Imagine that each row records a quantity and a unit price. The function pairs the quantity and price on each row, multiplies them, and adds the row totals. This is the same arithmetic as calculating each line amount separately and summing the results, but the formula can do it in one step.
With quantities in A2:A5 and unit prices in B2:B5, use =SUMPRODUCT(A2:A5,B2:B5). For quantities 2, 3, 1, and 4 and prices 5, 7, 10, and 2, the result is 49: 2×5 + 3×7 + 1×10 + 4×2. The matching positions matter: the first quantity is paired with the first price, and so on.
If you provide only a single numeric range, SUMPRODUCT adds its values. That makes the function capable of acting like SUM in a simple case, although SUM is usually clearer when no multiplication or array logic is needed. SUMPRODUCT’s main advantage is combining multiplication and addition in one calculation.
Common uses: totals and weighted averages
A frequent use is a line-item total, as in the quantity-and-price example. It also works for other paired measures, such as hours multiplied by an hourly rate or units sold multiplied by a margin per unit. The ranges should represent corresponding records and have the same number of rows and columns.
Another common use is a weighted average. A plain average treats every value equally; a weighted average gives some values more influence according to their weights. With scores in A2:A4 and weights in B2:B4, use =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). If the scores are 80, 90, and 70, and the weights are 20%, 30%, and 50%, the result is 78: the weighted score total is 78, and the weights sum to 100% (or 1). Dividing by the sum of the weights also works when the weights do not add to 100, provided their total is not zero.
Make sure the weights reflect the meaning of the data. For instance, if course grades are weighted by assessment importance, enter the intended proportions, not simply the number of assessments. A correct formula cannot compensate for incorrect or inconsistently entered weights.
Using SUMPRODUCT with conditions
SUMPRODUCT can also total values only when one or more tests are met. A comparison such as A2:A6=”North” produces TRUE for matching rows and FALSE for others. Multiplying that result by another condition and by a values range effectively keeps qualifying values and turns the other rows into zeroes.
With region names in A2:A6, product names in B2:B6, and sales amounts in C2:C6, use =SUMPRODUCT((A2:A6="North")*(B2:B6="Apples")*C2:C6). If the rows are North/Apples/10, South/Apples/20, North/Pears/5, North/Apples/7, and South/Pears/8, the result is 17. Only the two rows meeting both conditions contribute: 10 + 7.
Conditions can include comparisons such as greater than, less than, or equal to a cell value. Text criteria need quotation marks inside the formula, while a reference to a cell containing the criterion is written without quotation marks. For example, a criterion typed in E1 could replace the fixed text “North” with A2:A6=E1.
When you combine conditions with multiplication, they work like AND: every condition must be true for a row to contribute. To represent OR, add condition arrays instead, while taking care that rows meeting both conditions are not counted twice if the result is meant to count each row only once. For straightforward conditional totals, SUMIFS is often easier to read.
Range size and data requirements
All array arguments used together should have the same dimensions. For example, A2:A20 and B2:B20 are both 19 rows tall and can be paired. A2:A20 and B2:B19 are different sizes and can cause SUMPRODUCT to return a #VALUE! error. When the arrays are two-dimensional, their row and column dimensions need to match as well.
Use ranges that cover the same records. If one range starts on row 2 and another starts on row 3, the calculation may pair unrelated values even if Excel does not report a size error. This is a data-alignment problem, not a flaw in the function’s arithmetic.
For performance, prefer a realistic bounded range over entire-column references in a complex SUMPRODUCT formula. Whole-column arrays can make Excel process far more cells than the calculation needs, especially when multiple array expressions are involved. Convert a changing dataset to an Excel table when appropriate; table columns expand as rows are added and can make formulas easier to maintain.
SUMPRODUCT compared with SUM and SUMIFS
Choose a function based on what the calculation needs to express. SUM adds numbers. SUMPRODUCT combines corresponding values by position and totals the products. SUMIFS totals a values range after applying one or more criteria ranges, so it is often the clearest choice for a conventional conditional sum.
- Use SUM for a direct total with no row-by-row multiplication or criteria logic.
- Use SUMPRODUCT for products of corresponding values, weighted calculations, or a compact formula involving array conditions.
- Use SUMIFS for conditional totals when its criteria-based structure is a better fit and easier for others to audit.
SUMPRODUCT is not automatically better because it can do several kinds of calculations. A formula that is shorter but difficult to understand may be a poor choice for a shared workbook. For a basic conditional total, SUMIFS commonly makes the intent more obvious; for multiplying quantities by prices, SUMPRODUCT expresses the calculation directly.
Common problems and how to avoid them
A #VALUE! result often points to incompatible array dimensions, but an error value already present in a referenced cell can also flow into the result. Check the ranges first, then inspect the source cells for errors such as #N/A or #DIV/0!. SUMPRODUCT does not automatically repair errors in its inputs.
Text and empty cells can also affect expectations. Non-numeric entries in referenced arrays are generally treated as zero for the product calculation, while comparison expressions intentionally produce TRUE or FALSE values. If a number is stored as text, do not assume every formula will interpret it as the intended numeric value in every context. Check the cell’s contents and convert or clean inconsistent source data when needed.
Other useful checks include:
- Confirm that the first and last rows of every range refer to the same records.
- Check for numbers stored as text, unexpected blanks, and hidden error values.
- Verify that percentage weights are entered as percentages or equivalent decimals, not as whole numbers by mistake.
- Test a few row-level products separately when a total seems wrong.
- Use parentheses around each condition so the intended logic is clear.
Excel version and platform notes
SUMPRODUCT is an established Excel function and is available in Microsoft Excel for Microsoft 365 on Windows, as well as many earlier Excel releases. Its core behavior does not require a newer dynamic-array function. In Microsoft 365, ordinary Enter is sufficient; the function evaluates its array calculations without the older Ctrl+Shift+Enter workflow associated with some legacy array formulas.
The formula syntax is the same in Excel for Mac and Excel for the web. In localized Excel installations, function names and list separators may differ according to language and regional settings; formulas shown here use English function names and comma separators, as requested. The worked examples use ordinary cell ranges and do not rely on a Windows-only feature.
Frequently Asked Questions
What does SUMPRODUCT do in Excel?
It multiplies corresponding values in one or more arrays and adds the resulting products. With one array, it adds the values in that array.
Does SUMPRODUCT require Ctrl+Shift+Enter?
No. In current Excel, enter a SUMPRODUCT formula with Enter. It handles its array calculations without the special keystroke used by some older array formulas.
Why does SUMPRODUCT return #VALUE!?
Check whether the arrays have matching dimensions and whether any referenced cells contain error values. Ranges that include different numbers of rows or columns are a common cause.
Can SUMPRODUCT add values that meet multiple conditions?
Yes. Multiply condition tests together and multiply the result by the values to total. Multiplying conditions means all of them must be true for a row to count; use SUMIFS instead when that structure is clearer.
Is SUMPRODUCT the same as SUMIFS?
No. Both can produce conditional totals, but SUMIFS is designed to sum a range based on criteria, while SUMPRODUCT multiplies corresponding array values and sums those products. Pick the formula that best communicates the calculation.
Related
- Excel Sum Minus: How to Add and Subtract Numbers in Excel
- Excel SUM: How to Exclude Hidden Rows and Filtered Rows
- Excel: Sum Filtered Rows with SUBTOTAL
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Subtotal Filtered Rows: Formula, Steps, and FixesYes. In Excel for Microsoft 365 on Windows, use the SUBTOTAL function to calculate from rows that remain…
- Excel SUM Formula That Changes With FiltersUse =SUBTOTAL(109,B2:B100) to sum only the currently visible values in B2:B100. When you filter the rows, Excel recalculates…
- Excel Filters: How to Sort and Show the Rows You NeedTo filter data in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- NetSuite Saved Search Formula Examples and TipsNetSuite saved search formulas let you calculate, classify, format, and filter search results with SQL-style expressions, usually using…
- IFERROR in Excel: Leave Blank When a Formula ErrorsTo make an Excel formula show a blank instead of an error, wrap it in IFERROR and use…
- Excel Sum Between Dates: SUMIFS Formulas and ExamplesIf dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in E2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.