COUNTIF Calculation Examples: Text, Dates, and Numbers

COUNTIF counts cells in a range that meet one condition, such as a number, text value, date, or blank. With the numbers 12, 18, 18, 25, a blank, 0, and 31 in A2:A8, =COUNTIF(A2:A8,18) returns 2 because 18 appears twice.

How COUNTIF works

COUNTIF has two inputs: the range to examine and the criterion to test. It returns one count for the cells that match; it does not add their values or test different conditions in separate columns. In the opening example, Excel checks each cell in A2:A8 and counts the cells equal to 18.

A criterion can be a number, text in quotation marks, a comparison operator, a wildcard pattern, or a reference to a cell containing the criterion. When you use a comparison operator with a cell reference, join the operator and reference with an ampersand. The examples below use Microsoft Excel for Microsoft 365 on Windows; the formulas work the same way in current Excel for Mac and Excel for the web.

COUNTIF is not case-sensitive: for example, its text criterion treats East and EAST as matching. It evaluates one condition across one range. When the task calls for multiple conditions, COUNTIFS is usually the better fit.

Count numbers and apply thresholds

For an exact number, use the number itself as the criterion. With 12, 18, 18, 25, a blank, 0, and 31 in A2:A8, =COUNTIF(A2:A8,18) returns 2. The zero is a value, so it is not included in that result.

Comparison operators let you count values above, below, or equal to a threshold. With the same values in A2:A8, =COUNTIF(A2:A8,">=18") returns 4: the matching values are 18, 18, 25, and 31. The operator is included in the text criterion, so the quotation marks are required around the complete comparison.

If the threshold is stored in a cell, concatenate the operator with that reference. With the same numbers in A2:A8 and the threshold 20 in D2, =COUNTIF(A2:A8,">="&D2) returns 2, for 25 and 31. This is useful when a report needs to count values against a limit that can be changed without editing the formula.

> means greater than; >= means greater than or equal to.
< means less than; <= means less than or equal to.
<> means not equal to.

For a count of values other than 18, with the same contents in A2:A8, =COUNTIF(A2:A8,"<>18") returns 5. That result includes the blank cell, because a blank is also not equal to 18. If blanks should not count, use a second condition with COUNTIFS or subtract the blank count; make the choice explicit so the total reflects the intended definition.

Count text and use wildcards

For an exact text match, enclose the criterion in straight double quotation marks. Suppose B2:B8 contains East, West, East, Northeast, a blank, East, and Southeast. Then =COUNTIF(B2:B8,"East") returns 3. Excel ignores capitalization for this function, but otherwise the full text must match.

Wildcards help when only part of a text value is known. In the same B2:B8 list, =COUNTIF(B2:B8,"East*") returns 3 because an asterisk matches any number of characters after East, including none. By contrast, =COUNTIF(B2:B8,"*east*") returns 5: it finds East, Northeast, and Southeast, regardless of capitalization.

* matches any number of characters, including zero.
? matches exactly one character.
~ escapes a wildcard when you need to search for a literal asterisk or question mark.

For a question-mark example, suppose E2:E5 contains A104, A204, B104, and A14. The formula =COUNTIF(E2:E5,"A??4") returns 2: A104 and A204 start with A, end with 4, and have exactly two characters between them. To count cells containing a literal asterisk, with the data to search in E2:E5, use =COUNTIF(E2:E5,"*~**"); it returns the number of cells containing an asterisk anywhere in that range. The first and last asterisks are wildcards; the tilde makes the middle asterisk literal.

Use a cell reference as the criterion

A criterion in a worksheet cell makes a formula easier to update and reuse. With the numbers 12, 18, 18, 25, a blank, 0, and 31 in A2:A8 and the value 18 in D2, =COUNTIF(A2:A8,D2) returns 2. Changing D2 to 25 changes the count to 1 without changing the formula.

For a threshold kept in a cell, the operator must be joined to the reference. With the same values in A2:A8 and 20 in D2, =COUNTIF(A2:A8,">="&D2) returns 2. Writing the reference without joining it to the operator would not create the intended greater-than-or-equal criterion.

References also work with text. If B2:B8 contains the region names from the previous example and D2 contains East, =COUNTIF(B2:B8,D2) returns 3. This approach is particularly useful in a summary table where users select or type a criterion in a designated input cell.

Count blanks and nonblank cells

COUNTIF can distinguish empty cells from cells containing entries. With the seven values in A2:A8 from the opening example, including one genuinely empty cell, =COUNTIF(A2:A8," ") returns 0 because a space character is not the same as an empty cell. To count genuinely empty cells in that range, use =COUNTIF(A2:A8,""); it returns 1. To count nonblank cells, use =COUNTIF(A2:A8,"<>"); it returns 6.

