Excel SUMPRODUCT with Multiple Criteria Explained

Formulas & functions · Updated November 22, 2026

The direct answer: use =SUMPRODUCT((range1=criteria1)*(range2=criteria2)*sum_range) to sum values that meet multiple conditions in Excel. This formula multiplies arrays of TRUE/FALSE tests (which coerce to 1/0) and then sums the products, effectively acting like a multi-condition SUMIFS.

How SUMPRODUCT Handles Multiple Criteria

SUMPRODUCT is a versatile function that multiplies corresponding components in given arrays and returns the sum of those products. When you use it for multiple criteria, you replace the simple multiplication of numbers with multiplication of logical tests. Each test like (A2:A20="North") returns an array of TRUE or FALSE. In Excel arithmetic, TRUE equals 1 and FALSE equals 0, so multiplying the tests together yields 1 only where all conditions are TRUE, and 0 elsewhere. Multiplying that result by the sum range gives you the values you want to add, and SUMPRODUCT sums them.

For example, with sales data in A2:A20 (region), B2:B20 (product), and C2:C20 (amount), to sum all amounts where region is “North” and product is “Widget”, you would write:

=SUMPRODUCT((A2:A20="North")*(B2:B20="Widget")*C2:C20)

This works because the multiplication of the two logical arrays produces an array of 1s and 0s, which then multiplies each amount in C2:C20. The result is the sum of amounts that match both criteria.

Why Not Just Use SUMIFS?

Excel has a dedicated function for summing with multiple criteria: SUMIFS. For most basic multi-condition summing, SUMIFS is simpler and faster:

=SUMIFS(C2:C20, A2:A20, "North", B2:B20, "Widget")

So why would anyone use SUMPRODUCT? SUMPRODUCT is more flexible in several ways:

  • It can handle array operations that SUMIFS cannot, such as checking criteria against calculated values or using functions like LEFT or MONTH inside the criteria.
  • It can sum based on conditions that involve other functions, like =SUMPRODUCT((MONTH(A2:A20)=1)*C2:C20) to sum January sales.
  • It can perform weighted averages or other calculations where you multiply two ranges and then conditionally sum.
  • It works with OR logic naturally (using + instead of *), whereas SUMIFS uses AND logic only.

However, SUMPRODUCT is generally slower on large datasets because it processes arrays in memory. For simple AND criteria on a few thousand rows, SUMIFS is usually the better choice.

How to Use SUMPRODUCT with OR Logic

Sometimes you need to sum when any one of several criteria is true. For example, sum sales where product is either “Widget” or “Gadget”. SUMPRODUCT handles this with addition:

=SUMPRODUCT(((B2:B20="Widget")+(B2:B20="Gadget"))*C2:C20)

Here, each test returns 1 or 0, so adding them gives 2 if both are true (which can’t happen for a single cell, but if it could, you’d need to cap it), 1 if one is true, and 0 if neither. Multiplying by the amount gives the sum of all rows where either condition holds. If you have overlapping conditions that could both be true, you might want to use --((B2:B20="Widget")+(B2:B20="Gadget")>0) to force a 1, but for mutually exclusive categories like product names, the simple addition works fine.

Common Mistakes and How to Fix Them

Several pitfalls trip up users when writing SUMPRODUCT with multiple criteria:

  • Mismatched range sizes: All ranges must have the same number of rows and columns. If you use A2:A20 and C2:C21, SUMPRODUCT returns a #VALUE! error.
  • Text vs. numbers: If your criteria are numbers stored as text, the comparison may fail. Use VALUE or ensure consistent data types.
  • Whole column references: Using A:A works but can slow down calculation because it processes over a million rows. Prefer specific ranges like A2:A1000.
  • Using commas instead of asterisks: In older Excel versions, =SUMPRODUCT((A2:A20="North"),(B2:B20="Widget"),C2:C20) works as AND logic, but in Excel 365, the comma syntax treats each argument as a separate array and multiplies them, which is equivalent. However, using asterisks is more explicit and avoids confusion.
  • Forgetting to coerce TRUE/FALSE: If you write =SUMPRODUCT((A2:A20="North")*C2:C20) without the second criteria, it works because multiplication coerces. But if you use =SUMPRODUCT((A2:A20="North"),C2:C20) it also works. The issue arises when you try to use AND or OR functions inside SUMPRODUCT—they don’t work as expected because they aggregate the entire array. Always use * for AND and + for OR.

Using SUMPRODUCT with Wildcards and Partial Matches

Unlike SUMIFS, SUMPRODUCT does not natively support wildcards (*, ?). To match partial text, you need to use functions like ISNUMBER and SEARCH or LEFT/RIGHT. For example, to sum amounts where the product name starts with “Widget”:

=SUMPRODUCT((LEFT(B2:B20,6)="Widget")*C2:C20)

Or to sum where the product contains “Widget” anywhere:

=SUMPRODUCT((ISNUMBER(SEARCH("Widget",B2:B20)))*C2:C20)

Note that SEARCH is case-insensitive; use FIND for case-sensitive matching. These functions return arrays, so they work inside SUMPRODUCT.

Performance and Alternatives for Large Data

On large datasets (tens of thousands of rows), SUMPRODUCT can become noticeably slow because it calculates each array element. Excel 365 offers dynamic array functions like FILTER and SUM that are often faster and more readable. For example, to sum with two criteria:

=SUM(FILTER(C2:C20, (A2:A20="North")*(B2:B20="Widget")))

This uses the same multiplication logic but leverages Excel’s modern calculation engine. FILTER requires Excel 365 or Excel 2021. Another alternative is to use a pivot table for quick summaries, but that changes your workflow.

If you must use SUMPRODUCT on a huge range, consider limiting the range to the actual data extent (e.g., C2:C10000 instead of C:C) and avoid volatile functions. Also, be aware that SUMPRODUCT cannot reference entire columns in older Excel versions without performance issues.

Frequently Asked Questions

Can I use SUMPRODUCT with dates as criteria? Yes, you can compare dates directly. For example, to sum sales after January 1, 2024: =SUMPRODUCT((A2:A20>DATE(2024,1,1))*C2:C20). Ensure your date cells are real dates, not text.

How do I count rows with multiple criteria using SUMPRODUCT? To count, omit the sum range: =SUMPRODUCT((A2:A20="North")*(B2:B20="Widget")). This sums the 1s and 0s, giving the count.

Why does my SUMPRODUCT return #VALUE!? This usually means the ranges have different dimensions. Check that each range has the same number of rows and columns. Also, ensure there are no errors in the referenced cells, as SUMPRODUCT treats errors as errors.

Can SUMPRODUCT handle blank cells? Yes, but blank cells in a numeric range are treated as 0. If you want to ignore blanks in criteria, use <>"" like =SUMPRODUCT((A2:A20<>"")*C2:C20).

Is there a way to use SUMPRODUCT with a drop-down list criteria? Yes, just reference the cell containing the drop-down value. For example, if E1 has the region, use =SUMPRODUCT((A2:A20=E1)*C2:C20). It updates automatically when you change the drop-down.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.