COUNTIF in Excel: Formula Examples for Budgets, Status, and Duplicates

COUNTIF counts how many cells in a range meet one condition. It is the formula behind common review questions like "how many expenses are software?", "how many tasks are overdue?", "how many rows are blank?", or "how many duplicate IDs appeared in this export?".

That makes it useful in live operating sheets like a small business budget, budget tracker, or expense tracker where counts by category or status should update automatically.

The syntax

• range — the cells to check
• criteria — what to count for. Can be text, a number, a comparison, a wildcard, or a cell reference

Common use cases

Count by exact text match:

Count numbers meeting a threshold:

Count non-blank cells:

Count cells containing specific text (wildcard):

The * wildcard matches any sequence of characters. Use ? to match exactly one character.

Count using a cell reference as criteria:

COUNTIF is case-insensitive

"apple", "Apple", and "APPLE" all count as the same. If case matters, use EXACT inside SUMPRODUCT:

Multiple conditions: COUNTIFS

COUNTIF only handles one condition. For two or more, use COUNTIFS:

COUNTIFS uses the same pattern as SUMIFS — alternate between range and criteria pairs.

Common mistakes

Criteria edge cases to check

COUNTIF criteria are text expressions even when they contain operators. Use ">1000" for a fixed threshold and ">"&H2 when the threshold is stored in another cell. For dates, compare against a real date value, such as =COUNTIF(E2:E100,">="&DATE(2026,1,1)), instead of relying on a date-looking text string.

COUNTIF is not case-sensitive and ignores differences in capitalization. It can still miss values with leading or trailing spaces, nonprinting characters, or inconsistent labels. Clean the source column or use a controlled dropdown when the field drives reporting. If the question has multiple conditions, use COUNTIFS and make sure each criteria range covers the same rows.

For duplicate review, =COUNTIF($A$2:$A2,A2) gives each occurrence a running count. Treat values above 1 as duplicates only after deciding whether blank rows, punctuation, and case variants should be considered the same record.

Make the result explainable

Put the criterion in a labeled input cell when a manager may change it, then reference that cell in the formula. Keep a nearby note that says whether the count is case-insensitive, whether wildcards are allowed, and which rows are included. This makes a summary easier to audit than a hardcoded formula copied across several dashboard cards. When a count feeds a KPI, compare it with a filtered detail view at least once during setup.

Sources

• Microsoft Support: COUNTIF function
• Microsoft Support: Ways to count values in a worksheet

The Griddy way

COUNTIF is easy for simple counts but awkward for partial matches, case sensitivity, or OR logic across multiple columns. Just ask:

"Count how many rows have 'Corp' anywhere in the company name and a deal value over $5,000"

Griddy writes the formula and applies it — no manual syntax construction.