COUNTIF Function Examples in Excel

In Excel for Microsoft 365, COUNTIF counts cells in a range that satisfy one condition. With statuses in A2:A8 and three cells containing Pending, =COUNTIF(A2:A8,"Pending") returns 3.

What COUNTIF Does

COUNTIF is useful when you need to count matching entries rather than add values. You can use it to tally orders with a particular status, find how many scores are above a threshold, or count names that contain a word. The function evaluates each cell in one range against one criterion and returns a number.

COUNTIF does not change or filter the source data; it only reports how many cells match. Its single-condition design is also important: if a count depends on two or more conditions at once, COUNTIFS is usually the better choice. COUNTIF is available in current desktop Excel, Excel for the web, and Excel for Mac. The formulas below use commas as argument separators; some regional settings display semicolons instead.

How the Arguments Work

COUNTIF has two arguments: the range to inspect and the criterion to apply. The range is the group of cells being checked. The criterion can be text, a number, an expression such as greater than a value, a wildcard pattern, or a reference to a cell containing the condition. Put text criteria and comparison expressions in straight double quotation marks.

For a text match, enter the text inside quotation marks. For a comparison such as greater than 10, put the operator and number together inside quotation marks. If a criterion refers to a value stored in another cell, join the operator to that cell reference with an ampersand. These small differences are common sources of formula errors.

Use a consistent range size and avoid including a heading unless you want Excel to consider it as data. COUNTIF counts matching cells, not distinct values: if the same matching word appears four times, the result includes all four occurrences.

Examples for Text and Status Values

Suppose A2:A8 contains Pending, Paid, Pending, Overdue, Paid, Pending, and a blank cell. To count cells whose content is exactly Pending, with those statuses in A2:A8, use:

=COUNTIF(A2:A8,"Pending")

The result is 3. COUNTIF is not case-sensitive, so Pending and PENDING both satisfy this criterion. It does not, however, treat a longer phrase containing the word as an exact match. For instance, Pending review is not an exact match for Pending.

To count a word wherever it appears within a cell, use asterisks as wildcards. If A2:A7 contains Notebook, Notebook stand, Pen, Blue notebook, Note, and Notebook, then this formula returns 4:

With product descriptions in A2:A7, use =COUNTIF(A2:A7,"*notebook*") to count cells containing notebook anywhere in the text.

The asterisk represents any number of characters, including no characters. To count entries that begin with a word, put the asterisk after it; to count entries that end with a word, put the asterisk before it. For example, the pattern "Notebook*" would match Notebook and Notebook stand, but not Blue notebook.

Examples for Numbers and Cell References

COUNTIF can compare numbers using operators such as greater than (>), less than (<), greater than or equal to (>=), and not equal to (<>). Suppose B2:B7 contains 5, 12, 8, 0, 12, and 3. To count values greater than 7, with those quantities in B2:B7, enter:

=COUNTIF(B2:B7,">7")

The result is 3: the matching values are 12, 8, and 12. To count values equal to zero, use the criterion 0 without quotation marks, or use "=0". Both forms express an equality test.

A cell reference makes a threshold easy to change without editing the formula. If the same quantities are in B2:B7 and the threshold is in D1, this formula counts values greater than the value in D1:

With the comparison threshold stored in D1 and quantities in B2:B7, use =COUNTIF(B2:B7,">"&D1). If D1 contains 7, the result is 3.

The ampersand joins the operator to the cell’s value to make a criterion Excel can evaluate. Without it, a quoted criterion such as ">D1" is treated as text, not as a reference to D1.

Examples for Dates and Wildcards

Excel stores dates as serial numbers, so COUNTIF can test date boundaries. To count dates on or after a date, a DATE function helps avoid ambiguity between regional date formats. Suppose D2:D7 contains January 5, January 18, February 2, February 20, March 1, and January 31, 2026. To count February dates, use two boundary counts and subtract them:

With the dates listed in D2:D7, this formula returns 2:

=COUNTIF(D2:D7,">="&DATE(2026,2,1))-COUNTIF(D2:D7,">="&DATE(2026,3,1))

The first count includes February 1 and all later dates; the second removes March 1 and later dates. The remaining matches are February 2 and February 20. This approach assumes the cells contain real Excel dates, not text that merely looks like a date.

The question mark wildcard stands for exactly one character, while the asterisk stands for any number of characters. If F2:F6 contains A1, A12, AB, A7, and B1, this criterion matches the two-character codes that begin with A:

With codes in F2:F6, use =COUNTIF(F2:F6,"A?"); the result is 3 because A1, AB, and A7 each have one character after A.

