Excel SUMPRODUCT with Criteria: How to Use It

Formulas & functions · Updated November 22, 2026

The direct answer: use =SUMPRODUCT((range1=criteria)*(range2)) to sum values in range2 where range1 meets the criteria. For example, =SUMPRODUCT((A2:A10="North")*(B2:B10)) sums all amounts in B2:B10 where the region in A2:A10 is “North”. This works because the comparison returns TRUE/FALSE arrays, which Excel coerces to 1s and 0s when multiplied.

What SUMPRODUCT Actually Does

SUMPRODUCT multiplies corresponding components in given arrays and returns the sum of those products. Its syntax is SUMPRODUCT(array1, [array2], ...). When you give it a single array, it simply sums the values—like a built-in SUM. But its real power comes from using arrays of TRUE/FALSE values (from comparisons) and numeric values. Excel treats TRUE as 1 and FALSE as 0 in arithmetic operations, so multiplying a condition array by a value array zeroes out the values that don’t meet the condition.

How to Use SUMPRODUCT with a Single Criterion

Suppose you have a sales table: products in A2:A20, regions in B2:B20, and sales amounts in C2:C20. To sum sales for the “East” region, enter:

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

This returns the total of C2:C20 where B2:B20 equals “East”. The multiplication (B2:B20="East")*(C2:C20) creates an array of amounts for matching rows and 0 for non-matching rows, then SUMPRODUCT sums that array.

Using Multiple Criteria with SUMPRODUCT

You can add more conditions by multiplying more arrays. For instance, to sum sales for “East” region and product “Widget”, use:

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

Each condition returns an array of 1s and 0s; multiplying them together gives 1 only where all conditions are TRUE. You can also use OR logic by adding conditions, but be careful with parentheses: =SUMPRODUCT(((B2:B20="East")+(B2:B20="West"))*(C2:C20)) sums sales for either East or West.

SUMPRODUCT vs. SUMIFS and Other Functions

SUMIFS is usually simpler for straightforward criteria: =SUMIFS(C2:C20, B2:B20, "East", A2:A20, "Widget"). However, SUMPRODUCT is more flexible. It can handle:

  • Criteria based on other calculations, like =SUMPRODUCT((C2:C20>100)*(C2:C20)) to sum values over 100.
  • Array operations that SUMIFS cannot do, such as summing the product of two columns with a condition: =SUMPRODUCT((B2:B20="East")*(C2:C20*D2:D20)).
  • Counting with multiple criteria: =SUMPRODUCT((B2:B20="East")*(A2:A20="Widget")) returns the count of rows meeting both.

SUMPRODUCT also works with wildcards? No—SUMPRODUCT does not support wildcards like * or ?. For partial matches, use SUMIFS with wildcards or use ISNUMBER(SEARCH()) inside SUMPRODUCT.

Common Errors and How to Fix Them

One frequent issue is the #VALUE! error when ranges have different sizes. Ensure all arrays have the same number of rows (and columns if using 2D). Another issue is using entire columns (e.g., A:A) which can slow down calculation; limit ranges to your data. Also, if you have text numbers in the value range, SUMPRODUCT may treat them as 0; convert them to real numbers.

Another common mistake is forgetting to coerce TRUE/FALSE to numbers. In older Excel versions, you might need to use double unary operator --: =SUMPRODUCT(--(B2:B20="East"), C2:C20). In modern Excel, multiplication works fine, but using the double unary is still a good habit.

Performance and Large Data Sets

SUMPRODUCT is an array formula that calculates each element in the range. On large datasets (tens of thousands of rows), it can slow down your workbook because it processes every cell. For better performance, consider using SUMIFS which is optimized for simple criteria, or use Excel Tables and structured references. If you must use SUMPRODUCT on a huge range, avoid entire column references and keep ranges tight.

Alternatives to SUMPRODUCT for Conditional Sums

Besides SUMIFS, you can use:

  • SUMIF for a single criterion: =SUMIF(B2:B20, "East", C2:C20).
  • FILTER function (Excel 365) to return an array and then SUM: =SUM(FILTER(C2:C20, B2:B20="East")).
  • PivotTables for interactive summaries without formulas.
  • Power Query for complex data transformations before summarization.

Each has its place. SUMPRODUCT shines when you need to multiply arrays or use conditions that involve other calculations, like weighted averages: =SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10).

Frequently Asked Questions

Can SUMPRODUCT handle OR conditions?

Yes, by adding condition arrays. For example, to sum sales for “East” or “West”, use =SUMPRODUCT(((B2:B20="East")+(B2:B20="West"))*(C2:C20)). The addition creates an array of 1s and 2s; any non-zero multiplies to the value. Be careful not to double-count if both conditions could be true for the same row (which is impossible here, but in general use OR logic carefully).

Why does my SUMPRODUCT return 0?

Check that your criteria text matches exactly (including spaces) and that the ranges are the same size. Also, if your value range contains text or blanks, SUMPRODUCT may ignore them or treat as 0. Use =SUMPRODUCT((B2:B20="East")*(C2:C20)) and press Ctrl+Shift+Enter if you are using an older Excel version (though modern Excel handles it as a dynamic array).

Can I use wildcards in SUMPRODUCT?

No, SUMPRODUCT does not support wildcards. For partial text matches, use =SUMPRODUCT((ISNUMBER(SEARCH("North", A2:A20)))*(B2:B20)). SEARCH returns a number if found, otherwise an error; ISNUMBER converts that to TRUE/FALSE.

Is SUMPRODUCT the same as an array formula?

It’s an array formula under the hood, but you don’t need to press Ctrl+Shift+Enter in modern Excel. It evaluates arrays internally. In older versions, you could use SUMPRODUCT to avoid CSE array entry, which was a common reason to use it.

How do I sum only if a cell is not blank?

Use =SUMPRODUCT((A2:A20<>"")*(B2:B20)). The condition A2:A20<>"" returns TRUE for non-blank cells. Note that cells with formulas that return “” are considered non-blank; to exclude those, use =SUMPRODUCT((A2:A20<>"")*(ISNUMBER(A2:A20))*(B2:B20)).

Can SUMPRODUCT work with dates?

Yes, dates are stored as numbers. For example, to sum sales after a certain date, use =SUMPRODUCT((A2:A20>DATE(2024,1,1))*(B2:B20)). You can also reference a cell containing a date.

SUMPRODUCT is a versatile function that goes beyond simple sums. With a clear understanding of how it multiplies arrays and coerces TRUE/FALSE, you can handle complex conditional sums, counts, and weighted calculations. For everyday single-criterion sums, SUMIFS is often simpler, but SUMPRODUCT remains a powerful tool in your Excel arsenal.

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.