Excel COUNTIF Range Examples: Text, Numbers, and Dates

Use Excel’s COUNTIF function to count cells in a range that match one condition: for example, =COUNTIF(A2:A20,"East") counts cells containing East. Replace the range and condition to count numbers, dates, blanks, or text patterns; the examples below show how each kind of criterion works.

How COUNTIF works with a range

COUNTIF has two arguments: the range of cells to check, and the criterion that each cell must meet to be counted. The range can be a row, a column, or a rectangular block, but the function applies one condition to the cells in that range. It returns a number, including zero if nothing matches.

For example, with product names in A2:A20, put the formula in another cell, such as B1. It counts matching cells without changing the source data. A criterion can be text, a number, a comparison such as greater than 100, a date, or a wildcard pattern. Text criteria are not case-sensitive: East and EAST match the same entries.

In the examples, the formula is placed outside the range it counts. If you put a formula inside the range it examines, the formula cell itself may affect the result, so a separate results area is usually clearer.

Count an exact text value

To count exact matches for a word or label, use the text in quotation marks as the criterion. With department names in A2:A20, this formula counts the cells whose value is East:

=COUNTIF(A2:A20,"East")

If East appears in A2, A5, A9, and A12, the result is 4. The match is for the whole cell value, rather than a cell that merely contains those letters. A cell containing East Coast does not match the exact criterion East.

You can also refer to a cell containing the criterion. With department names in A2:A20 and the desired department typed in D2, use:

=COUNTIF(A2:A20,D2)

This is useful when you want to change the value being counted without editing the formula. Do not put quotation marks around the cell reference: D2 tells Excel to read the value in that cell, while "D2" would look for the literal text D2.

Count numbers and apply comparison criteria

For an exact numeric match, use a number as the criterion. With quantities in B2:B50, this counts cells equal to 10:

=COUNTIF(B2:B50,10)

To count values above, below, or equal to a threshold, put the comparison operator and threshold together in a quoted criterion. With scores in B2:B50, this counts scores greater than 80:

=COUNTIF(B2:B50,">80")

To make the threshold adjustable, join the operator to a cell reference. With scores in B2:B50 and the cutoff in D2, use:

=COUNTIF(B2:B50,">"&D2)

If D2 contains 80, the formula counts values above 80, not values equal to 80. Use ">=" when the cutoff itself should count. Other useful operators are <, <=, and <> for “not equal to.” Keep the operator inside the quoted text when writing a fixed criterion.

Count text that contains a word or pattern

COUNTIF supports two wildcard characters for text criteria. An asterisk (*) matches any number of characters, including none; a question mark (?) matches exactly one character. With descriptions in A2:A100, this counts cells containing the word urgent anywhere in the text:

=COUNTIF(A2:A100,"*urgent*")

With product codes in A2:A100, this counts codes that start with AB:

=COUNTIF(A2:A100,"AB*")

With codes in the same range, this counts four-character entries that start with AB, because each question mark represents one character:

=COUNTIF(A2:A100,"AB??")

To search for a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde (~). For example, with entries in A2:A100, this counts cells containing an actual asterisk:

=COUNTIF(A2:A100,"*~**")

The first and last asterisks in that criterion allow any surrounding text; ~* represents the literal asterisk being sought.

Count dates in a range

Excel stores valid dates as serial numbers, so COUNTIF can compare dates using the same operators as other numbers. It is usually safer to build a date criterion with the DATE function than to type a date string, because typed date text can be interpreted differently under different regional settings.

With dates in B2:B31, this counts entries on or after January 1, 2026:

=COUNTIF(B2:B31,">="&DATE(2026,1,1))

For a date interval, use COUNTIFS, which applies multiple conditions to corresponding ranges. With dates in B2:B31, this counts dates from January 1 through January 31, 2026, including both endpoints:

=COUNTIFS(B2:B31,">="&DATE(2026,1,1),B2:B31,"<"&DATE(2026,2,1))

Using the first day of the following month as an exclusive upper limit also includes dates that have times attached, such as January 31 at 3:00 p.m. A criterion that stops at midnight on January 31 could exclude those time-stamped entries.

Count blank, nonblank, and not-equal cells

To count genuinely empty cells in a range, use an empty text criterion. With status values in C2:C50, this counts blank cells:

=COUNTIF(C2:C50,"")

To count cells that are not blank, use the not-equal operator with an empty criterion. With status values in C2:C50, this counts entries that contain a value:

=COUNTIF(C2:C50,"<>")

