Excel COUNTIF for Text: Examples and Common Fixes

Formulas & functions · Updated November 20, 2026

To count cells that match text in Microsoft Excel, use =COUNTIF(A2:A20,"Apple"), replacing the range and word with your own. COUNTIF can also find text that starts with, ends with, or contains a word by using wildcard characters in its criterion.

How COUNTIF works with text

COUNTIF checks a range and returns the number of cells that meet one condition. Its syntax is COUNTIF(range, criteria): the range is the cells Excel checks, and the criteria tells Excel what to count. Text criteria go inside straight double quotation marks when typed directly into a formula.

For the examples below, assume A2:A8 contains North, South, North, East, a genuinely empty cell, Northeast, and south, in that order. These examples distinguish exact matches from partial matches, which is important when a list contains similar labels.

With North in A2:A8, this formula returns 2 because North appears in two cells:

=COUNTIF(A2:A8,"North")

COUNTIF text matching is not case-sensitive. That means the lower-case south in A8 also matches a criterion of South. COUNTIF is convenient for ordinary labels, names, categories, and status values, but it is not the right function by itself when capitalization must distinguish one value from another.

Count an exact text value

An exact text criterion counts cells whose contents match the specified value, rather than every cell that happens to include it. For example, the criterion North matches North, but not Northeast or North Region. Leading or trailing spaces can prevent an otherwise similar-looking value from matching.

With labels in A2:A8, this formula counts the exact value South and returns 2, because matching ignores capitalization:

=COUNTIF(A2:A8,"South")

You can also place the criterion in a cell and refer to it. This is useful when people need to change the value being counted without editing a formula. If C1 contains North and the labels are in A2:A8, the formula returns 2:

=COUNTIF(A2:A8,C1)

When referring to a criterion cell, do not put the reference in quotation marks. Quotation marks would make Excel treat the reference name as literal text instead of reading the cell’s contents.

Use wildcards for partial text matches

COUNTIF supports two wildcard characters in text criteria. An asterisk (*) represents any number of characters, including none; a question mark (?) represents exactly one character. This lets you count patterns without knowing every complete cell value.

  • Begins with: A criterion such as North* matches North and Northeast.
  • Ends with: A criterion such as *th matches North and South.
  • Contains: A criterion such as *east* matches East and Northeast.
  • One unknown character: A criterion such as B?g matches Bag or Big, but not Buggy.

With the sample labels in A2:A8, this formula returns 3: North appears twice, and Northeast appears once.

=COUNTIF(A2:A8,"North*")

To count cells containing the text east anywhere in the value, the formula returns 2, for East and Northeast. The matching is case-insensitive.

=COUNTIF(A2:A8,"*east*")

If the text you need to find contains a literal asterisk or question mark, precede that wildcard with a tilde (~) in the criterion. For example, a criterion of ~* searches for a literal asterisk rather than treating it as a wildcard. A tilde itself is escaped by doubling it. This matters when counting product codes or text that includes these symbols.

Build criteria from cell values or operators

A criterion can combine an operator with a value from another cell. Join the operator in quotation marks to the cell reference with an ampersand. For example, to count values that begin with the text in C1, use the pattern C1&”*”. With labels in A2:A8 and North in C1, this formula returns 3:

=COUNTIF(A2:A8,C1&"*")

Comparison operators such as greater than (>) and less than (<) are more often used with numbers or dates, but COUNTIF criteria can include operators. For text, the not-equal operator is sometimes useful, although it can include blank cells depending on the criterion and data. If the requirement is specifically to count a certain text value, an explicit text match is usually easier to understand and verify.

When criteria combine an operator and a cell reference, write the operator as text and concatenate it. With scores in B2:B20 and a threshold in D1, the formula =COUNTIF(B2:B20,">"&D1) counts scores greater than the threshold. It is not a text-counting formula, but the same criteria construction is useful when a worksheet mixes text and numeric conditions.

Count text, blanks, and formula results carefully

A frequent related question is how to count all cells containing text, rather than one particular word. The wildcard criterion * is commonly used for that purpose. With values of any kind in A2:A20, this formula counts text entries that match the wildcard:

=COUNTIF(A2:A20,"*")

This is different from counting nonempty cells. COUNTA includes numbers and other nonblank values as well as text, while COUNT counts numeric values. A formula that displays an empty string, such as a formula returning “”, can also behave differently from a genuinely empty cell in counts. If your result seems off, check whether the source cells contain formulas, spaces, or nonprinting characters rather than assuming they are truly blank.

To count genuinely empty cells, COUNTBLANK is often clearer. With a range A2:A20, this formula counts cells Excel considers blank, including cells whose formulas return an empty string:

