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 (
Frequently Asked Questions
Why does COUNTIFS return a lower count than expected when I try to use OR conditions?
COUNTIFS applies AND logic to every criteria pair, so it only counts rows where all conditions are true at the same time. If you want rows where any one condition is true, you need to add separate COUNTIFS results together and subtract any overlap.
How do I count rows in Excel where a cell matches one value OR another value in the same column?
Add two COUNTIFS formulas together: =COUNTIFS(A:A,"Value1")+COUNTIFS(A:A,"Value2"). Because the same cell can't equal both values simultaneously, there's no overlap to subtract in this case.
What is the risk of double-counting when combining COUNTIFS with OR logic?
Double-counting happens when a single row can satisfy both conditions at once β for example, when your OR criteria span two different columns. Subtracting a third COUNTIFS that checks both conditions together removes the duplicated rows from your total.
Can SUMPRODUCT replace COUNTIFS for OR logic in Excel?
Yes. SUMPRODUCT with boolean arrays handles OR logic cleanly using the + operator between conditions: =SUMPRODUCT(((A2:A100="X")+(B2:B100="Y"))>0). The >0 check collapses any row that matches one or both conditions into a single count of 1.
Does the COUNTIFS OR fix work the same way in Excel 365 as in older versions?
The addition and SUMPRODUCT patterns work identically across Excel 2007 and later, including Microsoft 365. In Excel 365 you can also use BYROW or dynamic array formulas for more advanced multi-condition logic, but the classic patterns are always reliable.
π€ Share this article
Sign in to saveRelated Articles
How-To Guides
Fixing PostgreSQL Upsert That Overwrites Unchanged Rows and Breaks updated_at
8m read
How-To Guides
Fixing Excel XLOOKUP That Returns Wrong Value When Search Array Has Merged Cells
9m read
How-To Guides
Fixing Excel INDEX MATCH #VALUE! Error When Array Formula Spans Multiple Columns
9m read
Comments (0)
No comments yet. Be the first!