The COUNTIF function counts cells in a range that meet one condition, such as matching a name or exceeding a number.
These examples show how to build the formula, combine criteria with cell references, and avoid common edge cases in Excel and Google Sheets.
COUNTIF syntax and what each part means
The syntax is =COUNTIF(range, criterion).
The range is the cells to inspect, and the criterion is the condition a cell must meet to be counted.
For example, =COUNTIF(A2:A20,"Complete") counts cells in A2:A20 whose value is Complete.
Text criteria go in quotation marks.
A criterion can also refer to a cell, or combine a comparison operator with a value.
Basic COUNTIF formula examples
Count cells that match text
Suppose B2:B8 contains these statuses: Open, Closed, Open, Pending, Closed, Open, and Pending.
To count the cells that say Open, enter:
=COUNTIF(B2:B8,"Open")
The expected result is 3.
Text matching is not case-sensitive, so this also counts entries such as OPEN or open.
Extra spaces, however, can prevent a match: Open is different from Open.
Count cells that match a number
If C2:C10 contains sales quantities, this formula counts entries equal to 25:
=COUNTIF(C2:C10,25)
Numbers used as criteria generally do not need quotation marks.
Use a quoted criterion when joining a comparison operator to a number, as in the next example.
Count values greater than, less than, or equal to a number
To count numbers greater than 100 in C2:C10, use:
=COUNTIF(C2:C10,">100")
The criterion is text containing the operator and value.
Other useful patterns include "<50" for less than 50, ">=100" for at least 100, and "<>0" for values other than zero.
Use a cell reference as the COUNTIF criterion
If E2 contains a target status, count its matches in B2:B100 with:
=COUNTIF(B2:B100,E2)
For a comparison against a number stored in E2, join the operator to the cell reference with an ampersand.
To count values greater than the amount in E2, use =COUNTIF(C2:C100,">"&E2).
For values greater than or equal to E2, use =COUNTIF(C2:C100,">="&E2).
This approach makes the result update when you change the value in E2, without editing the formula.
Count blank and nonblank cells
To count blank cells in A2:A20, use =COUNTIF(A2:A20," ") without the space inside the quotation marks: the criterion is an empty string, written as "".
The complete formula is =COUNTIF(A2:A20,"").
To count cells that are not blank, use =COUNTIF(A2:A20,"<>").
A formula that returns an empty string may be treated differently from a truly empty cell in some counting scenarios.
If the count matters, check whether the cells are genuinely blank or contain formulas that return "".
COUNTIF wildcard examples
COUNTIF supports wildcards in text criteria.
The asterisk (*) matches any number of characters, while the question mark (?) matches one character.
=COUNTIF(A2:A50,"North*")counts entries beginning with North, such as North and Northeast.=COUNTIF(A2:A50,"*son")counts entries ending in son, such as Jackson.=COUNTIF(A2:A50,"*book*")counts entries containing book anywhere in the text.=COUNTIF(A2:A50,"A?C")counts three-character entries that start with A and end with C, such as ABC.
To match a literal asterisk or question mark instead of using it as a wildcard, precede it with a tilde.
For example, =COUNTIF(A2:A50,"~*") counts cells containing a literal asterisk as the criterion.
Count dates within a range
Spreadsheet dates are stored as serial values, so comparison criteria can count dates before or after a chosen day.
If D2:D100 contains valid dates and E2 contains a cutoff date, count dates after it with:
=COUNTIF(D2:D100,">"&E2)
To count entries on or before the cutoff, use =COUNTIF(D2:D100,"<="&E2).
Make sure the cells contain real spreadsheet dates rather than text that merely looks like a date.
Regional settings can affect how typed date strings are interpreted, so a reference to a date cell is often safer than typing a date into the criterion.
Count values that are not equal to a criterion
The not-equal operator is <>.
To count cells in B2:B100 that do not contain Cancelled, use =COUNTIF(B2:B100,"<>Cancelled").
This criterion can also be built from a cell reference: =COUNTIF(B2:B100,"<>"&E2).
Be aware that a not-equal criterion may include blank cells.
If you want to count nonblank cells that also differ from Cancelled, use =COUNTIFS(B2:B100,"<>",B2:B100,"<>Cancelled").
COUNTIFS applies multiple conditions to corresponding ranges.
COUNTIF limits and compatibility
COUNTIF handles one condition.
For two or more conditions, use COUNTIFS in modern Excel and Google Sheets.
For example, to count rows where the department is Sales and the status is Open, use =COUNTIFS(A2:A100,"Sales",B2:B100,"Open").
COUNTIF is available in current Excel versions, including Excel for Microsoft 365, Excel for the web, and perpetual desktop editions that support the function, as well as Google Sheets.
Both platforms use the same basic syntax, though spreadsheet locale settings may require semicolons instead of commas between arguments.
Keep the range as a single row or column for predictable results.
A criterion that refers to an entire column, such as A:A, is convenient, but a bounded range can be easier to review and may improve performance in large workbooks.
Common COUNTIF problems and fixes
- The result is zero unexpectedly: Check for leading or trailing spaces, spelling differences, and text-formatted numbers.
A value stored as text may not behave like a numeric value. - The formula shows an error: Confirm that the range and criterion are separated by the correct argument delimiter for your locale.
Some settings require semicolons. - A comparison formula does not update: When using a cell reference with an operator, join them with
&, as in">"&E2. - The count includes unexpected entries: Text criteria are not case-sensitive, and wildcards can match more values than an exact criterion.
- Counts differ between sheets or files: Check whether numbers or dates are stored as text and whether blank-looking cells contain spaces or formulas.
When a formula gives an unexpected result, inspect a few cells in the range directly and compare their stored values with the criterion.
That often reveals whether the issue is spacing, data type, or a broader condition that calls for COUNTIFS.