COUNTIF Excel Formula Examples and How to Use Them

In Excel, COUNTIF counts cells in a range that meet one condition. Its basic pattern is =COUNTIF(range,criteria); for example, with regions in A2:A8, =COUNTIF(A2:A8,"East") returns 3 if “East” appears three times.

What COUNTIF Does

COUNTIF is useful when you need a count rather than a sum or a list: how many orders are complete, how many scores are at least 70, or how many cells contain a particular word. It checks each cell in one range against a single criterion and returns the number of matches. The function is available in current Excel versions, including Microsoft 365.

The first argument is the range to inspect. The second is the criterion, which can be text, a number, a comparison written as text, a wildcard pattern, or a reference to a cell containing one of those criteria. Text criteria should be enclosed in straight double quotation marks. A number criterion can be entered directly or written in quotes when combined with a comparison operator.

COUNTIF is not case-sensitive: “east” and “East” are treated as the same text. It also does not add values or test multiple independent conditions. Use SUMIF to add values meeting one condition, or COUNTIFS when a count must satisfy several conditions at once.

Count Text Matches

For a basic text match, use the word or label you want to find as the criterion. Suppose A2:A8 contains East, West, East, North, East, West, and South. The following formula counts exact matches for East.

With the seven region names listed in A2:A8 as stated above, enter:

=COUNTIF(A2:A8,"East")

The result is 3, because East occurs in A2, A4, and A6. “Exact match” here means the cell’s text must match the criterion; it does not mean the function distinguishes capitalization. A cell containing “East Region” is not an exact match for “East,” though a wildcard criterion can find it.

You can make the criterion editable by placing it in a cell. This is convenient for a small report where someone should be able to change the region without editing the formula.

With the same region names in A2:A8 and the text East in D2, enter:

=COUNTIF(A2:A8,D2)

The result is 3. Because D2 is a cell reference, it is not enclosed in quotation marks. If D2 changes to West, the count updates to 2.

Count Numbers and Comparisons

For a numeric threshold, put the comparison operator and value together inside quotation marks. The supported comparison operators are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). An equality test can use the number itself or an equals sign in the criterion.

Suppose B2:B8 contains 120, 85, 140, 99, 150, 75, and 110. This example counts values of 100 or higher.

With those seven numbers in B2:B8, enter:

=COUNTIF(B2:B8,">=100")

The result is 4: 120, 140, 150, and 110 meet the condition. To make the threshold adjustable, place the number 100 in D2 and join the operator to that reference with the ampersand (&).

With the same numbers in B2:B8 and the threshold 100 in D2, enter:

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

The result is again 4. If the value in D2 changes to 120, the result becomes 3. This construction matters because COUNTIF needs the comparison operator and referenced value to form one criterion.

Use Wildcards for Partial Text

Wildcards let COUNTIF find text by pattern instead of requiring a whole-cell match. Excel uses an asterisk (*) for any number of characters, including none, and a question mark (?) for exactly one character. Put the wildcard pattern inside the quoted criterion.

"App*" finds text beginning with App.
"*pear*" finds text containing pear anywhere.
"A?e" finds a three-character value beginning with A and ending with e, with one character in between.

For a concrete example, suppose A2:A7 contains Apple, Apricot, Pineapple, pear, Apple pie, and Banana. The next formula counts entries beginning with “App.”

With those six text values in A2:A7, enter:

=COUNTIF(A2:A7,"App*")

The result is 2: Apple and Apple pie. Apricot does not match because its first three letters are “Apr,” not “App.” A pattern of "*pear*" would return 2, matching Pineapple and pear.

If the text you need to find includes a literal asterisk, question mark, or tilde, precede that character with a tilde (~) in the criterion. For example, a criterion with "~*" searches for a literal asterisk rather than treating it as a wildcard. This distinction is useful when counting codes or notes that include those symbols.

Count Dates and Blank Cells

Excel stores dates as serial numbers, so COUNTIF can compare dates using the same operators as numeric values. For reliable date criteria, construct the date with DATE rather than relying on a typed date string that could be interpreted differently under another regional date setting.

Suppose C2:C7 contains the actual dates December 31, 2025; January 1, 2026; February 14, 2026; June 30, 2026; December 31, 2026; and January 1, 2027. This formula counts dates in calendar year 2026 by including the first day of the year and excluding the first day of the next year.

With those six Excel date values in C2:C7, enter:

=COUNTIFS(C2:C7,">="&DATE(2026,1,1),C2:C7,"<"&DATE(2027,1,1))

The result is 4. Using a less-than boundary for January 1, 2027 also works if the cells contain times, whereas testing for a date equal to December 31 can miss entries later that day. COUNTIFS is used here because the year is defined by two conditions.

