Fixing Excel COUNTIFS That Returns Wrong Count When Criteria Use OR Logic

You built a COUNTIFS formula to count rows where either one condition or another is true, but the number coming back doesn't match what you see when you filter manually. The formula looks correct, the ranges are right, yet the count is off — usually too low.

The problem isn't a bug. It's a fundamental misunderstanding of how COUNTIFS is designed to work. Once you see why it fails for OR logic, the fix becomes obvious.

What You'll Learn

• Why COUNTIFS cannot natively handle OR logic and what it does instead
• How to combine two COUNTIFS calls to get a correct OR count
• How to avoid double-counting rows that satisfy both conditions
• How to use SUMPRODUCT for more flexible OR criteria
• Which pattern to reach for depending on your situation

Prerequisites

These examples assume you're working in Excel 2016 or later. The SUMPRODUCT approach works all the way back to Excel 2007. If you're on Microsoft 365, you'll also see an alternative using dynamic array functions at the end.

The Core Problem: COUNTIFS Is AND-Only by Default

COUNTIFS evaluates every criteria pair with AND logic. This means a row is counted only when all conditions are satisfied simultaneously. There is no native OR switch in the function signature.

Here's the scenario that trips most people up. Say column A holds a category (for example, Region) and column B holds Sales Amount. You want to count every row where the region is either East or West.

Your first instinct might be something like:

=COUNTIFS(A2:A100,"East","West")

or

=COUNTIFS(A2:A100,"East" OR "West")

Neither formula works because COUNTIFS doesn't understand OR operators. Every criteria argument is tied to a range, and all criteria pairs must evaluate to TRUE for the same row. In other words, COUNTIFS asks:

"Does this row satisfy condition 1 AND condition 2 AND condition 3?"

It never asks:

"Does this row satisfy condition 1 OR condition 2?"

That's why trying to force OR logic into a single COUNTIFS almost always produces incorrect results.

The Simplest Solution: Add Multiple COUNTIFS Together

The easiest way to implement OR logic is to calculate each condition separately and add the results.

Example:

=COUNTIFS(A2:A100,"East")+COUNTIFS(A2:A100,"West")

Suppose your data looks like:

The formula returns:

East = 2
West = 2

Total = 4

This works because no row can simultaneously contain both "East" and "West" in the same cell.

Whenever your OR conditions are mutually exclusive, simply adding separate COUNTIFS formulas is perfectly safe.

The Double-Counting Trap

Problems begin when your OR conditions involve different columns.

Example:

You want every row where:

• Region = East
• OR
• Product = Laptop

Your first attempt:

=COUNTIFS(A2:A100,"East")+COUNTIFS(B2:B100,"Laptop")

Imagine the data:

Expected answer:

3 rows

Formula returns:

East = 2

Laptop = 2

Total = 4

Why?

The first row satisfies both conditions.

It gets counted twice.

Removing Double Counts

Use the inclusion-exclusion principle.

Formula:

=COUNTIFS(A2:A100,"East")
+COUNTIFS(B2:B100,"Laptop")
-COUNTIFS(A2:A100,"East",B2:B100,"Laptop")

Breaking it down:

First count:

East = 2

Second count:

Laptop = 2

Overlap:

East AND Laptop = 1

Final:

2 + 2 - 1 = 3

This pattern works for nearly every two-condition OR scenario.

Multiple OR Conditions

Suppose you need:

East

OR

West

OR

North

Simply extend the formula:

=COUNTIFS(A2:A100,"East")
+COUNTIFS(A2:A100,"West")
+COUNTIFS(A2:A100,"North")

Since each row contains only one region, there is no overlap.

OR Logic Inside Another AND Condition

A very common business example:

Count orders where:

• Region = East OR West
• AND Status = Completed

Don't attempt:

=COUNTIFS(A:A,"East","West",B:B,"Completed")

Instead:

=COUNTIFS(A:A,"East",B:B,"Completed")
+COUNTIFS(A:A,"West",B:B,"Completed")

Each COUNTIFS evaluates one branch of the OR condition while preserving the AND requirement.

Using SUMPRODUCT for Flexible OR Logic

As conditions become more complex, SUMPRODUCT becomes easier to manage.

Example:

=SUMPRODUCT(
((A2:A100="East")+(A2:A100="West"))*
(B2:B100="Completed")
)

