Fix the Wrong count error

What Wrong count means

When COUNTIF returns a count but the number is higher or lower than expected, the formula found matches — just the wrong ones. Over-counting is usually caused by wildcard patterns matching too broadly or duplicates in the range. Under-counting is usually caused by data-type mismatches or hidden characters preventing recognition of a cell.

Common causes

• The criteria uses * which matches any number of characters — 'Jan' matches 'January', 'Janitor', and 'Japan', leading to over-counting.
• The range includes hidden or filtered rows — COUNTIF counts all rows in the range regardless of visibility; it does not respect filters.
• Duplicate rows in the data are intentional in the source but unexpected in the count context.
• Dates formatted as text ('01/01/2024') are not equal to real date serial numbers, causing under-counting when criteria is a real date.
• The range accidentally includes the header row which contains a label matching the criteria.

Example fix

The broken formula started at A1 (including the header 'Jan Sales') and used a wildcard that matched January, Janitor, etc. The fixed version excludes the header and uses an exact criteria.

How to fix it

1. 1Be more specific with wildcards: instead of "Jan*" use the full word "January" if you only want January, or verify exactly what the wildcard matches in a helper column.
2. 2To count only visible rows after filtering, use SUBTOTAL: =SUBTOTAL(102,A2:A100). SUBTOTAL(102,...) counts non-blank visible cells only.
3. 3Exclude the header row by starting the range at row 2: =COUNTIF(A2:A100,"criteria") instead of =COUNTIF(A:A,"criteria").
4. 4Standardise dates: ensure the criteria_range column is formatted as Date (not Text) and use a DATE() formula or a cell reference containing a real date as criteria.
5. 5Remove duplicates from source data if they are unintentional (Data > Remove Duplicates in Excel; Data > Remove Duplicates in Sheets), then re-run the count.

Frequently asked