Your COUNTIFS formula isn’t returning the correct count, it shows 0, wrong numbers, or no result at all, even when matching data exists.
This usually happens due to range mismatches, incorrect criteria syntax, or hidden data issues.
This guide fixes it step by step.
Why the Issue Happens
- Criteria ranges are different sizes
- Text vs number mismatch (
100vs"100") - Extra spaces or hidden characters
- Incorrect criteria syntax (missing quotes, wrong operators)
- Date values not recognized properly
- Case or formatting inconsistencies
- Blank cells affecting logic
- Using full-column references inefficiently
Step-by-Step Fixes
Step 1: Use Correct COUNTIFS Structure
Basic syntax:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2])
Example:
=COUNTIFS(A2:A100, "Sales", B2:B100, ">100")
Step 2: Match All Range Sizes
All ranges must be identical in size.
Wrong:
=COUNTIFS(A2:A100, "Sales", B2:B50, ">100")
Correct:
=COUNTIFS(A2:A100, "Sales", B2:B100, ">100")
Step 3: Fix Criteria Syntax
Text must be in quotes:
=COUNTIFS(A2:A100, "Sales")
Numbers with conditions:
=COUNTIFS(B2:B100, ">100")
Wrong:
=COUNTIFS(B2:B100, >100)
Step 4: Fix Text vs Number Mismatch
If data is text but criteria is numeric, it won’t match.
Fix:
=COUNTIFS(A2:A100, VALUE(E1))
Or convert data to numbers using Format → Number.
Step 5: Remove Extra Spaces
Hidden spaces break matching.
Fix:
=COUNTIFS(ARRAYFORMULA(TRIM(A2:A100)), "Sales")
Better approach:
- Clean data first using TRIM in a helper column
Step 6: Fix Date Criteria
Dates must be handled properly.
Wrong:
=COUNTIFS(B2:B100, ">01/01/2024")
Correct:
=COUNTIFS(B2:B100, ">"&DATE(2024,1,1))
Step 7: Handle Blank Cells
To exclude blanks:
=COUNTIFS(A2:A100, "<>")
To count blanks:
=COUNTIFS(A2:A100, "")
Step 8: Debug Using COUNTIF
Test individual conditions:
=COUNTIF(A2:A100, "Sales")
If this returns 0, the issue is with data, not the formula.
Step 9: Avoid Full Column References
Using full columns slows performance and may cause issues.
Avoid:
=COUNTIFS(A:A, "Sales")
Use:
=COUNTIFS(A2:A1000, "Sales")
Step 10: Clean Hidden Characters
Imported data often contains invisible characters.
Fix:
=COUNTIFS(ARRAYFORMULA(TRIM(CLEAN(A2:A100))), "Sales")
Common Mistakes
- Using mismatched range sizes
- Missing quotes around text criteria
- Ignoring text vs number differences
- Not cleaning spaces or hidden characters
- Using incorrect date formats
- Expecting matches when data doesn’t actually match
- Mixing full-column and limited ranges
Pro Tips / Better Alternatives
Use FILTER + COUNTA
More flexible and easier to debug:
=COUNTA(FILTER(A2:A100, A2:A100="Sales", B2:B100>100))
Use QUERY for Complex Conditions
=QUERY(A1:C100, "SELECT COUNT(A) WHERE A='Sales' AND B>100", 1)
Use Helper Columns for Clean Data
=TRIM(CLEAN(A2))
Then apply COUNTIFS on cleaned data.
Use Wildcards for Partial Matching
=COUNTIFS(A2:A100, "*Sales*")
Validate Data Before Counting
Ensure:
- No hidden spaces
- Correct formats
- Consistent data types
Bottom Line
If COUNTIFS isn’t working, fix in this order:
- Match all range sizes
- Use correct criteria syntax
- Fix data types (text vs number)
- Remove extra spaces and hidden characters
- Handle dates properly
- Debug using COUNTIF
Most issues come from data inconsistencies and incorrect criteria setup.
Fix those, and COUNTIFS will work accurately and consistently.
Google Sheets Fixes: