Excel SUMPRODUCT Multiple Criteria: How To

Formulas & functions · Updated November 22, 2026

To look up or sum values with multiple criteria in Excel, use SUMPRODUCT with boolean logic: =SUMPRODUCT((range1=criteria1)*(range2=criteria2)*sum_range). For an exact lookup that returns a single value, wrap the criteria in -- (double unary) or multiply by 1, then divide by the same condition count if needed. This article explains how SUMPRODUCT handles multiple conditions, when to prefer it over SUMIFS or XLOOKUP, and common pitfalls.

What SUMPRODUCT Actually Does

SUMPRODUCT multiplies corresponding components in given arrays and returns the sum of those products. Its native syntax is =SUMPRODUCT(array1, [array2], ...). When you feed it logical tests like (A2:A100="Red"), Excel evaluates each cell to TRUE or FALSE. In arithmetic, TRUE equals 1 and FALSE equals 0. Multiplying several such tests together gives 1 only where all conditions are TRUE, and 0 elsewhere. Then multiplying by the value range (e.g., sales) yields the sum of values that meet every criterion.

Because SUMPRODUCT does not require Ctrl+Shift+Enter (unlike legacy array formulas), it became a popular workaround before dynamic arrays arrived. It still works in all Excel versions, including Excel for the web, though newer functions like FILTER and SUMIFS are often simpler.

Basic Multiple Criteria Lookup Example

Suppose you have a table with columns: Product (A2:A20), Region (B2:B20), and Sales (C2:C20). You want total sales for “Widget” in “East”.

Enter this formula:

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

Result: sums all C values where A equals “Widget” and B equals “East”. If there are three matching rows with sales 100, 200, and 300, the result is 600.

For a single lookup (return the first match, not a sum), you need to divide by the count of matches to avoid summing multiple rows. For example, to return the sales amount for the first row where Product is “Widget” and Region is “East”:

=SUMPRODUCT((A2:A20="Widget")*(B2:B20="East")*C2:C20) / SUMPRODUCT((A2:A20="Widget")*(B2:B20="East")*1)

This works only if there is exactly one match; otherwise it returns an average, not a true lookup. For a true lookup with multiple matches, use INDEX/MATCH or XLOOKUP with concatenated criteria.

Using SUMPRODUCT for Exact Match Lookup

To mimic VLOOKUP with multiple criteria, you can combine SUMPRODUCT with INDEX. For example, to return the Price (column D) where Product is “Widget” and Region is “East”:

=INDEX(D2:D20, SUMPRODUCT((A2:A20="Widget")*(B2:B20="East")*ROW(C2:C20))-1)

This is fragile because ROW returns absolute row numbers, and subtracting 1 assumes the data starts in row 2. A cleaner approach is to use MATCH with concatenation:

=INDEX(D2:D20, MATCH("Widget"&"East", A2:A20&B2:B20, 0))

That is an array formula and requires Ctrl+Shift+Enter in Excel 2019 and earlier; in Excel 365 it works normally. However, SUMPRODUCT is still useful when you need to sum or count with multiple criteria and want to avoid array entry.

SUMPRODUCT vs SUMIFS vs XLOOKUP

Excel offers several ways to handle multiple criteria. Here is a quick comparison:

  • SUMIFS – sums values that meet multiple criteria. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). It is faster and easier to read than SUMPRODUCT for summing.
  • COUNTIFS – counts rows meeting multiple criteria. Use it instead of SUMPRODUCT for counts.
  • XLOOKUP – returns a single value for the first match. For multiple criteria, concatenate lookup values: =XLOOKUP("Widget"&"East", A2:A20&B2:B20, D2:D20). Requires Excel 2021 or Microsoft 365.
  • FILTER – returns an array of all matching rows. Use =FILTER(A2:D20, (A2:A20="Widget")*(B2:B20="East")) to spill all matches.
  • SUMPRODUCT – best when you need to sum with conditions that involve arithmetic (e.g., sum of sales multiplied by a commission rate) or when you want to avoid array entry in older Excel.

