COUNTIFS with Multiple Criteria & OR Logic

COUNTIFS in Excel and Google Sheets counts rows that meet multiple conditions at once, and to apply OR logic within a single criterion you use an array constant like {“A”,”B”} or add multiple COUNTIFS together. This article explains exactly how to combine AND and OR logic in COUNTIFS, with real examples, common pitfalls, and alternatives.

Formula

The core formula for OR logic in COUNTIFS is:

=COUNTIFS(range1, crit1, range2, {"val1","val2"})

This counts rows where range1 matches crit1 AND range2 matches either val1 OR val2. The array constant {"val1","val2"} makes Excel evaluate the function twice and sum the results. For more than two OR values, extend the array: {"A","B","C"}. In Google Sheets, the same syntax works, but you may need to wrap it with SUMPRODUCT if you get unexpected results.

Variables:

• range1, range2 – the cell ranges to check (must be same size).
• crit1 – the AND criterion (e.g., “>10” or “Yes”).
• {"val1","val2"} – the OR criterion values, in curly braces.

Worked Example

Suppose column A has region (North, South, East, West) and column B has sales amount. You want to count rows where region is North OR South AND sales > 100.

1. Set up data: A1:A10 = North, South, East, West, North, South, East, West, North, South; B1:B10 = 150, 80, 200, 90, 120, 110, 300, 50, 140, 95.
2. Formula: =COUNTIFS(A1:A10,"North",B1:B10,">100") + COUNTIFS(A1:A10,"South",B1:B10,">100")
3. Count North >100: North rows are 1,5,9 – values 150,120,140 → 3.
4. Count South >100: South rows are 2,6,10 – values 80,110,95 → 1.
5. Total = 4.

Using the array version: =COUNTIFS(A1:A10,{"North","South"},B1:B10,">100") returns the same 4. The array constant splits the count into two separate calculations and sums them.

How COUNTIFS Handles AND vs OR

COUNTIFS natively applies AND logic between each range/criteria pair. Every condition must be true for a row to be counted. OR logic is not built-in for a single criterion; you must either use an array constant or add multiple COUNTIFS. The array constant trick works because Excel expands the formula into multiple evaluations and sums the results. In Google Sheets, the same approach works, but if you have other criteria that are arrays, you may need ARRAYFORMULA or SUMPRODUCT to force the correct behavior.

When to Use Each Approach

• Array constant – best for a single OR condition with 2-5 values. It is concise and easy to read.
• Multiple COUNTIFS added – best when you have several OR groups or when the criteria are complex (e.g., wildcards, cell references). It is more verbose but clearer.
• SUMPRODUCT – best for advanced users who need OR logic across multiple columns or when working with arrays in Google Sheets.

Example: Count rows where (Region = North OR South) AND (Product = Widget OR Gadget). Use: =COUNTIFS(A:A,{"North","South"},B:B,{"Widget","Gadget"}) – this returns a 2×2 array and sums to the correct total. In Google Sheets, wrap with SUMPRODUCT to get a single number.

Common Mistakes and Pitfalls

• Mismatched range sizes – all ranges in COUNTIFS must be the same size, or you get #VALUE!.
• Array constant with text – use double quotes inside braces: {"A","B"} not {A,B}.
• Forgetting to press Ctrl+Shift+Enter – in older Excel versions, array formulas require this. In Excel 365 and Google Sheets, it is not needed.
• OR with wildcards – wildcards like * and ? work in COUNTIFS but not inside array constants. Use multiple COUNTIFS instead.
• Counting blanks or errors – use "" for blanks and "#N/A" for errors, but note that array constants may not handle errors well.

Comparison: COUNTIFS vs SUMPRODUCT vs FILTER

COUNTIFS is the simplest and fastest for counting with AND logic. SUMPRODUCT is more flexible for OR logic and array operations but can be slower on large datasets. FILTER (Excel 365 and Google Sheets) can count by wrapping with COUNTA, but it is designed to return rows, not counts. For example, =COUNTA(FILTER(A:A,(A:A="North")+(A:A="South"))) gives the same result as COUNTIFS with OR, but FILTER is more readable when you need to see the matching rows.

Alternatives for Complex OR Logic

• Add multiple COUNTIFS – the most explicit and reliable method.
• Use a helper column – create a column that flags rows meeting the OR condition, then COUNTIF on that column.
• Use SUMPRODUCT – =SUMPRODUCT(((A1:A10="North")+(A1:A10="South"))*(B1:B10>100)) – this handles OR with arithmetic.
• Use FILTER – for dynamic arrays in modern Excel/Sheets.

Frequently Asked Questions

Can COUNTIFS handle OR logic with cell references?

Yes, but you need to use multiple COUNTIFS. For example, =COUNTIFS(A:A,D1,B:B,">100")+COUNTIFS(A:A,D2,B:B,">100") where D1 and D2 contain the OR values. Array constants cannot reference cells directly; they only accept literal values.

Why does my COUNTIFS with array constant return 0?

This usually happens when the array constant is not entered as an array formula in older Excel versions, or when the ranges are not the same size. In Google Sheets, you may need to wrap the formula with ARRAYFORMULA or use SUMPRODUCT.

How do I count with OR logic across two different columns?

Use SUMPRODUCT or multiple COUNTIFS. For example, count rows where column A = “X” OR column B = “Y”: =COUNTIFS(A:A,"X")+COUNTIFS(B:B,"Y")-COUNTIFS(A:A,"X",B:B,"Y") to avoid double-counting rows where both are true.

Does COUNTIFS work with dates and OR logic?

Yes. Use date values in array constants like {DATE(2023,1,1),DATE(2023,2,1)} or use multiple COUNTIFS with comparison operators. Be careful with date serial numbers and formatting.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF only handles one condition. COUNTIFS handles multiple conditions with AND logic. To get OR logic, you must combine multiple COUNTIFS or use array constants.

Related

• Why You Need More Than One Pair of Headphones
• Hide iTunes purchases: What it does and how to do it
• Is Disney Plus Worth It? Cost & Value

in data | Data range | - | A1:A10 | 0
in crit | Criteria (comma separated) | - | A, B | 0
out count | Count of matches | - | sum(if(isnumber(match(data, crit, 0)), 1, 0)) | 0