Yes. In Excel, SUMPRODUCT can sum values that meet multiple criteria by multiplying TRUE/FALSE tests together; for example, with regions in A2:A6, products in C2:C6, and amounts in D2:D6, this formula sums amounts for North and Desk: =SUMPRODUCT((A2:A6="North")*(C2:C6="Desk")*D2:D6). If the matching rows contain 120 and 80, the result is 200.
What multiple criteria means
Multiple criteria are two or more tests applied to each row. In the example above, a row must have both the region North and the product Desk to contribute its amount. This is an AND condition: every test must be true. A different question, such as whether a row is in North or contains the Desk product, uses OR logic.
SUMPRODUCT is useful because it can evaluate arrays of conditions and combine their results with values in corresponding rows. It is not limited to multiplying numbers in separate columns; it can also turn logical tests into a filter for a sum or count. This makes it a flexible option for reports where criteria are more involved than a simple total.
For clarity, imagine a small table with these columns: A is Region, B is Rep, C is Product, and D is Revenue. Rows 2 through 6 contain North/Ava/Desk/120, North/Ben/Chair/90, South/Ava/Desk/150, North/Ava/Desk/80, and South/Ben/Chair/110. The formulas below refer to that layout unless another range is stated.
How the AND formula works
In the two-condition formula, each comparison creates an array of TRUE and FALSE results. Multiplication coerces TRUE to 1 and FALSE to 0. A row passes only when both tests are true, because 1 × 1 is 1, while any product containing 0 is 0. Multiplying by the revenue range leaves revenue unchanged for qualifying rows and makes other rows contribute zero.
For North and Desk, the tests pass on rows 2 and 5. SUMPRODUCT therefore adds 120 and 80, returning 200. The same pattern works with more conditions: add another parenthesized test and multiply it into the expression. For example, adding a test for rep Ava would narrow the qualifying rows further.
Parentheses matter. They make Excel evaluate each comparison as an array before applying multiplication. Keep the criteria ranges and the values range the same size and shape: five cells in each range in this example. If one range has six rows and another has five, the formula may return an error or an unintended result.
Use cells and numeric conditions as criteria
Hard-coding words such as North into a formula is fine for a one-off calculation, but a cell reference makes the report easier to reuse. Put a region criterion in F1 and a product criterion in F2. With the same A2:A6, C2:C6, and D2:D6 data ranges, enter this formula. If F1 contains North and F2 contains Desk, it returns 200:
=SUMPRODUCT((A2:A6=F1)*(C2:C6=F2)*D2:D6)
SUMPRODUCT can also apply numeric comparisons, such as greater than, less than, or not equal to. With regions in A2:A6 and revenues in D2:D6, this formula sums North revenue only where revenue exceeds 100. The matching amount is 120, so the result is 120:
=SUMPRODUCT((A2:A6="North")*(D2:D6>100)*D2:D6)
For a threshold held in F3, replace the number with a cell reference, for example D2:D6>F3. If a criterion combines an operator with a cell value—for example, a condition that should be greater than the number in F3—build the comparison directly as shown. In more conventional functions such as SUMIFS, operators are often joined to a cell reference with an ampersand; SUMPRODUCT comparisons generally put the operator in the comparison itself.
Write OR criteria without double-counting
For OR logic, add the tests instead of multiplying them. A common trap is that a row meeting both alternatives produces 2, not 1. If that value is multiplied directly by revenue, a row meeting both conditions is counted twice. Convert the combined result back to a TRUE/FALSE test by checking whether it is greater than zero.
With regions in A2:A6, products in C2:C6, and revenue in D2:D6, this formula sums a row if its region is North or its product is Desk. It includes the North Desk rows once, for a total of 440:
=SUMPRODUCT(--(((A2:A6="North")+(C2:C6="Desk"))>0),D2:D6)
The double unary operator, --, converts TRUE and FALSE to 1 and 0. Here the greater-than-zero test already produces logical values, and the double unary makes those values numeric for the multiplication with revenue. When OR alternatives are mutually exclusive, direct addition may work, but the greater-than-zero pattern is safer because it avoids accidental double-counting when a row matches more than one alternative.
Count rows instead of summing values
To count how many rows meet multiple criteria, leave the values range out. With regions in A2:A6 and products in C2:C6, this formula counts North Desk records. There are two, so the result is 2:
=SUMPRODUCT((A2:A6="North")*(C2:C6="Desk"))
This counts qualifying rows, not distinct people or unique product names. If the goal is to count unique entries that meet conditions, a different approach may be needed, especially when repeated values occur. In Microsoft 365 or Excel 2021 and later, dynamic-array functions such as FILTER and UNIQUE can help with that task; they are not required for ordinary conditional totals.
Text, dates, blanks, and matching behavior
Text comparisons in SUMPRODUCT are generally not case-sensitive: North and NORTH are treated as the same text for this kind of equality test. Extra spaces, however, can prevent a match. A cell containing North followed by a space is not necessarily equal to North. If imported data may contain leading or trailing spaces, clean the source values or use a helper column to normalize them rather than complicating every report formula.
Dates in Excel are stored as numbers, so date criteria can be tested with less-than or greater-than comparisons. Make sure the cells contain real Excel dates rather than text that merely looks like a date. For example, a condition for dates on or after a cutoff can compare the date range with a cell holding a valid date. For a date-and-time column, remember that a date may include a time fraction: a test for equality with a date serial can exclude entries later that same day.
Blank cells deserve attention too. A comparison with an empty criterion cell can match blank values, which may or may not be intended. If blank criteria should mean “do not filter by this field,” use an explicit formula design for that requirement; a simple equality test does not automatically ignore a blank criterion.
SUMPRODUCT compared with SUMIFS
For straightforward conditional sums, SUMIFS is often easier to read and can be more efficient on large ranges. It was designed to sum a range based on one or more criteria ranges. Using the sample layout, this formula sums revenue for North Desk records and returns 200:
=SUMIFS(D2:D6,A2:A6,"North",C2:C6,"Desk")
Choose SUMIFS when the task is a conventional sum with AND-style criteria. SUMPRODUCT is especially handy when conditions need array calculations, when OR logic must be combined carefully, or when the expression includes arithmetic that is awkward to express with SUMIFS. For counting, COUNTIFS is usually the simpler choice for basic AND criteria.
- Use SUMIFS for readable, standard conditional sums across large data sets.
- Use COUNTIFS for basic counts with multiple AND criteria.
- Use SUMPRODUCT when conditions involve combined array logic or a calculation that the standard conditional functions do not express as directly.
Common errors and performance tips
If the result is zero when you expect a match, check the actual cell contents first. Common causes include extra spaces, a number stored as text, a criterion that points to the wrong cell, or a mismatch between text and numeric values. Confirm that every range begins and ends on the same rows. Also check that the result is not simply zero because matching rows have zero amounts.
Avoid applying SUMPRODUCT to entire columns, such as A:A and D:D, in a large workbook when bounded ranges or Excel tables will do. SUMPRODUCT may evaluate many cells, and whole-column array calculations can slow recalculation. Use ranges that cover the data, or structured references in a table, and expand them as records are added. SUMPRODUCT formulas in Microsoft 365 for Windows can be entered with Enter; they do not normally require Ctrl+Shift+Enter. The same formula logic is available in Excel for Mac and Excel for the web, though interface details and formula separators can vary with regional settings.
Finally, keep the formula legible. Put each criterion in a clearly labeled input cell when users need to change it, and use line breaks in the formula bar when a long expression becomes hard to inspect. Test a result against a small sample whose matching rows you can verify manually. That simple check is particularly useful for OR formulas, where accidental duplicate counting is easy to miss.
Frequently Asked Questions
Can SUMPRODUCT use more than two criteria?
Yes. Multiply one parenthesized comparison for each AND condition. All the condition ranges and the summed range should have matching dimensions.
Does SUMPRODUCT need Ctrl+Shift+Enter?
No. In current Excel, including Microsoft 365 for Windows, enter the formula with Enter. The traditional array-entry shortcut is not normally needed for SUMPRODUCT.
Can SUMPRODUCT handle OR criteria?
Yes. Add the logical tests and then test whether the sum is greater than zero. That step prevents a row matching multiple alternatives from contributing more than once.
Is SUMPRODUCT better than SUMIFS?
Not for every task. SUMIFS is usually clearer and efficient for ordinary conditional sums. SUMPRODUCT is useful when the criteria logic requires array arithmetic or a more customized combination of conditions.
Why does my SUMPRODUCT formula return an error?
First check that all referenced ranges have the same number of rows and columns. Then inspect source values for errors, inconsistent data types, or references that extend beyond the intended data. A mismatch in range dimensions is a frequent cause.
Related
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
- Excel Weekday Text: Show Dates as Day Names
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- HLOOKUP in Excel With Multiple Sheets: A Practical GuideHLOOKUP does not search multiple worksheets in a single lookup operation. To search several sheets, combine separate HLOOKUP…
- Excel SUBTOTAL COUNTIF: Count Matching Visible RowsExcel has no built-in function that combines SUBTOTAL and COUNTIF: SUBTOTAL can ignore filtered-out rows, but it cannot…
- ISERROR in Excel: Check for Errors in Your FormulasIn Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such…
- Excel Timesheet: Calculate Hours Worked With FormulasTo calculate a shift in an Excel timesheet, put the start time in C2, end time in D2,…
- Excel Formula for Quarter From Date: 3 Easy MethodsIf the date is in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from 1…
- Excel Conditional Formatting Based on a FormulaYes. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.