There is an important distinction between a cell that is empty and a formula cell that displays an empty string. COUNTIF with an empty-string criterion can also match formulas that return an empty string, even though those cells contain formulas. If the difference matters, inspect the source cells or use a more targeted approach instead of assuming that a displayed blank is truly empty.

Count dates and values within a date range

Excel stores dates as serial numbers, so COUNTIF can compare dates using operators. For example, suppose C2:C6 contains January 5, January 20, February 3, February 28, and a blank in 2025. With those dates in C2:C6, =COUNTIF(C2:C6,">="&DATE(2025,1,1)) returns 4 because all four dates are on or after January 1.

To count dates in a particular month, use two conditions with COUNTIFS: one for the first day and another for the first day of the following month. With the same five entries in C2:C6, =COUNTIFS(C2:C6,">="&DATE(2025,1,1),C2:C6,"<"&DATE(2025,2,1)) returns 2, counting January 5 and January 20. Using a less-than boundary for February 1 includes every time on January 31 if the cells contain date-and-time values; an inclusive end date at midnight can miss later times that day.

DATE constructs an actual Excel date from year, month, and day numbers, which is safer than typing a date string whose interpretation may depend on regional settings. If dates look like dates but are stored as text, comparisons may not behave as expected. Convert the source values to real Excel dates before relying on a date count.

When to use COUNTIFS instead

COUNTIF checks one criterion. COUNTIFS applies multiple criteria, usually by checking that all conditions are met for corresponding cells. For instance, a table may need to count orders from one region that also exceed a sales threshold. COUNTIFS can check the region column and sales column together, while separate COUNTIF formulas cannot express that same row-by-row combination as directly.

COUNTIFS is also useful for ranges such as dates between two boundaries, as shown above. Keep the criteria ranges the same size and shape so Excel can compare corresponding rows. If the goal is to count cells matching any one of several alternatives, such as East or West, one simple option is to add two COUNTIF results. Be mindful of overlap if the criteria can match the same cell; otherwise the sum may count a cell twice.

Common COUNTIF problems and checks

If a result is unexpectedly zero or too large, check the data and criterion before rewriting the formula. These are common sources of mismatches:

Extra spaces: A value such as East followed by a space may not match the intended exact text. TRIM can help clean ordinary spaces, but it does not remove every nonprinting character.
Numbers stored as text: A cell that looks numeric may be text. Check the source data type and convert inconsistent entries when numeric comparisons are required.
Wrong range: Confirm that the formula covers all intended rows and that the criterion range has not shifted when copied.
Missing quotation marks: Text criteria and comparison strings need quotation marks. When a criterion comes from a cell, join the operator to the reference.
Wildcard behavior: An asterisk or question mark in a criterion has special meaning unless escaped with a tilde.
Dates stored as text: Convert text-formatted dates to actual date values before comparing them.

COUNTIF is case-insensitive, so it cannot distinguish East from EAST as an exact case-sensitive match. Also, a formula result may not update as expected if Excel calculation is set to manual; check Formulas > Calculation Options if workbook results appear stale. When copying a formula, consider whether the range should remain fixed. For example, an absolute reference such as $A$2:$A$8 stays anchored when the formula is filled to another cell.

Frequently Asked Questions

What is the difference between COUNT and COUNTIF? COUNT counts cells containing numbers. COUNTIF counts cells that meet a specified condition, which can be a number, text, comparison, wildcard pattern, or blank test.

Can COUNTIF count cells that contain a word anywhere? Yes. Use an asterisk on both sides of the word. With text in B2:B8, =COUNTIF(B2:B8,"*east*") counts entries containing east anywhere in the text, without distinguishing uppercase from lowercase.

How do I count values that are not equal to a number? Use the not-equal operator in the criterion. With numbers in A2:A8, =COUNTIF(A2:A8,"<>18") counts values other than 18, and may include blank cells; add a nonblank condition with COUNTIFS if blanks must be excluded.

Can COUNTIF check two conditions? No. COUNTIF accepts one criterion. Use COUNTIFS when a result must satisfy more than one condition, such as a region match and a date range at the same time.

Why does COUNTIF return zero when the values look identical? Check for leading or trailing spaces, text-versus-number differences, text-formatted dates, and wildcard characters in the criterion. Also verify that the selected range contains the cells you expect.

Choosing the right counting formula

Use COUNTIF for a single, clear condition; use COUNTIFS when several conditions must be true together. For text searches, decide whether the whole cell must match or whether a wildcard should find a partial match. For date periods, use real dates and a start-inclusive, next-period-exclusive boundary. These choices make the formula easier to audit and reduce surprises when the source data changes.

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.