Excel’s SUMPRODUCT multiplies corresponding values in ranges and adds the results, so it can calculate totals such as units sold times unit price. With product names in A2:A7, regions in B2:B7, units in C2:C7, and prices in D2:D7, the formula =SUMPRODUCT(C2:C7,D2:D7) returns total sales revenue: $145.75 for the sample data below.
Sample data to enter in Excel
Enter this small sales table in a worksheet, starting at cell A1. The dollar signs indicate prices for readability; enter the prices as numbers and apply currency formatting if desired.
| Product | Region | Units | Unit price |
|---|---|---|---|
| Notebook | East | 12 | $3.50 |
| Pen | East | 20 | $1.25 |
| Notebook | West | 8 | $3.50 |
| Pen | West | 15 | $1.25 |
| Folder | East | 10 | $2.00 |
| Folder | West | 6 | $2.00 |
With the headers in row 1, the data ranges are A2:A7 for products, B2:B7 for regions, C2:C7 for units, and D2:D7 for unit prices. Revenue by row is $42.00, $25.00, $28.00, $18.75, $20.00, and $12.00. Their sum is $145.75, which makes the example easy to check against Excel’s result.
How SUMPRODUCT works
SUMPRODUCT takes corresponding positions from one or more arrays, multiplies them together, and sums the products. For the sales example, it pairs the first units value with the first price, the second units value with the second price, and so on. In plain language, it calculates (12 × 3.50) + (20 × 1.25) + (8 × 3.50) + (15 × 1.25) + (10 × 2.00) + (6 × 2.00).
The function is useful when each row represents one item or transaction and you need a total based on paired columns. It can also combine conditions with calculations, which is why it appears in examples for conditional sums, counts, and weighted averages. It does not require a special keystroke or array-entry mode in Microsoft 365 for Windows: enter the formula in a cell and press Enter.
Calculate a basic extended total
To calculate revenue across all six rows, use the units and price ranges. With units in C2:C7 and unit prices in D2:D7, enter =SUMPRODUCT(C2:C7,D2:D7). The result is $145.75. Format the result cell as Currency or Accounting if it displays without a dollar sign; number formatting changes how a value appears, not the underlying calculation.
You can also use a single range when the values themselves are the amounts to add. With amounts in E2:E7, for example, =SUMPRODUCT(E2:E7) adds those values. In that case, SUM is usually simpler. SUMPRODUCT becomes more distinctive when it has multiple corresponding arrays or conditions to evaluate.
Sum revenue for one region
A common follow-up is to ask for revenue only in the East region. SUMPRODUCT can compare each cell in the region range with the requested text. Those comparisons produce TRUE or FALSE values; multiplying by the numeric ranges makes matching rows contribute their revenue and nonmatching rows contribute zero.
With regions in B2:B7, units in C2:C7, and unit prices in D2:D7, enter =SUMPRODUCT((B2:B7="East")*C2:C7*D2:D7). The result is $87.00: $42.00 from notebooks, $25.00 from pens, and $20.00 from folders in the East. The exact text criterion is in double quotation marks. Text comparisons in Excel are generally case-insensitive, so “east” matches “East.”
The condition does not have to be a fixed word in the formula. If a user-selected region is entered in F1, with the same data ranges as above, use =SUMPRODUCT((B2:B7=F1)*C2:C7*D2:D7). When F1 contains East, this returns $87.00; changing F1 to West returns $58.75. A cell reference makes the worksheet easier to reuse without editing the formula.
Apply more than one condition
Conditions can be multiplied together so that a row contributes only when every condition is true. For example, you can calculate revenue for notebooks sold in the East. With products in A2:A7, regions in B2:B7, units in C2:C7, and prices in D2:D7, enter =SUMPRODUCT((A2:A7="Notebook")*(B2:B7="East")*C2:C7*D2:D7). The result is $42.00, because only the first row meets both conditions.
The same pattern can count matching rows by summing the condition results rather than multiplying by sales values. With products in A2:A7 and regions in B2:B7, =SUMPRODUCT((A2:A7="Notebook")*(B2:B7="East")) returns 1. Each row that meets both tests contributes 1; other rows contribute 0. This is one way to count with multiple criteria, though newer Excel functions and database-style functions may be clearer for some tasks.
Use SUMPRODUCT for a weighted average
A weighted average gives some values more influence than others. For instance, a course grade might combine several assessment scores, where an exam counts more than a quiz. Suppose scores are in C2:C4 (80, 90, and 75) and their weights are in D2:D4 (20%, 30%, and 50%). With those values in those cells, enter =SUMPRODUCT(C2:C4,D2:D4)/SUM(D2:D4). The result is 80.5: the weighted points are 16, 27, and 37.5, and the weights sum to 100%.
Dividing by the sum of the weights is useful when weights may not total exactly 100%, for example because they were entered as 20, 30, and 50 rather than percentages. If the weights do not add to the intended total, the calculation still normalizes them; check that this is appropriate for the task. For grades, budgets, or other consequential calculations, confirm the required weighting rules rather than assuming a particular method.
Range rules and common errors
The most frequent source of a SUMPRODUCT problem is mismatched array sizes. The paired ranges must have the same number of rows and columns. C2:C7 and D2:D7 match; C2:C7 and D2:D8 do not. In a conditional formula, every criteria range should also line up row by row with the values being calculated.
- Use numeric values for the quantities and prices. A value stored as text may not behave like a number in a calculation.
- Keep the header row out of numeric calculation ranges. Use C2:C7 rather than C1:C7 when row 1 contains a heading.
- Check for missing or extra rows when copying a formula to another section of a worksheet.
- Use straight quotation marks around text criteria, as in
"East", and make sure the spelling matches the data. - Check the result with a small manual calculation or a filtered subset if the total is unexpected.
In Excel for Microsoft 365 on Windows, commas separate function arguments in the examples here. Some regional settings use semicolons as argument separators; if Excel rejects a correctly formed example, check the list separator configured for the computer. Excel for Mac has the same SUMPRODUCT function and calculation behavior, although regional separators can differ there as well. Excel for the web also supports SUMPRODUCT; these examples do not depend on a desktop-only feature.
When SUMIFS or another function is a better fit
For a straightforward conditional total, SUMIFS is often easier for another person to read. For example, if you first calculate each row’s revenue in E2:E7, then with regions in B2:B7 and revenue in E2:E7, =SUMIFS(E2:E7,B2:B7,"East") returns $87.00. The formula states the sum range and the condition range directly, and it can accept multiple criteria pairs.
SUMPRODUCT is particularly handy when you want to multiply quantities and prices without adding a helper column, combine conditions with that multiplication, or calculate a weighted sum. A helper column can still be a good choice when it makes row-level calculations visible and easier to audit. There is no need to choose SUMPRODUCT simply because it can do the job: choose the formula that best communicates the calculation and is easiest to maintain.
Frequently Asked Questions
What does SUMPRODUCT do in Excel?
It multiplies corresponding values in one or more arrays and adds the products. It can also evaluate conditions as part of an array calculation, such as totaling revenue for a selected region.
Do SUMPRODUCT ranges have to be the same size?
Yes. The arrays should have matching dimensions so each value lines up with its corresponding value. Ranges with different row or column counts can return an error.
Can SUMPRODUCT add values that meet a condition?
Yes. A comparison such as (B2:B7="East") can act as a condition when combined with the values to total. The sample East-region revenue formula returns $87.00.
Why is SUMPRODUCT returning zero or an unexpected total?
Check that the criteria text matches the worksheet, the calculation ranges cover the intended rows, and quantities and prices are numeric. Also verify that every range is the same size and that the formula references the correct columns.
Is SUMPRODUCT better than SUMIFS?
Not in every case. SUMIFS is often clearer for ordinary conditional sums, while SUMPRODUCT is useful for multiplying paired ranges and combining that calculation with conditions. Use the approach that makes the worksheet easiest to understand and check.
Related
- Excel Data Tips and Tricks for Cleaner, Safer Workbooks
- Record Data in Excel Online: Setup, Sharing and Tips
- Gujarati Data in Excel: Typing, Sorting, and Importing
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Percentile Function: Rank, Syntax, and ExamplesExcel has separate functions for percentiles and percentile ranks: use PERCENTILE.INC or PERCENTILE.EXC to find the value at…
- Filter Cells by Color in Excel: Steps and TipsTo filter cells by color in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- How to Hide Filter Arrows in Excel and Keep Table FiltersTo hide filter arrows in Excel without removing a table’s AutoFilter setup, click inside the table, open Table…
- VLOOKUP vs XLOOKUP: Examples and Key DifferencesVLOOKUP and XLOOKUP both find a value in a range and return related information, but XLOOKUP is generally…
- Excel Time Conversion Formula: Hours, Minutes, and MoreIf A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes,…
- Excel Date Plus Years: Formula and Leap-Year RulesTo add years to a date in Excel, use =EDATE(A2,12*B2), where A2 contains the starting date and B2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.