In Excel for Microsoft 365 on Windows, SUMPRODUCT multiplies corresponding values in arrays and adds the products. If quantities are in B2:B5 and prices are in C2:C5, enter =SUMPRODUCT(B2:B5,C2:C5) to calculate their combined value without creating a separate multiplication column.
What SUMPRODUCT Does
SUMPRODUCT is a worksheet function for combining multiplication and addition. It takes one or more arrays, multiplies values in matching positions, then returns the sum of those products. With two four-cell ranges, Excel pairs the first cell in one range with the first cell in the other, then does the same for the second, third, and fourth positions.
For example, if B2:B5 contains 3, 5, 2, and 4 units, and C2:C5 contains prices of 12, 8, 15, and 6, the formula returns 130: (3 × 12) + (5 × 8) + (2 × 15) + (4 × 6). The result is the total value of all four rows. SUMPRODUCT is useful for totals, weighted calculations, and calculations that include conditions.
Despite its name, the function does not only multiply two lists. It can take several arrays, and it can use logical tests inside the formula to include or exclude rows. It is also not limited to product or inventory data: the same approach can calculate weighted scores, commissions, or counts that meet multiple criteria.
How to Enter the Function
Click the cell where you want the answer, type an equals sign and the function, then select or type the ranges separated by commas. Press Enter to calculate. In Microsoft 365 for Windows, formulas use commas as argument separators under the usual regional settings; some regional settings use semicolons instead.
For the quantity-and-price layout in B2:B5 and C2:C5, the formula =SUMPRODUCT(B2:B5,C2:C5) multiplies corresponding cells and returns 130 using the example values above. The ranges should have the same number of rows and columns. SUMPRODUCT returns an error if array dimensions do not match.
Excel’s Function Arguments dialog is another way to enter the function: select the Insert Function button next to the formula bar, search for SUMPRODUCT, and fill in the array arguments. In Excel for Mac, the function works the same way; ribbon placement and keyboard shortcuts may differ, but the formula syntax is unchanged. You do not need to enter SUMPRODUCT as an array formula with Ctrl+Shift+Enter.
Calculate a Weighted Total or Average
A common use is multiplying a quantity by a unit value for every row and adding the results. This avoids adding a helper column solely to calculate each row’s product. It is especially convenient when a table has many records or when the calculation is used in a summary cell.
To calculate a weighted average, divide the total of value-times-weight by the total weight. Suppose scores are in B2:B4 and their weights are in C2:C4. With scores of 80, 90, and 70, and weights of 20, 50, and 30, the weighted average is 82: (80 × 20 + 90 × 50 + 70 × 30) ÷ 100.
With scores in B2:B4 and weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). This returns 82 for the example. The denominator matters: if the weights do not add up to 100, dividing by 100 would not generally give the correct weighted average. Check that weights are numeric and that the total weight is not zero.
Use Criteria to Include Specific Rows
SUMPRODUCT can apply conditions by turning comparisons into arrays of TRUE and FALSE values. In arithmetic inside the function, those logical values are treated as 1 and 0: a TRUE condition allows the row’s value to contribute, while a FALSE condition makes its product zero. Multiplying conditions together gives an AND test, so every condition must be true for a row to count.
Suppose regions are in A2:A5, quantities in B2:B5, and prices in C2:C5. Using the example rows North/3/12, South/5/8, North/2/15, and East/4/6, the formula =SUMPRODUCT((A2:A5="North"),B2:B5,C2:C5) returns 66. Only the two North rows contribute: 3 × 12 plus 2 × 15.
For the same region layout, =SUMPRODUCT((A2:A5="North")*(B2:B5>2)) counts rows where the region is North and the quantity is greater than 2. It returns 1 for the example. The multiplication between the tests acts as AND; it does not multiply the quantities into the result.
For an OR test, add the conditions and test whether the sum is greater than zero. With regions in A2:A5 and sales values in B2:B5, =SUMPRODUCT(((A2:A5="North")+(A2:A5="South")>0)*B2:B5) totals sales for either region, counting each matching row once. The greater-than-zero test is important when a row could meet more than one condition, because simply adding condition arrays can otherwise count that row twice.
Count Matches and Apply Date Ranges
For a count, use a condition as the array being summed. With region names in A2:A100, =SUMPRODUCT(--(A2:A100="North")) counts the cells that match North. The double unary operator, written as two minus signs, converts TRUE and FALSE into 1 and 0. Multiplication by 1 can serve the same conversion purpose in many SUMPRODUCT formulas.
Date conditions work like other comparisons because Excel stores dates as serial numbers. Suppose dates are in A2:A100 and amounts are in B2:B100. To total amounts on or after January 1, 2026, but before January 1, 2027, use =SUMPRODUCT((A2:A100>=DATE(2026,1,1))*(A2:A100<DATE(2027,1,1))*B2:B100). The inclusive start and exclusive end include every time on the final day if the date cells contain times as well as dates. DATE constructs the boundary dates without relying on how a regional setting displays date text.
SUMPRODUCT Compared with Other Functions
For a plain sum, SUM is simpler and more direct. For a total based on one or more straightforward conditions, SUMIFS is often easier to read and may be more efficient on large ranges. For a count, COUNTIF or COUNTIFS usually communicates the intent more clearly than a SUMPRODUCT formula. Choose SUMPRODUCT when you need to multiply corresponding values, combine arithmetic with conditions, or express a calculation that is awkward to write with those functions.
For example, if regions are in A2:A100 and sales are in B2:B100, =SUMIFS(B2:B100,A2:A100,"North") totals sales for North. SUMPRODUCT can produce the same result, but the SUMIFS version makes the criteria and sum range explicit. If the task is instead to multiply each row’s quantity by its price and total the products, SUMPRODUCT expresses that calculation directly.
Neither option is automatically best for every workbook. Readability for the next person who maintains the file matters. If a complicated SUMPRODUCT is difficult to audit, helper columns or separate calculations may be easier to check. For large datasets, consider Excel tables, pivot tables, or other tools appropriate to the task rather than building a single dense formula.
Common Errors and Performance Tips
A #VALUE! error commonly means the arrays have incompatible dimensions, or that part of an expression cannot be evaluated as expected. Check that every range starts and ends on the same rows. Also inspect cells containing errors such as #N/A: an error in a referenced array can flow through to the result.
Text in a numeric range is another source of confusing results. SUMPRODUCT treats text values in referenced arrays as zero, so a number stored as text may silently be left out of a total. Check that quantities and amounts are stored as numbers, not just displayed like numbers. Blank cells generally contribute zero. If a result is unexpectedly zero, verify the criteria text for extra spaces or spelling differences, and check that dates are real Excel dates rather than text strings.
A few habits make formulas easier to maintain:
- Use ranges of matching size and keep headers out of the calculation ranges.
- Avoid full-column references such as A:A in SUMPRODUCT formulas over large sheets; bounded ranges reduce the number of cells Excel must evaluate.
- Use clear criteria and, where practical, put changeable criteria in cells instead of typing them into a long formula.
- Check a small sample by calculating a few row products manually or with a temporary helper column.
SUMPRODUCT is not necessarily slow in every workbook, but formulas that evaluate hundreds of thousands of cells repeatedly can add calculation work. If a workbook grows, use only the needed rows or convert the source data to an Excel table and use appropriately sized references. Keep an eye on whether a formula recalculates across many summary cells.
Frequently Asked Questions
Do I need Ctrl+Shift+Enter for SUMPRODUCT?
No. Enter the formula with Enter in current Excel versions, including Microsoft 365. SUMPRODUCT handles its array calculations internally; Ctrl+Shift+Enter is not required.
Can SUMPRODUCT use more than two arrays?
Yes. It accepts multiple arrays, and corresponding values are multiplied together before the products are summed. Make sure the arrays have compatible dimensions and that adding another array matches the calculation you intend.
Can SUMPRODUCT count cells that meet multiple conditions?
Yes. Multiply logical tests to require all conditions, then use a double unary operator or another numeric conversion to count the matches. Use COUNTIFS instead when it expresses the same count more simply.
Why does SUMPRODUCT return zero when cells look numeric?
The values may be stored as text, or no rows may satisfy the criteria. Check the underlying cell values, the criteria spelling and spacing, and whether dates are actual Excel date values. Formatting a text value to look like a number does not convert it into a number.
Is SUMPRODUCT better than SUMIFS?
Not universally. SUMIFS is usually clearer for conditional sums that do not require multiplying values row by row. SUMPRODUCT is useful when the calculation combines products and criteria or needs array arithmetic that SUMIFS does not express as directly.
Choosing a Formula You Can Maintain
Start by writing down what each row represents and what should contribute to the answer. For a weighted total, identify the paired numeric ranges. For a conditional calculation, specify whether the conditions mean AND or OR, and decide how boundary cases such as dates should be treated. Then test the formula against a small set of known values.
SUMPRODUCT is a flexible tool, but its flexibility can make dense formulas harder to inspect. Use it when the multiplication-and-sum model fits the calculation, keep ranges aligned, and choose a simpler function when it says the same thing more clearly. A result that is easy to verify is more useful than a compact formula whose logic is hard to follow.
Related
- Stop Excel Converting Text to Dates Before It Happens
- Sorting by Dates in Excel: A Clear Step-by-Step Guide
- Snowflake CSV vs CSV for Excel: Which Should You Choose?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF: Count Values Greater Than a Number EasilyYes. To count values greater than a number with Excel COUNTIF, use a criterion such as ">100". If…
- Excel Text Functions List: Names, Uses, and ExamplesExcel’s text functions help you extract, combine, clean, compare, format, and convert text or values. The main functions…
- Excel COUNTIF: How to Count Matching CellsIn Excel, use COUNTIF to count cells in a range that meet one condition: enter =COUNTIF(range, criteria). For…
- Excel Day Value: Serial Dates and the DAY FunctionAn Excel day value can mean either the serial number Excel uses to store a date or the…
- Extract the First Word from a Cell in Excel with a FormulaTo extract the first word from a cell in Excel, enter =LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1) in an empty cell,…
- Excel Calculation for Hours Worked: Formulas and TipsFor a shift with start time in A2, end time in B2, and unpaid break duration in C2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.