COUNTIF Generator
Create COUNTIF formulas for counting cells that match text, numbers, comparisons, or wildcards.
COUNTIF
Count cells that match a condition.
Cells Excel checks against the condition.
Condition to count. Text criteria need quotes.
=COUNTIF(A2:A100, "Complete")Checks Before You Paste
- •Use wildcards like "A*" to count text that starts with A.
- •Use COUNTIFS when you need multiple conditions.
- •Comparison criteria should be quoted, for example ">=10".
How COUNTIF works
Availability: Excel 97 onwards, Excel for Mac, Excel for the web, Google Sheets and LibreOffice Calc. Criteria syntax is the same in all of them.
COUNTIF walks a range and returns how many cells satisfy one condition. It is the shortest route to "how many rows say Complete", "how many values are above target" and "how many of these are duplicated", and its criteria grammar is shared with SUMIF, AVERAGEIF, COUNTIFS and SUMIFS, so learning it once pays off across all of them.
The condition can be a plain value, a comparison in quotes such as ">=10" or "<>Cancelled", or text with wildcards - * for any run of characters, ? for exactly one, ~ to escape a literal * or ?. Text matching ignores case, so "complete" and "COMPLETE" count the same cells. To compare against a cell rather than a typed number the operator must be concatenated: COUNTIF(B2:B200, ">"&F1). ">F1" is the literal text F1 and counts nothing.
Two criteria are worth memorising because nothing else expresses them as briefly. COUNTIF(range, "") counts blank cells and also cells holding a zero-length string produced by a formula, which is why it can exceed COUNTBLANK's answer in a sheet full of IF(...,"",...) results. COUNTIF(range, "<>") counts everything that is not blank, and COUNTIF(range, "*") counts cells containing text - which deliberately excludes numbers, dates, TRUE/FALSE and blanks, making it a quick way to find stray text in a numeric column.
The behaviour that catches people out is type. A number and the same number stored as text are different values to COUNTIF: with 1042 in one cell and "1042" in another, COUNTIF(range, 1042) counts only the first and COUNTIF(range, "1042") counts only the second. Excel also compares long numeric strings as numbers, so two 16-digit account references that differ only after the fifteenth digit are counted as equal - a real problem when counting duplicate card or IBAN numbers. Prefix the criterion, as in COUNTIF(A:A, A2&"*"), to force a text comparison.
Syntax
=COUNTIF(range, criteria)| Argument | Required | What it does |
|---|---|---|
range | Required | The cells to test. May be several columns wide, in which case COUNTIF counts every cell in the block rather than rows. A range on another workbook is only readable while that file is open. |
criteria | Required | The condition. A number, a cell reference, or quoted text carrying a comparison operator and the wildcards * ? ~. Text is matched without regard to case. |
More worked examples
A support log with priorities in C2:C900 and you want a count of everything not yet closed.
=COUNTIF(C2:C900, "<>Closed")Returns: The number of cells whose text is anything other than Closed. Empty cells are counted too, because a blank is not Closed.
To exclude the blanks as well you need COUNTIFS(C2:C900,"<>Closed",C2:C900,"<>").
Email addresses in A2:A2000, checking whether the one on the current row appears more than once.
=COUNTIF($A$2:$A$2000, A2)>1Returns: TRUE on every row whose address occurs at least twice in the list.
This is also the test to put behind Conditional Formatting's duplicate rule when you need it to look at a fixed range rather than the selection.
Scores in B2:B120 and a pass mark typed into E1 so it can be changed without editing formulas.
=COUNTIF(B2:B120, ">="&E1)Returns: The number of scores at or above the mark in E1, updating the moment E1 changes.
If E1 is empty the criterion becomes ">=" and Excel counts every non-blank cell, which is worth guarding with an IF when the cell may be left empty.
Common mistakes
- Numbers stored as text counted separately
- COUNTIF(A2:A100, 1042) does not count a cell holding "1042" as text, and COUNTIF(A2:A100, "1042") does not count the real number. A column part-imported as text produces two counts that are each individually right and together confusing. Convert the column, then count.
- Long reference numbers compared as numbers
- Excel truncates numeric comparison at 15 significant digits, so COUNTIF treats 1234567890123456 and 1234567890123457 as the same value and reports duplicates that are not. Force text comparison with COUNTIF($A$2:$A$2000, A2&"*").
- Wildcards firing when you wanted a literal match
- An asterisk or question mark inside a criterion is always a wildcard, so counting how many cells read "Q1?" counts "Q1a" and "Q1b" as well. Escape it: "Q1~?".
- Counting a whole row instead of a block
- COUNTIF counts individual cells, so a two-column range double-counts a record that matches in both columns. It has no notion of rows - that is COUNTIFS' job, where each criteria range is compared position by position.
- A closed source workbook
- COUNTIF and SUMIF cannot read a workbook that is not open. A formula that worked yesterday returns #VALUE! the moment the source file is closed. SUMPRODUCT((range=criterion)*1) reads closed files and is the usual substitute.
Frequently Asked Questions
COUNT counts numbers only. COUNTA counts anything non-empty, including text and error values and zero-length strings. COUNTBLANK counts truly empty cells plus cells holding "". COUNTIF is the only one of the four that takes a condition, and it can imitate the others: COUNTIF(range,"<>") is close to COUNTA and COUNTIF(range,"") is close to COUNTBLANK.
Wrap the term in asterisks: =COUNTIF(A2:A200, "*urgent*") counts every cell with urgent anywhere in it. This searches the displayed text, so it will not find a word buried inside a formula, and it remains case-insensitive.
No. COUNTIF only sees values, not formatting, and there is no built-in function that counts by colour. Either add a column recording whatever the colour means and count that, or use a VBA UDF - which then makes the file a macro-enabled .xlsm.
Usually a type mismatch between number and text, or leading and trailing spaces, or a non-breaking space CHAR(160) pasted in from a web page. =COUNTIF(A2:A200,"*") tells you how many cells in a numeric column are actually text, which normally settles it.
Yes - it counts every cell in the range regardless of visibility. For a count that tracks the filter use =SUBTOTAL(103, range) for non-blank visible cells, or AGGREGATE if you also need to ignore errors.