Here's what happens.

First array:

East?

TRUE FALSE TRUE ...

Second array:

West?

FALSE TRUE FALSE ...

Adding them gives:

1

1

1

Then Excel multiplies by:

Completed?

1

0

1

Only rows satisfying both requirements remain.

SUMPRODUCT naturally supports OR logic because adding Boolean arrays performs an OR operation.

Counting Numeric OR Conditions

Suppose you need:

Sales > 1000

OR

Discount > 20%

Formula:

=SUMPRODUCT(
((B2:B100>1000)+(C2:C100>20%))>0
)

The expression:

>0

converts:

0 → FALSE

1 → TRUE

2 → TRUE

ensuring rows matching either condition are counted only once.

OR Logic with Wildcards

Example:

Count customers whose company name starts with:

• Micro
• Tech

Formula:

=COUNTIFS(A:A,"Micro*")
+COUNTIFS(A:A,"Tech*")

Again, only safe if names cannot satisfy both criteria.

If overlap is possible, subtract the intersection.

Dynamic OR Lists (Excel 365)

Microsoft 365 introduces a much cleaner solution.

Suppose:

F2:F4

contains:

East

West

North

Formula:

=SUM(COUNTIF(A2:A100,F2:F4))

COUNTIF returns:

{15,9,21}

SUM combines them.

This makes maintaining long OR lists much easier because users simply edit the cells instead of rewriting formulas.

Using FILTER for Verification

When a count looks suspicious, verify visually.

Example:

=FILTER(
A2:C100,
(A2:A100="East")+
(A2:A100="West")
)

Seeing the matching rows often reveals:

• Typos
• Unexpected spaces
• Incorrect assumptions

before you spend time debugging formulas.

Hidden Spaces Cause Wrong Counts

Suppose cells contain:

East

and

East␠

They look identical.

They are not.

Check with:

=LEN(A2)

or clean data:

=TRIM(A2)

before building complex OR formulas.

Numbers Stored as Text

Another silent problem.

This won't match:

=COUNTIFS(A:A,100)

if the cells contain:

"100"

stored as text.

Check:

=ISTEXT(A2)

or convert using:

=VALUE(A2)

Data type mismatches are a common reason manual filters disagree with formula results.

When COUNTIF Is Better Than COUNTIFS

If you're only checking one column, use COUNTIF.

Instead of:

=COUNTIFS(A:A,"East")

write:

=COUNTIF(A:A,"East")

It's shorter, easier to read, and performs the same task.

Reserve COUNTIFS for genuine multi-condition calculations.

Common COUNTIFS OR Mistakes

Trying to Put OR Inside COUNTIFS

Excel has no OR operator inside COUNTIFS.

Forgetting Double Counts

Rows matching both conditions inflate totals.

Mixing Text and Numbers

Formatting differences silently break comparisons.

Ignoring Hidden Spaces

Trailing spaces prevent matches.

Using COUNTIFS for Complex Logic

SUMPRODUCT or dynamic arrays are often cleaner.

Which Formula Should You Use?

Choosing the right approach keeps formulas shorter and eliminates hidden counting errors.

Troubleshooting Checklist

If your OR count doesn't match a manual filter:

• Verify every range has identical dimensions.
• Confirm criteria values have no extra spaces.
• Check for numbers stored as text.
• Look for rows satisfying multiple OR conditions.
• Test each COUNTIF or COUNTIFS individually before combining them.
• Use FILTER (Excel 365) to inspect the matching rows.
• Compare the result against a manual filtered count to confirm correctness.

Most COUNTIFS "bugs" turn out to be one of these issues.

Final Thoughts

COUNTIFS is one of Excel's most useful functions, but it was designed around AND logic, not OR logic. Trying to force OR conditions into a single COUNTIFS formula inevitably produces confusing results because the function simply wasn't built for that purpose.

Fortunately, the fix is straightforward once you understand the underlying behavior. For mutually exclusive conditions, adding separate COUNTIF or COUNTIFS formulas is usually all you need. When overlap is possible, subtract the intersection to avoid double-counting. And for more sophisticated logic involving multiple columns or dynamic criteria, SUMPRODUCT or modern Excel dynamic array functions provide a cleaner, more scalable solution.

The key takeaway is to choose the formula that matches your logic rather than trying to make COUNTIFS do something it was never designed to do.