Advanced SUMPRODUCT formulas let Excel multiply corresponding values, apply one or more conditions, and add only the results that qualify—all in a single formula. In Microsoft 365 for Windows, a common pattern is =SUMPRODUCT((A2:A6="North")*(B2:B6)): if A2:A6 contains regions and B2:B6 contains amounts, it totals the amounts for North.
What makes SUMPRODUCT advanced?
SUMPRODUCT is often introduced as a way to multiply two lists and add the products. Its more flexible use comes from combining those lists with logical tests. A test such as whether a region equals North produces TRUE or FALSE values; multiplication coerces those values to 1 or 0. The amounts are then included when the test is TRUE and zeroed out when it is FALSE.
This gives SUMPRODUCT a role similar to a conditional sum, but it can combine arithmetic and multiple tests directly. It can also handle weighted averages, OR conditions, date windows, and some text searches. “Advanced” does not mean that the function is a separate Excel feature: it means using its array calculations to solve a more involved problem.
How SUMPRODUCT calculates a result
SUMPRODUCT takes arrays or ranges, multiplies corresponding items, and adds those products. The ranges used together should have the same number of rows and columns. If they do not, Excel commonly returns a #VALUE! error. You can supply one array, but the most familiar form uses two or more.
Assume product prices are in A2:A6 and quantities are in B2:B6: the rows contain 12 and 3, 8 and 5, 15 and 2, 10 and 4, and 20 and 1. The formula =SUMPRODUCT(A2:A6,B2:B6) returns 166, because 12×3 + 8×5 + 15×2 + 10×4 + 20×1 equals 166.
With this arrangement, the comma separates function arguments. In some Excel regional settings, formulas use semicolons instead of commas; use the separator configured for your installation. In ordinary Excel formulas, SUMPRODUCT does not require Ctrl+Shift+Enter, even though it evaluates arrays internally.
Sum values that meet one or more conditions
For a conditional total, put a logical test in parentheses and multiply it by the range to be added. Suppose A2:A6 contains North, South, North, East, South, while B2:B6 contains 120, 90, 150, 80, 110. The formula =SUMPRODUCT((A2:A6="North")*B2:B6) returns 270: the two North rows contribute 120 and 150, while the other rows contribute zero.
To require two conditions at once, multiply the tests together. Suppose C2:C6 contains Complete, Complete, Pending, Complete, Complete, in the same row order as the regions and amounts above. The formula =SUMPRODUCT((A2:A6="North")*(C2:C6="Complete")*B2:B6) returns 120. Only the first row matches both North and Complete.
For a count rather than a sum, multiply the condition by itself only as needed to turn TRUE and FALSE into numeric values. With regions in A2:A6, =SUMPRODUCT(--(A2:A6="North")) returns 2. The double unary operator, two minus signs, converts TRUE to 1 and FALSE to 0; multiplication by 1 or 0 is another common coercion method.
Handle OR logic and multiple acceptable values
Multiplication between conditions represents AND: every condition must be true for a row to contribute. Addition can represent OR, because at least one true test produces a positive value. With the same region and sales data, =SUMPRODUCT(((A2:A6="North")+(A2:A6="East"))*B2:B6) returns 350: it includes 120 and 150 for North and 80 for East.
One caution is that addition can produce 2 if a row satisfies two tests. For mutually exclusive labels such as North and East, that cannot happen, so the example is safe. If the tests can overlap, a row might be counted twice. A useful alternative is to test whether the sum of the conditions is greater than zero. With regions in A2:A6 and amounts in B2:B6, =SUMPRODUCT(--(((A2:A6="North")+(A2:A6="East"))>0),B2:B6) returns 350 and counts each matching row once.
Use SUMPRODUCT for weighted averages
A regular average gives each observation equal weight. A weighted average gives greater influence to observations with larger quantities, hours, or other weights. Divide the sum of each value multiplied by its weight by the sum of the weights.
Suppose prices are in B2:B4 as 10, 12, and 8, and quantities are in C2:C4 as 3, 2, and 5. The formula =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) returns 9.4. The weighted total is 94 (30 + 24 + 40), and total quantity is 10, so 94 divided by 10 is 9.4.
You can also limit the weighted average to qualifying rows by including a condition in the numerator and denominator. For example, suppose product names are in A2:A4, prices in B2:B4, and quantities in C2:C4, with one row per product. The formula =SUMPRODUCT((A2:A4="Tea")*B2:B4*C2:C4)/SUMPRODUCT((A2:A4="Tea")*C2:C4) calculates the weighted price for Tea rows only. If there are no matching rows, the denominator is zero; handle that case explicitly if the workbook should show a message instead of a division error.
Search text and total date ranges
For a contains-style text condition, SEARCH can look for a substring and ISNUMBER can identify matches. Suppose A2:A5 contains East cable, West cable, East adapter, and Central cable, while B2:B5 contains 20, 30, 15, and 25. The formula =SUMPRODUCT(--ISNUMBER(SEARCH("cable",A2:A5)),B2:B5) returns 75, adding the three amounts for descriptions containing “cable.” SEARCH is not case-sensitive. If the search text is absent from a cell, SEARCH returns an error, which ISNUMBER turns into FALSE for the test.
Date conditions work the same way as other comparisons. Suppose dates are in D2:D6 and amounts are in E2:E6; the rows are January 1, January 15, February 1, February 20, and March 1, 2026, with amounts 50, 75, 100, 125, and 200. The formula =SUMPRODUCT((D2:D6>=DATE(2026,1,1))*(D2:D6<DATE(2026,3,1))*E2:E6) returns 350, the total for January and February. Using a less-than March 1 boundary also includes any February dates that contain times, rather than accidentally excluding entries late on February 28.
Common errors and performance considerations
When a SUMPRODUCT formula returns an unexpected result, check the data shape and types before adding more conditions. Useful checks include:
- Matching dimensions: Every paired range should cover the same rows and columns. A2:A20 and B2:B19 do not align.
- Unintended text: A text value in a numeric range can cause trouble when that range is used in an arithmetic expression. Confirm that amounts and quantities are numbers, not numbers stored as text.
- Errors in source cells: An error in a referenced range can flow into the result. Find and correct the source error rather than assuming SUMPRODUCT will ignore it.
- Correct condition logic: Multiplying tests means AND; adding tests usually means OR. Parentheses make the intended order of operations clear.
- Zero denominators: A weighted-average formula fails if its qualifying weights sum to zero. Decide whether to show a blank, a message, or another defined result for that case.
Avoid using entire-column references in elaborate SUMPRODUCT expressions when a bounded range will do. Calculating over a defined set of rows is easier to inspect and can reduce unnecessary work, especially in a large workbook with many formulas. Excel tables are often useful because their structured references expand as rows are added; check that every referenced table column represents the same records.
When to choose SUMPRODUCT instead
For a straightforward conditional total, SUMIFS is often easier for another person to read and maintain. It is designed to sum a range using one or more criteria ranges. SUMPRODUCT is useful when the calculation combines conditions with arithmetic, uses OR logic that is awkward to express with SUMIFS, or needs a weighted calculation in the same expression. It is not automatically faster or better; choose the clearest formula that meets the requirement.
SUMPRODUCT also differs from a visible dynamic-array result. It usually returns one scalar total, not a spilled list of matching records. If the goal is to display matching rows in Microsoft 365, FILTER may be more suitable; if the goal is to add values meeting ordinary criteria, SUMIFS may be simpler. These functions solve related but different tasks.
Frequently Asked Questions
Does SUMPRODUCT require Ctrl+Shift+Enter?
No. In current Microsoft 365 Excel, SUMPRODUCT accepts its array calculations as a regular formula entered with Enter. Ctrl+Shift+Enter is associated with legacy array formulas, not a requirement for SUMPRODUCT.
Can SUMPRODUCT use more than two conditions?
Yes. You can multiply additional condition tests for AND logic, or combine tests with addition for OR logic. Keep each test aligned to the same rows as the values being calculated, and use parentheses to make the logic unambiguous.
Why does SUMPRODUCT return #VALUE!?
Common causes include incompatible range dimensions, an error in a referenced cell, or text being used in an arithmetic expression where Excel expects numeric values. Check the source ranges and simplify the formula temporarily to identify which part fails.
Is SUMPRODUCT better than SUMIFS?
Not in every case. SUMIFS is usually clearer for ordinary conditional sums, while SUMPRODUCT can be convenient for combined array arithmetic, weighted calculations, and some OR or text-search patterns. Prefer readability and reliable performance over using a more complex function just because it is available.
Does SUMPRODUCT work in Excel for Mac and Excel for the web?
SUMPRODUCT is available in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. Basic formula behavior is the same; the argument separator can vary with regional settings. If a formula does not parse, check whether that Excel installation expects commas or semicolons.
Related
- Excel Export to Tab-Delimited CSV: How to Save It Correctly
- Excel Export CSV Pipe Delimited: Steps and Options
- Excel Export to CSV with Semicolons
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Age in Excel From DD/MM/YYYY With a FormulaTo calculate someone’s age in Excel from a DD/MM/YYYY birth date, enter the birth date as a real…
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- Excel IFS vs COUNTIF: When to Use EachIn Excel, IFS tests conditions in order and returns a corresponding result, while COUNTIF counts cells that meet…
- Excel Sort Dates by Quarter: Methods and TipsYes. In Excel for Microsoft 365 on Windows, add a helper column that calculates each date’s quarter, then…
- VLOOKUP Formula to Compare Two Sheets in Excel EasilyTo compare values on two Excel sheets by a shared ID, enter this formula on the first sheet,…
- Using Transpose in Excel: Methods, Formulas, and TipsTo transpose data in Excel, copy the cells, select the destination, and choose Home > Paste > Transpose.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.