Excel COUNTIF Examples for Text, Dates, and Numbers

Excel’s COUNTIF function counts cells in a range that match one condition. Its syntax is =COUNTIF(range,criteria); for example, if names are in A2:A10, =COUNTIF(A2:A10,"Mia") returns 3 when Mia appears three times. The examples below show how to count text, numbers, dates, blanks, and wildcard matches, and explain when a different function is more suitable.

What COUNTIF Does and How It Works

COUNTIF evaluates each cell in one range against a single criterion, then returns the number of matching cells. The range can contain values, text, dates, or formulas, while the criterion tells Excel what to match. You can type a criterion directly into the formula or refer to a cell that contains it.

In Microsoft Excel for Microsoft 365 on Windows, enter the function in a cell, type the range and criterion between the parentheses, and press Enter. Excel uses commas between arguments in the formulas shown here. Depending on regional settings, a particular Excel installation may require semicolons as argument separators instead; follow the separator Excel accepts on that device. COUNTIF works in Excel for Mac and Excel for the web as well, though ribbon placement and some interface details can differ.

A few rules make COUNTIF easier to use:

The range is the cells Excel checks; the result is a single count.
Text criteria need quotation marks when typed into a formula. A number or a cell reference usually does not.
Comparison operators such as greater than are included in a quoted criterion.
COUNTIF is not case-sensitive, so it treats uppercase and lowercase versions of the same text as a match.

Basic Examples for Text and Numbers

For an exact text match, suppose A2:A10 contains Mia, Omar, Mia, Leo, Omar, Mia, Nia, Omar, and one blank cell. The formula below counts the three cells containing Mia:

With names in A2:A10, use =COUNTIF(A2:A10,"Mia"). The result is 3.

You can also put the criterion in a worksheet cell. This is useful when you want to change the name without editing the formula. With names in A2:A10 and the search name Mia in D2, use =COUNTIF(A2:A10,D2). The result is 3. D2 is a reference, so it is not enclosed in quotation marks.

For numbers, assume B2:B8 contains 72, 88, 91, 64, 88, 100, and 79. To count scores at least 80, use =COUNTIF(B2:B8,">=80"). The result is 4: 88, 91, 88, and 100 meet the condition. A comparison criterion must be text in quotation marks, even when the value being compared is numeric.

To make the threshold adjustable, put 80 in D2. With scores in B2:B8 and the threshold in D2, use =COUNTIF(B2:B8,">="&D2). The ampersand joins the operator text to the value in D2, and the result is again 4. This approach is handy for reports where someone may change the threshold regularly.

Using Comparison Operators

COUNTIF recognizes the operators greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), equal to (=), and not equal to (<>). Place the operator and comparison value together inside quotation marks when you type the condition directly. For example, a criterion of ">=80" means “at least 80.”

Suppose C2:C8 contains invoice amounts of 25, 40, 40, 55, 70, 70, and 90. With those amounts in C2:C8, use =COUNTIF(C2:C8,"<>70") to count amounts other than 70. The result is 5: the values 25, 40, 40, 55, and 90 qualify. To count values equal to 40, use =COUNTIF(C2:C8,40); the result is 2. A numeric criterion does not need quotation marks unless it includes an operator as part of a text criterion.

For a criterion built from a cell reference, join the operator and cell value with an ampersand rather than putting the reference inside the quoted text. If the operator is part of the cell itself—for instance, D2 contains the text >=80—you can use that cell as the criterion directly. When building a formula, check that the cell contains the intended operator and value, with no accidental spaces.

Wildcards for Partial Text Matches

Wildcards let COUNTIF match text patterns instead of only complete strings. The asterisk (*) stands for any number of characters, including none. The question mark (?) stands for exactly one character. A tilde (~) makes Excel treat a wildcard character as a literal character.

Suppose A2:A7 contains AB-104, CD-205, AB-311, A-009, EF-400, and AB-018. With those codes in A2:A7, use =COUNTIF(A2:A7,"AB-*"). The result is 3 because three codes begin with AB-. The asterisk allows any characters after the hyphen.

To match a fixed number of characters, use question marks. With the same codes in A2:A7, =COUNTIF(A2:A7,"??-???") returns 5: five entries have two characters, a hyphen, and three characters. The code A-009 does not match because it has only one character before the hyphen. Wildcards apply to text matching; they are not a general way to test numeric ranges.

If a cell contains an actual asterisk and you need to count that symbol rather than use it as a wildcard, escape it with a tilde. With labels in A2:A8, =COUNTIF(A2:A8,"~*") counts cells whose entire content is a literal asterisk. For a more complicated pattern, test a few known matching and nonmatching entries so you can confirm the wildcard is placed correctly.

Counting Dates and Blank Cells

Excel stores real dates as serial numbers, so COUNTIF can compare them using the same operators used for numbers. Avoid typing an ambiguous date directly into a criterion: date formats vary by region, and text that looks like a date may not be a real Excel date.

