COUNTIF & COUNTIFS

How COUNTIF & COUNTIFS works

COUNTIF walks down a column, checks each cell against your condition, and adds 1 to a tally for every match. The condition can be a value, a comparison wrapped in quotes, or a wildcard pattern.

COUNTIFS is the multi-condition cousin. You list pairs of (range, criteria) and Excel only counts rows where every condition is true. It's AND, never OR.

Both are read-only operations: they answer questions about your data, they never modify it. Pair them with SUMIF for total spend by category and COUNTIF for transaction count by category and you have a working dashboard.

= COUNTIF(range, criteria)

Arguments

COUNTIF basic: count active employees

You're an HR analyst. Leadership needs a single number: how many people on the roster are currently active. The Status column has values like Active, On Leave, Terminated. One COUNTIF turns the answer in.

=COUNTIF(B:B,"Active")

• •B:B -> the Status column
• •"Active" -> the value to count
• •Whole-column reference -> lets the formula keep working as new hires get added
• •Returns -> an integer: the count of matching rows

COUNTIF with a comparison: how many sales over $1,000

Same pattern as SUMIF: wrap a comparison operator in quotes and COUNTIF tallies the matching numeric cells. Useful for any 'how many of X cleared the bar' question.

=COUNTIF(B:B,">1000")

• •B:B -> the column of sale amounts
• •">1000" -> the comparison, in quotes
• •Operators -> use >, <, >=, <=, =, <> all wrapped in quotes
• •Dynamic compare -> =COUNTIF(B:B,">"&E2) compares against the value in E2

COUNTIF with wildcards: count by name pattern

The * wildcard matches any sequence of characters; ? matches exactly one. Useful for tallying entries by prefix, suffix, or partial match.

=COUNTIF(A:A,"J*")

• •A:A -> the Name column
• •"J*" -> matches any name starting with J
• •Other patterns -> "*Smith" matches anything ending in Smith. "*log*" matches any name containing log.
• •Numbers -> wildcards only work on text. Use comparison operators for numeric ranges.

COUNTIFS: count by department AND status

Real reports stack conditions. The CFO doesn't want all employees, she wants Active employees in Engineering. COUNTIFS handles that with a (range, criteria) pair for each condition - all must be true for a row to count.

=COUNTIFS(B:B,"Engineering",C:C,"Active")

• •B:B, "Engineering" -> first condition: Department equals Engineering
• •C:C, "Active" -> second condition: Status equals Active
• •Pair pattern -> every additional condition is another (range, criteria) pair
• •AND, not OR -> COUNTIFS only counts rows where every condition is true

COUNTIFS combining comparisons and literals

COUNTIFS pairs can mix types. Count orders that are both 'large enough' (over $1,000) and from a specific region. One pair uses a comparison, the other an exact match.

=COUNTIFS(A:A,"North",B:B,">1000")

• •A:A, "North" -> first pair: Region equals North
• •B:B, ">1000" -> second pair: Amount strictly greater than 1000
• •Both must hold -> COUNTIFS counts rows where every pair matches
• •Order doesn't matter -> swap the pairs and you get the same answer

Where people go wrong

1. 01
   Forgetting quotes around comparison operators

   Writing =COUNTIF(B:B,>1000) without the quotes returns #NAME? because Excel can't parse a bare comparison operator. Quotes are mandatory for criteria with operators.

   Fix: Always wrap operator-based criteria in quotes: ">1000", "<>0", "<="&E2 (with ampersand to splice cell values).
2. 02
   Wildcards on numeric data

   Using =COUNTIF(B:B,"1*") to count numbers starting with 1 returns 0. Wildcards only work on text. Excel stores numbers as numbers, not as digit strings.

   Fix: For numeric ranges use comparisons: =COUNTIF(B:B,">=10") AND =COUNTIF(B:B,"<20") combined, or COUNTIFS with both bounds. For text-formatted numbers, fix the format at the source.
3. 03
   Mismatched range sizes in COUNTIFS

   Writing =COUNTIFS(B2:B100,'X',C2:C50,'Y') errors with #VALUE! because the two criteria ranges aren't the same shape. COUNTIFS requires every criteria range to have the same dimensions.

   Fix: Use whole columns (B:B, C:C) when you can. They auto-grow with new data and there's no chance of mismatch.
4. 04
   Counting blanks vs empty strings

   =COUNTIF(B:B,"") counts true blanks AND cells holding the empty string "". The two look identical on screen but Excel treats them differently for some operations.

   Fix: Use COUNTBLANK for actual empty cells. For non-blank counts, use COUNTIF with criteria "<>"

Notes

• COUNTIF was introduced in Excel 2003. COUNTIFS arrived in Excel 2007. Both work in every modern version.
• Wildcards: * matches any sequence of characters, ? matches exactly one character. Escape with ~ to match a literal asterisk or question mark.
• Comparison operators in criteria must be wrapped in quotes: ">100", "<>0", ">="&E2 to splice a cell value.
• Both functions are case-insensitive: "active" matches "Active" and "ACTIVE".
• Empty criteria string ("") matches blank cells. Use "<>" to match every non-blank cell.
• When no rows match, both functions return 0 (not an error).
• COUNTIFS is AND logic - all conditions must hold. For OR logic, sum two COUNTIF results or use SUMPRODUCT.

What to learn next

Now prove it