To search for an actual asterisk or question mark instead of using it as a wildcard, precede it with a tilde. For example, the pattern "~?" matches a literal question mark. This is helpful when counting text that contains symbols used in product codes or notes.

Counting with a Criterion Stored in a Cell

Putting criteria in worksheet cells makes a small report easier to maintain. For example, suppose G2:G8 contains Paid, Pending, Paid, Overdue, Paid, Pending, and Paid, while H1 contains the word Paid. The formula below uses the value in H1 as its criterion:

With statuses in G2:G8 and the requested status in H1, use =COUNTIF(G2:G8,H1). If H1 contains Paid, the result is 4.

This method is convenient for dashboards and reusable worksheets: a reader can change H1 to Pending and the result updates. For a comparison against a cell, combine the operator and reference. If a minimum value is in D1, the formula =COUNTIF(B2:B7,">="&D1) counts values greater than or equal to that threshold. Use a clearly labeled input cell so it is apparent what the count represents.

When to Use COUNTIFS Instead

COUNTIF handles one condition at a time. If you need to count rows where the region is North and the status is Paid, use COUNTIFS, which applies multiple criteria to corresponding ranges. Suppose A2:A7 contains North, South, North, West, North, South, and B2:B7 contains Paid, Paid, Pending, Paid, Paid, Pending. The matching North-and-Paid rows are the first and fifth rows.

With regions in A2:A7 and statuses in B2:B7, use =COUNTIFS(A2:A7,"North",B2:B7,"Paid"); the result is 2.

For an either/or count in one column, you can add separate COUNTIF results. If A2:A7 contains East, West, North, East, South, and West, counting East or West can be done as follows:

With the listed regions in A2:A7, use =COUNTIF(A2:A7,"East")+COUNTIF(A2:A7,"West"); the result is 4.

Adding counts is suitable when the alternatives cannot overlap in a single cell, as with those exact region names. For more complex combinations across columns, COUNTIFS or a different formula structure may be clearer. Do not replace COUNTIFS with separate counts if you need all conditions to be true for the same row.

Common Problems and Checks

Text does not match: Check for extra spaces, spelling differences, or a criterion that is too exact. COUNTIF ignores letter case, but spaces still matter. Wildcards can help when a partial match is intended.
A comparison is treated as text: Put an operator and a fixed number together, as in ">7". For an operator linked to a cell, join them with &.
Dates return an unexpected count: Confirm that the source cells contain numeric Excel dates rather than text. A date display format alone does not convert text into a date value.
The range misses some rows: Check the first and last cell in the range. A formula using A2:A8 will not include a new entry in A9.
The result is zero despite visible matches: Look for leading or trailing spaces and verify that the criterion refers to the intended cell. COUNTIF is case-insensitive, so capitalization alone is not the explanation.

If the data will grow, you can use a table column as the range so the reference expands when rows are added. For a simple worksheet, selecting a suitable fixed range is often enough. Avoid using an unnecessarily large range when a smaller, well-defined one would be easier to audit.

Frequently Asked Questions

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase letters as equivalent. If capitalization must distinguish matches, COUNTIF alone is not sufficient; use a case-sensitive approach such as SUMPRODUCT with EXACT, designed for the particular range and data.

Can COUNTIF check more than one condition?
Not in a single COUNTIF call. Use COUNTIFS when all of several conditions must be true for a row, or add separate COUNTIF results when you want to count alternative values in one range and the categories do not overlap.

How do I count cells that are not blank?
A common criterion for counting nonempty cells is "<>", as in =COUNTIF(A2:A20,"<>"). Check how your data is constructed if cells contain formulas returning an empty string, because formulas and visually empty cells can make blank counts less intuitive.

Can COUNTIF count a partial text match?
Yes. Put an asterisk before and/or after the text in the criterion. For example, "*book*" matches cells containing book anywhere. Use ? when exactly one character is allowed at that position.

Why does my COUNTIF formula show an error?
Check that the parentheses and quotation marks are paired, the range is valid, and the formula uses the argument separator expected by your Excel regional settings. In some locales, Excel uses semicolons instead of commas between arguments.

Choosing a Reliable COUNTIF Formula

Start by stating the question precisely: which cells should be inspected, and what should qualify as a match? Choose an exact text or numeric criterion for a precise count, a wildcard for an intentional partial match, or a cell reference when the condition should be adjustable. For date intervals, use explicit start and end boundaries and confirm that the source values are true dates.

Then compare the result with a few known records. This quick check can catch a range that excludes recent rows, a mistakenly exact text condition, or a threshold that was joined incorrectly. COUNTIF is a compact tool, but its result is only as dependable as the range and criterion you supply.

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.