Suppose F2:F8 contains real dates: January 4, January 17, January 31, February 1, February 12, March 5, and March 31, 2025. To count dates in January, count dates on or after January 1 and subtract dates on or after February 1. With those dates in F2:F8, use =COUNTIF(F2:F8,">="&DATE(2025,1,1))-COUNTIF(F2:F8,">="&DATE(2025,2,1)). The result is 3. The DATE function creates date values without relying on how a typed date is interpreted in a regional format.

To count cells that are blank, suppose G2:G7 contains Open, a blank cell, Closed, a blank cell, Open, and Closed. With those statuses in G2:G7, use =COUNTIF(G2:G7,""). The result is 2. To count cells with an entry instead, use =COUNTIF(G2:G7,"<>"); the result is 4. Keep in mind that a formula returning an empty string can look blank even though a formula is present in the cell. If a blank count seems unexpected, inspect the cells and consider whether they contain formulas.

Choosing Ranges and Making Criteria Flexible

Use a range that covers the data you intend to count, and make sure it does not accidentally include headers or unrelated values. A reference such as A2:A100 is easy to audit, but it will not include new records entered below row 100. A whole-column reference such as A:A can include future entries, although it may be less efficient in a workbook with many complex calculations. Excel tables are another useful option: when data is formatted as a table, structured references can make formulas easier to read and can expand as table rows are added.

For a reusable threshold or search term, store the criterion in a cell and refer to it from the formula. When copying formulas, decide whether the range should move. For example, if you fill a formula down and want the range to remain fixed, use absolute references. With scores in B2:B8 and a threshold in D2, the formula =COUNTIF($B$2:$B$8,">="&D2) keeps the score range fixed while the criterion reference can adjust if copied to another row. The dollar signs lock the referenced columns and rows.

For frequent summaries, label the criteria cells clearly—such as “Minimum score” or “Status to count”—and keep them near the results. That makes it easier for another person to understand why a count changes. It also reduces the chance that a formula will be edited incorrectly when only the desired criterion needs to change.

Common COUNTIF Problems and How to Fix Them

If a formula returns zero when matching text, check for extra spaces, spelling differences, or cells that contain numbers stored as text. COUNTIF does not distinguish uppercase from lowercase, but it does require the cell content to meet the criterion. A value that looks like 80 may be text rather than a number, particularly if it was imported from another system. Cleaning the source data or checking the cell format and underlying value can reveal the cause.

For date criteria, confirm that the range contains real Excel dates rather than date-shaped text. A quick way to check is to change a test cell’s number format to General: a real date will display as a serial number. Also confirm the boundary logic. To count dates within a month, include the first day of the month and exclude the first day of the next month; this handles dates with times more reliably than trying to match one displayed date exactly.

Another common mistake is joining a comparison operator to a cell reference incorrectly. A criterion such as “greater than the value in D2” needs the operator joined to the reference, rather than a quoted string that literally names D2. Also check that quotation marks are straight double quotes in the formula, not typographic curly quotes copied from formatted text.

Finally, remember that COUNTIF handles one condition. It cannot directly express “this product and this status” in a single COUNTIF criterion. For that, use COUNTIFS, which applies multiple conditions to corresponding ranges. If a formula is syntactically correct but counts the wrong rows, verify the input range, criterion, and data types separately before rebuilding the formula.

COUNTIF vs. COUNTIFS and Other Counting Options

Choose COUNTIF when one condition is enough: count a particular name, values above a threshold, or text that starts with a pattern. Choose COUNTIFS when every record must satisfy two or more conditions. For example, suppose product names are in A2:A7 and statuses are in B2:B7. The rows are Tea/Open, Coffee/Open, Tea/Closed, Tea/Open, Coffee/Closed, and Tea/Closed. With those products and statuses in A2:A7 and B2:B7, use =COUNTIFS(A2:A7,"Tea",B2:B7,"Open"). The result is 2, because two Tea rows have the Open status.

Use COUNTA when the goal is to count cells that contain something, regardless of whether their contents share a criterion. Use COUNTBLANK to count empty cells. Use COUNT when you only want to count numeric values. These functions answer different questions, so select the one that matches the definition of the total you need. For case-sensitive text matching or more complex logic, COUNTIF alone is not sufficient; a formula built with other functions may be needed.

Frequently Asked Questions

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. If case matters, use a method designed for case-sensitive comparison rather than relying on COUNTIF alone.

Can COUNTIF count cells that contain part of a word?
Yes. Use an asterisk before and/or after the text. For example, a criterion of "*shop*" matches text containing “shop” anywhere in the cell. The asterisk represents any sequence of characters.

Can COUNTIF use more than one condition?
Not in a single COUNTIF function. Use COUNTIFS for multiple conditions, with a criteria range and criterion for each condition. Each criteria range should correspond to the same rows of records.

Why does COUNTIF return zero for a date?
The cells may contain text that only looks like dates, or the criterion may be interpreted differently because of regional date formats. Confirm that the source values are real Excel dates and use DATE to create unambiguous comparison boundaries.

Does COUNTIF count blank cells?
Yes. A blank-string criterion can count empty cells, while a not-equal-to-blank criterion counts cells treated as nonblank. Cells containing formulas that return an empty string may behave differently from genuinely empty cells for some counting tasks, so inspect the data when results surprise you.

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.