Excel COUNTIF: Count Cells That Match a Condition
By Szabó Gergő · Updated
COUNTIF counts cells in one range that match one condition. Use it to count statuses, values above a threshold, duplicates, blanks, or text patterns.
Syntax and arguments
=COUNTIF(range, criteria)- range
- The cells Excel evaluates.
- criteria
- The value, comparison, cell reference, or pattern to count.
COUNTIF examples
Task statuses are in C2:C150.
=COUNTIF(C2:C150,"Complete")The result is the number of cells equal to Complete, ignoring case.
Scores are in B2:B40 and the target is in E2.
=COUNTIF(B2:B40,">="&E2)The operator is joined to the target cell with an ampersand.
IDs are in A2:A500 and the formula is entered on row 2.
=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","")Any ID appearing more than once receives a duplicate label.
Common mistakes
Operator and reference are not joined
Use quotes and &, as in ">"&E2, rather than ">E2".
Counting cells with several conditions
Use COUNTIFS for multiple criteria ranges.
Literal wildcard characters are counted as patterns
Prefix a literal asterisk or question mark with ~, such as "~*".
COUNTIF FAQ
How do I count nonblank cells?
Use COUNTIF(range,"<>") or COUNTA when every non-empty value should count.
How do I count blank cells?
Use COUNTIF(range,"") or the dedicated COUNTBLANK function.
Is COUNTIF case-sensitive?
No. For case-sensitive counting, use SUMPRODUCT with EXACT.