How to Use COUNTIF and COUNTIFS in Excel

COUNTIF counts the cells in a range that meet one condition, and COUNTIFS extends that to several conditions at once. In financial models they tally transactions by status, count accounts that breach a threshold, or measure how many periods met a target. They turn raw transaction tabs into summary counts without a pivot table and refresh the moment the underlying data changes.

Syntax and how they work

COUNTIF is =COUNTIF(range, criteria). It scans range and counts the cells matching criteria, which can be a value, a cell reference, or a comparison in quotes such as ">=1000".

COUNTIFS is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). Each criteria range pairs with its own criterion, and the conditions combine with AND logic, so a row must satisfy every pair to be counted. Every criteria range must span the same rows, or COUNTIFS returns a #VALUE! error.

Worked example: count open invoices over a threshold

A ledger has status labels in B2:B200 and amounts in D2:D200. You want the number of invoices that are both open and at or above 1000. That is two conditions, so COUNTIFS is the right tool.

1. Enter =COUNTIFS(B2:B200, "Open", D2:D200, ">=1000").
2. COUNTIFS checks B2:B200 for Open and D2:D200 for values at or above 1000.
3. Only rows where both conditions hold are counted, combining with AND logic.
4. To reference a threshold cell instead of a literal, concatenate the operator: ">="&H1.

Two rows are both Open and at or above 1000, so the result is 2.

Criteria syntax that earns its keep

Criteria are more expressive than a plain value. Comparison operators count ranges of numbers, wildcards match partial text, and references let the criterion live in a cell so the summary updates as inputs change.

Getting the quoting right is the whole game: operators and wildcards go inside quotes, while a cell reference is concatenated with &.

• Comparisons: ">100", "<=0", and not-equal "<>0" to count nonzero entries.
• Wildcards: "*Ltd" matches any text ending in Ltd, and "A1??" matches A1 plus two more characters.
• Reference a cell: ">="&H1 builds the criterion from a threshold cell.
• Count blanks with "" and non-blanks with "<>".

Common pitfalls in models

Mismatched range sizes are the top COUNTIFS error. Every criteria range must span exactly the same rows, so B2:B200 paired with D2:D199 throws #VALUE!. Lock ranges with absolute references like $B$2:$B$200 before copying so they do not drift.

The second trap is OR logic. COUNTIFS combines conditions with AND only, so to count rows that are Open OR Overdue you add two COUNTIF results, taking care not to double count any row that matches both. Text mismatches such as a trailing space in "Open " versus "Open" also undercount silently, since the strings are not equal.