For most summing tasks, SUMIFS is clearer and faster. SUMPRODUCT shines when your criteria involve calculations, like summing sales where quantity times price exceeds a threshold.

Common Pitfalls and How to Avoid Them

Several mistakes trip up users new to SUMPRODUCT:

  • Mismatched range sizes – All ranges inside SUMPRODUCT must have the same number of rows and columns. If A2:A20 and B2:B100 are used, you get a #VALUE! error.
  • Text vs numbers – If your criteria are numbers stored as text, the comparison fails. Use VALUE() or ensure consistent data types.
  • Blank cells – Blank cells in criteria ranges evaluate to FALSE, which is fine, but blank cells in the sum range are treated as 0. That is usually what you want.
  • Using commas instead of asterisks – =SUMPRODUCT((A2:A20="Widget"),(B2:B20="East"),C2:C20) multiplies the arrays but treats TRUE/FALSE as 1/0 only if you coerce them. Without coercion, TRUE*TRUE is still 1, but TRUE*FALSE is 0, so it actually works. However, if you have text in the sum range, you get #VALUE!. Using asterisks forces numeric coercion and is safer.
  • Whole column references – A:A is allowed but can slow down calculation. Restrict to the actual data range.

SUMPRODUCT with Wildcards and Partial Matches

Unlike SUMIFS, SUMPRODUCT does not support wildcards (*, ?) directly. To match partial text, use ISNUMBER(SEARCH()) or FIND(). For example, to sum sales where Product contains “Widget”:

=SUMPRODUCT(--ISNUMBER(SEARCH("Widget", A2:A20)), C2:C20)

The double unary -- converts TRUE/FALSE to 1/0. Note that SEARCH is case-insensitive; FIND is case-sensitive. This technique is useful when you need partial matches with multiple criteria.

Performance and Large Data Sets

SUMPRODUCT is not the fastest function on very large datasets (100,000+ rows) because it performs array operations in memory. SUMIFS is usually faster for simple sums. If you have Excel 365, consider using dynamic arrays with SUM or FILTER, which are optimized. For legacy versions, using SUMPRODUCT on whole columns is a common cause of sluggish workbooks. Limit ranges to the actual data extent, and avoid volatile functions inside the criteria.

Frequently Asked Questions

Can SUMPRODUCT replace VLOOKUP with multiple criteria?

It can return a single value if you divide by the count of matches, but it is not a true lookup because it averages duplicates. For a reliable lookup, use INDEX/MATCH with concatenation or XLOOKUP with concatenated keys. SUMPRODUCT is better for summing or counting with multiple conditions.

Why does my SUMPRODUCT return #VALUE!?

This usually means one of the ranges contains text where a number is expected, or the ranges are not the same size. Check that all ranges have identical dimensions, and ensure the sum range contains numeric values. If you have text in the sum range, multiply by 1 or use -- to coerce.

How do I use SUMPRODUCT with OR logic (e.g., Product is Widget or Gadget)?

Use the plus sign (+) to add conditions: =SUMPRODUCT(((A2:A20="Widget")+(A2:A20="Gadget"))*C2:C20). This counts rows where either is TRUE, but be careful not to double-count rows that meet both conditions. If a row cannot be both, it is safe. Otherwise, wrap in -- and divide by the count of condition matches.

Does SUMPRODUCT work in Google Sheets?

Yes, SUMPRODUCT exists in Google Sheets with the same syntax. However, Google Sheets has its own functions like FILTER, SUMIFS, and QUERY that often handle multiple criteria more cleanly. The examples here work identically in Google Sheets, except that array formulas are handled automatically in most cases.

Can I use SUMPRODUCT with dates and times?

Yes. Dates are stored as numbers, so you can compare them directly. For example, to sum sales after a date: =SUMPRODUCT((A2:A20>DATE(2025,1,1))*C2:C20). Ensure the date range is formatted as dates, not text.

Is there a limit to how many criteria SUMPRODUCT can handle?

No hard limit, but each additional condition multiplies the array size, increasing memory usage. Practical limits depend on your computer and Excel version. For more than 5-6 criteria, consider using a helper column or a pivot table for clarity and speed.

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.