COUNTIF can also count blank cells or cells that are not blank. For example, assume D2:D6 contains the values Open, a truly empty cell, Closed, a truly empty cell, and Open.

With those five entries in D2:D6, enter:

=COUNTIF(D2:D6,"")

The result is 2. A formula that returns an empty text string can also behave like a blank for this criterion, so distinguish truly empty cells from formula-generated empty strings if that difference matters to your report.

With the same entries in D2:D6, enter this formula to count cells that are not blank:

=COUNTIF(D2:D6,"<>")

The result is 3. If a workbook contains formulas that display nothing, check the actual cell contents before assuming a blank count represents only cells never filled in.

Count with More Than One Condition

COUNTIF handles one condition. For two or more conditions that must all be true, use COUNTIFS, which accepts a criteria range and criterion for each condition. Each criteria range should have the same dimensions as the others so Excel compares corresponding rows.

Suppose A2:A7 contains East, West, East, East, West, East, while B2:B7 contains Complete, Complete, Pending, Complete, Pending, Pending. The following formula counts rows where the region is East and the status is Complete.

With regions in A2:A7 and the corresponding statuses in B2:B7 as listed above, enter:

=COUNTIFS(A2:A7,"East",B2:B7,"Complete")

The result is 2: the first and fourth rows meet both conditions. This is different from counting all East entries and all Complete entries separately; COUNTIFS requires both tests to match on the same row.

For an OR condition—such as East or West—one straightforward approach is to add separate COUNTIF results. With the seven region entries in A2:A8 from the text example, the following counts either label.

With region names in A2:A8, enter:

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

The result is 5: three East entries plus two West entries. This method is clear for a short list of alternatives. For a longer or changing list, consider using a helper column or a more suitable formula design rather than building a very long chain of COUNTIF functions.

Common COUNTIF Problems and Fixes

If a formula returns zero unexpectedly, check the criterion and the underlying values before changing the function. Common causes include extra spaces in imported text, numbers stored as text, a misspelled label, or a comparison operator placed outside the quoted criterion. COUNTIF ignores capitalization differences, so changing “east” to “East” will not fix a case-only mismatch.

Check the range: Make sure it includes the intended rows and does not stop before the latest data.
Check the criterion: For a comparison that uses a cell reference, join the operator and reference with &.
Check data types: A number stored as text may not behave like a numeric value in every comparison.
Check spaces: “East” and “East ” are different text values. TRIM can help clean ordinary extra spaces in a separate column.
Check wildcards: An asterisk or question mark in a criterion is a wildcard unless escaped with a tilde.

A useful diagnostic is to click a suspicious source cell and inspect the formula bar, then compare its contents with a known matching cell. Also verify that the criterion cell does not contain an unseen leading or trailing space. If your Excel regional settings use semicolons as argument separators, Excel may require semicolons when you enter formulas; the examples here use the comma separators specified for standard English-language formula syntax.

COUNTIF vs. COUNTIFS and Other Options

Choose COUNTIF when one condition is enough, such as counting one status or values above a threshold. Choose COUNTIFS when several conditions must be true for the same record, such as a region plus a status, or a date range with a lower and upper boundary. Do not use multiple COUNTIF criteria arguments: COUNTIF accepts only one criterion.

COUNTIF returns a single number, so it is a good fit for dashboard totals and summary cells. If you need to display the matching records rather than count them, filtering the table may be more useful. In Microsoft 365, FILTER can return rows that meet conditions, but it answers a different question and is not a replacement when only a count is needed.

For data that grows over time, converting the range to an Excel table can make formulas easier to maintain: select the data and choose Insert > Table. Structured references can then refer to table columns, and the table expands as rows are added. Whether you use a table or a fixed range, check that new records are included in the count.

Frequently Asked Questions

Can COUNTIF count text that appears anywhere in a cell?
Yes. Use an asterisk before and after the word in the criterion, such as "*late*", to match cells containing that text anywhere. The asterisk represents any number of characters.

Is COUNTIF case-sensitive?
No. COUNTIF does not distinguish uppercase and lowercase text. If case-sensitive counting is essential, use a different approach based on functions that compare text case explicitly.

How do I count cells that are not equal to a value?
Use the not-equal operator, written as <>, in the criterion. For instance, a criterion of "<>Closed" counts cells other than Closed, subject to how blank cells are treated in the range.

Why does COUNTIF return zero when the text looks identical?
Look for extra spaces, nonprinting characters, or a criterion that is not actually the same as the cell value. Imported data can contain characters that look like ordinary spaces but are different. Compare the source and criterion in the formula bar and clean the data if needed.

Can COUNTIF count with two conditions?
No. COUNTIF accepts one condition. Use COUNTIFS for conditions that must all be met, or add separate COUNTIF results when you need a simple OR count across alternatives.

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.