Arguments
range- The range of cells to count.criteria- The condition, e.g. ">100", "Yes", or "*text*".
COUNTIF
Counts the cells in a range that meet a single condition.
Syntax
=COUNTIF(range, criteria)=COUNTIF(A2:A100, "Complete")Returns: Number of cells equal to "Complete"
Counts exact text matches.
=COUNTIF(A2:A100, "*error*")Returns: Cells containing "error" anywhere
Wildcards (*) match partial text.
How COUNTIF evaluates
COUNTIF tests every cell in a range against one criterion and returns how many passed. It counts cells, never rows and never distinct values, so a column where the same customer appears eleven times returns 11, not 1. That literalness is what makes it useful for duplicate detection: comparing COUNTIF's answer to 1 is the standard way to flag repeats.
The criterion is a string that Excel parses, which is where the function's whole character comes from. A leading operator (>, <, >=, <=, <>, =) is stripped off and the rest becomes the operand; with no operator the criterion is an equality test. Text comparison ignores case, * and ? act as wildcards, and ~ escapes a literal wildcard. Comparing against a cell means building the string yourself with ">"&A1.
Its type rules are looser than a lookup's, which trips people going the other way. COUNTIF happily matches the number 100 against the text "100", where VLOOKUP would return #N/A on the same pair. It also truncates text comparisons at 255 characters, so two long strings that differ only past that point are counted as equal.
- Case-insensitive equality
- COUNTIF(A:A, "yes") and COUNTIF(A:A, "YES") return the same number. For a case-sensitive count use =SUMPRODUCT(--EXACT(range, "YES")), which compares character by character.
- Numbers and numeric text both match
- A criterion of 100 matches cells holding the number 100 and cells holding the text "100". This is unlike VLOOKUP and MATCH, so a COUNTIF that finds a value does not prove a lookup on the same key will.
- Wildcards are always active on text
- You cannot switch them off. Counting product codes that genuinely contain an asterisk requires escaping: "~*" for a literal asterisk, "~?" for a literal question mark, "~~" for a tilde.
- Blank cells and empty text differ
- COUNTIF(A:A, "") counts cells holding an empty string, including formula results of "". COUNTBLANK counts both those and truly empty cells. COUNTIF(A:A, "<>") counts every non-empty cell.
- Text is compared only to 255 characters
- Criteria longer than 255 characters return #VALUE!, and two cells that agree for the first 255 characters are treated as equal. Compare long strings with EXACT instead.
- Errors in the range propagate
- A #N/A anywhere in the range makes COUNTIF return that error rather than skipping the cell. To count error cells deliberately, =SUMPRODUCT(--ISNA(range)) works where a criterion of "#N/A" is unreliable across locales.
Errors COUNTIF returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | The range is in a closed workbook - COUNTIF cannot read one - or the criterion string is longer than 255 characters. Open the source file or shorten the comparison. |
| #NAME? | The criterion was written unquoted, as in =COUNTIF(A:A, >100). Excel reads the bare comparison as an undefined name. Quote the entire criterion including the operator. |
| #N/A or another error showing through | Coming from a cell inside the counted range, not from COUNTIF. Find it with =SUMPRODUCT(--ISERROR(range)) and clear it at source. |
| #REF! | The range argument points at deleted rows or columns. Rebuild the reference. |
| 0 with no error | Nothing matched. Check for trailing spaces in the range, and remember that a criterion cell containing a formula result of "" is not the same as an empty cell. Widening the test to a wildcard - "*"&A1&"*" - quickly shows whether the value is present in some other form. |
More examples
=COUNTIF($A$2:$A$5000, A2)>1Returns: TRUE on every row whose value repeats
The duplicate flag. Anchoring the range and leaving the criterion relative means each row asks how many times its own value appears in the whole column.
=COUNTIF($B$2:$B$200, "<>")-COUNTIF($B$2:$B$200, "")Returns: Cells with real content, excluding empty strings
Formula results of "" look empty but are text. Subtracting the count of empty strings from the count of non-blanks separates typed data from formula output.
=COUNTIF($C$2:$C$1000, ">"&AVERAGE($C$2:$C$1000))Returns: How many values are above the mean
Concatenating the operator to a computed value builds the criterion at calculation time, so the threshold follows the data instead of being typed in.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Fully supported, including wildcards and the 255-character comparison limit. |
| Excel 2019 | Identical; no behavioural change in this release. |
| Excel 2021 / Microsoft 365 | Unchanged, but an array of criteria now spills one count per criterion instead of needing Ctrl+Shift+Enter. |
| Google Sheets | Same syntax. Sheets also accepts a regular expression as the criterion when regex matching is enabled for the file. |
| LibreOffice Calc | Supported; whether * and ? are wildcards, regular expressions or literal text is set under Tools, Options, Calc, Calculate. |
Does COUNTIF Work in Google Sheets?
Yes. COUNTIF uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements COUNTIF with exactly two arguments; passing a third returns #VALUE! rather than being ignored.
The criterion parser is the one shared with SUMIF and AVERAGEIF: leading <, <=, >, >=, <> and = operators are recognised, an operand that reads as a number is compared numerically so the number 100 and the text "100" match each other, TRUE and FALSE become booleans, and text comparison is case-insensitive.
Wildcards * and ? are supported with ~ as the escape character, a criterion supplied as an empty cell matches blank cells, and a blank cell never satisfies an inequality such as ">0". The editor applies no 255-character truncation to text comparisons, so two long strings are compared in full.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
Build a COUNTIF formula
Fill in your ranges and copy a ready-to-paste formula - no signup.
COUNTIF questions
Because their type rules differ. COUNTIF compares loosely: the criterion 100 matches both the number 100 and the text "100". VLOOKUP and MATCH compare strictly, and a number never equals text that looks like it. So a COUNTIF proving a key is present says nothing about whether a lookup on that key will find it. Test with =ISNUMBER(A2) on both sides and make the column consistent.
Wrap the target in asterisks: =COUNTIF(A2:A100, "*urgent*"). The asterisk matches any run of characters, so the word can appear anywhere in the cell, and the match is case-insensitive. To build the pattern from a cell, concatenate: "*"&E1&"*". A question mark matches exactly one character, which is how you count codes of a known shape such as "??-2026".
Not on its own - it counts every matching cell, so duplicates inflate the answer. The classic workaround is =SUMPRODUCT(1/COUNTIF(range, range)), which gives each value a weight of one divided by how often it appears, so each distinct value contributes exactly 1. It fails if the range contains blanks. In Excel 2021 and 365, =COUNTA(UNIQUE(range)) is clearer and faster.
COUNTIF has to inspect every cell in the range before it can return a count - it cannot stop early the way MATCH does. Pointing it at A:A means over a million cells per formula, and a column of such formulas multiplies that. Restrict the range to the rows that actually hold data, or convert the data to a Table so the reference grows with it rather than covering the whole sheet.