There is a useful distinction when cells contain formulas. A cell can look empty because its formula returns an empty string, even though it is not truly empty. If the count seems surprising, check whether the range contains formulas returning "" or cells with spaces. A space is a character, not a blank. You may need to clean the source data or use a different test depending on whether you mean visually empty or actually empty.

You can also count values that are not a particular label. With status values in C2:C50, this counts cells that do not equal Closed:

=COUNTIF(C2:C50,"<>Closed")

That criterion can include blank cells, because an empty cell is also not equal to Closed. If blanks should be excluded, add a second condition with COUNTIFS. With statuses in C2:C50, the formula below counts nonblank values other than Closed:

=COUNTIFS(C2:C50,"<>Closed",C2:C50,"<>")

Choose and manage the range reference

The range you choose determines what gets counted. A reference such as A2:A20 checks only those cells; it does not automatically expand when you add data below row 20. If the list will grow, consider converting it into an Excel table: select a cell in the data, choose Insert > Table, and confirm the table range. Table references can make formulas easier to read and generally expand as rows are added.

For a fixed range that you will copy to other cells, dollar signs lock its address. With values in A2:A20 and a criterion in D2, this formula keeps the range fixed when copied:

=COUNTIF($A$2:$A$20,D2)

If instead each formula should check a different row or column, leave the relevant reference relative. For a quick one-off count, a full-column range such as A:A is possible, but a bounded range or table is often easier to audit and avoids asking Excel to examine unused cells.

COUNTIF accepts a single range and a single criterion. A rectangular range such as A2:C20 is valid, but Excel checks all its cells against that same criterion; it does not pair a criterion in one column with a different criterion in another. Use COUNTIFS when a record must meet multiple conditions, such as a department in one column and a status in another.

Common COUNTIF problems and how to fix them

Wrong result for a comparison: For a fixed threshold, include the operator inside quotation marks, as in ">80". For a threshold stored in a cell, join the quoted operator to the reference with &.
Text appears to match but is not counted: Check for leading or trailing spaces, inconsistent spelling, or text stored differently from the expected value. COUNTIF ignores letter case, but it does not ignore extra characters.
Number criteria do not match: A value that looks numeric may be stored as text. Check the source cells and convert text numbers to numbers if appropriate. Formatting alone does not always change the underlying value.
Dates are missed: Confirm that the cells contain real Excel dates rather than date-looking text. Use DATE to construct comparison criteria and check whether the dates include times.
Copied formula checks the wrong cells: Lock a range with dollar signs when it must stay fixed, or use a table reference for a growing dataset.
Formula is rejected after pasting: Excel normally uses commas in formulas under the settings assumed here, but list separators can vary with regional settings. If Excel requires semicolons, use the separator configured for that installation.

For formulas that still seem incorrect, temporarily inspect a few cells in the counted range, including ones expected to match and ones expected not to match. This often reveals a stray space, a text-versus-number mismatch, or an unexpected blank more quickly than changing the criterion at random.

COUNTIF or COUNTIFS?

Use COUNTIF when one condition is enough, such as “count East” or “count values greater than 80.” Use COUNTIFS when the result depends on multiple conditions. For example, suppose regions are in A2:A100 and sales figures are in B2:B100. To count rows where the region is East and sales exceed 500, use:

=COUNTIFS(A2:A100,"East",B2:B100,">500")

COUNTIFS evaluates the conditions together, so a row must meet both requirements to be counted. Each criteria range should have the same dimensions as the other criteria ranges. If you want an “East or West” total, you can add separate COUNTIF results; with regions in A2:A100, use:

=COUNTIF(A2:A100,"East")+COUNTIF(A2:A100,"West")

This is an OR calculation: a cell matching either label contributes to the total. The approach is easy to read for a short list of alternatives, while many conditions may call for a different summary method, such as a PivotTable.

Frequently Asked Questions

Can COUNTIF count cells in more than one range?
One COUNTIF call takes one range. For separate ranges, add separate COUNTIF formulas, or use COUNTIFS when several conditions must be met across corresponding ranges.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive, so East and EAST match the same text criterion. A case-sensitive count requires a different approach, such as a formula using EXACT.

Can I use a cell reference as the criterion?
Yes. Put the cell reference in the criteria argument without quotation marks. For comparison criteria, join the operator to the reference with an ampersand.

Why does COUNTIF return zero when the value looks right?
Common causes include extra spaces, a number stored as text, a date stored as text, or a range that omits the matching cell. Check the source values and the exact range before changing the formula.

Can COUNTIF count cells that contain part of a word?
Yes. Use asterisks around the text, such as "*urgent*", to match cells containing that text anywhere. Use question marks when you need to represent a specific number of single characters.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.