=COUNTBLANK(A2:A20)

To count cells that are not blank, use a criterion of <> or use COUNTA, depending on whether you mean nonempty values or nonempty cells. These counts are not always interchangeable when formulas return empty strings. Check a few cells directly if a worksheet uses formulas to hide results.

Make the match case-sensitive

COUNTIF treats uppercase and lowercase letters as equivalent. If a code system treats Abc and ABC as different values, use EXACT with SUMPRODUCT instead. EXACT compares text with case sensitivity, and SUMPRODUCT adds up the TRUE results after they are converted to 1s.

With the values to check in A2:A8, this formula counts only cells that exactly match lower-case south and returns 1:

=SUMPRODUCT(--EXACT("south",A2:A8))

Unlike COUNTIF, this approach compares the full cell content and respects capitalization. It is useful for case-sensitive IDs or codes, but is more complex than COUNTIF. For ordinary text categories where case does not matter, COUNTIF is generally simpler.

Count text with more than one condition

When a count must meet multiple conditions at the same time, use COUNTIFS. It applies each criteria range and criterion together, so a row is counted only when all specified conditions match. The ranges must have the same dimensions.

For example, suppose names are in A2:A20 and order statuses are in B2:B20. To count rows where the name is Maya and the status is Shipped, use:

=COUNTIFS(A2:A20,"Maya",B2:B20,"Shipped")

COUNTIFS is useful for questions such as how many records belong to a particular department and have a particular status. It is not the same as counting either condition: both must be true for the same row. For an either-or count, calculate each condition separately and consider whether records could meet both conditions, since adding two counts may count overlapping rows twice.

Common COUNTIF problems and fixes

If a formula returns zero even though the text appears in the range, check the underlying cell contents. Text imported from another system may have extra spaces, nonbreaking spaces, or different punctuation. A cell might look like North but contain North followed by a space, making an exact match fail. Try a wildcard criterion for a quick diagnosis, then clean the source data if exact matching is required.

  • Check the range: Confirm it includes the data rows and does not accidentally point to a neighboring column.
  • Check quotation marks: Typed text criteria need straight double quotes, not typographic “curly” quotes.
  • Check the wildcard: Use * for any number of characters and ? for one character; remember that COUNTIF does not distinguish capitalization.
  • Check spaces and hidden characters: Compare the source values carefully, especially after copying data from websites or other applications.
  • Check the separator: The formulas here use commas. Excel’s list separator can depend on regional settings; if Excel expects semicolons, use the separator shown by your installation.

For repeated counting across a changing list, use a range that covers the expected data or convert the data to an Excel table so formulas can use structured references. Avoid selecting an unnecessarily huge range if workbook size and calculation speed are concerns. If you copy a formula down a column, consider whether the range should remain fixed; for example, absolute references such as $A$2:$A$20 keep the same range when copied.

When to choose another approach

COUNTIF is a good choice for one condition in one range. COUNTIFS is the natural next step for several conditions. Use SUMPRODUCT with EXACT when capitalization matters. For a quick visual count of categories, a PivotTable may be more useful because it summarizes many different values at once instead of requiring a separate COUNTIF formula for each one.

For a one-time check, Excel’s Find feature can locate text, but it is not a formula-driven count that updates as the data changes. COUNTIF recalculates when relevant cell contents change, making it a better fit for a dashboard or ongoing report. If you need to return matching rows rather than just count them, a filtering tool or a dynamic-array function may suit the task better than COUNTIF.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text criteria are case-insensitive, so Apple and apple match the same criterion. Use SUMPRODUCT with EXACT when capitalization must be significant.

How do I count cells containing a word anywhere?
Put an asterisk before and after the word in the criterion. With data in A2:A20, for example, =COUNTIF(A2:A20,"*apple*") counts cells containing apple as part of a longer value as well as cells containing only apple.

Can COUNTIF use a cell reference as its criterion?
Yes. Put the range first and the criterion cell reference second, without quotation marks around the reference. Use concatenation with & when combining a reference with wildcards or a comparison operator.

Why does COUNTIF return zero for text that looks identical?
The cell may contain an extra space or hidden character, or the formula may use the wrong range. Check the underlying value and whether the criterion needs wildcards. COUNTIF ignores capitalization, so case alone will not explain a zero result.

What is the difference between COUNTIF and COUNTIFS?
COUNTIF tests one condition. COUNTIFS tests multiple range-and-criterion pairs and counts a row only when every condition is satisfied. Use COUNTIFS when, for example, you need a specific name and a specific status on the same record.

Related

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.