COUNTIF Excel by Text Match: Exact and Partial Matches

Formulas & functions · Updated November 22, 2026

Yes. In Excel, use COUNTIF to count cells that match text: with the values in A2:A9, =COUNTIF(A2:A9,"Apple") counts cells equal to Apple, while =COUNTIF(A2:A9,"*apple*") counts cells containing apple anywhere. COUNTIF ignores letter case, and the asterisks make the second search a partial match.

How COUNTIF matches text

COUNTIF has two arguments: the range to check and the criterion that defines a match. It evaluates each cell in the range against that criterion, then returns one count. The criterion can be a text value, a wildcard pattern, a comparison such as “greater than,” or a reference to a cell that contains the criterion.

For a basic exact-text count, assume A2:A9 contains these entries: Red apple, Apple, apple, Pineapple, Apple pie, green apple, a blank cell, and Apple. With those values in A2:A9, =COUNTIF(A2:A9,"Apple") returns 3: the cells containing Apple, apple, and Apple. The match is exact in the sense that the cell’s text must be the same apart from letter case; Apple pie and Pineapple do not count.

For an ordinary word or phrase, put the text in quotation marks when typing it directly into a formula. If you use a cell reference instead, do not put the reference in quotation marks. That distinction lets a formula use the contents of another cell as its criterion.

Exact text versus partial text

An exact criterion matches the whole cell. This is useful when a column contains one category per cell, such as “Open,” “Closed,” or “Pending,” and you need to count one category without counting longer entries that happen to contain the same word.

A partial match uses wildcards. In the same A2:A9 example, =COUNTIF(A2:A9,"*apple*") returns 7. Every nonblank entry contains the letters apple somewhere, including Pineapple and Apple pie. The asterisk can stand for any number of characters, including no characters, so the pattern also matches a cell whose entire value is Apple.

Choose the pattern based on where the text may appear:

  • =COUNTIF(A2:A9,"Apple*") counts entries that begin with Apple. In the sample list, it returns 4: Apple, apple, Apple pie, and Apple.
  • =COUNTIF(A2:A9,"*apple") counts entries that end with apple. It returns 4: Red apple, apple, Pineapple, and green apple.
  • =COUNTIF(A2:A9,"*apple*") counts entries with apple anywhere in the cell. It returns 7.

These patterns are not case-sensitive. The starting or ending position is still important: *apple does not match Apple pie because that text ends with pie, while Apple* does not match Red apple because it does not begin with Apple.

Using wildcards, question marks, and literal symbols

COUNTIF recognizes three wildcard characters in text criteria. An asterisk (*) represents any sequence of characters, a question mark (?) represents exactly one character, and a tilde (~) tells Excel to treat the following wildcard as a literal character. This is useful for matching a range of names or codes without listing every possible value.

For example, assume product codes are in A2:A20. The criterion “AB?” matches a three-character code that begins with AB, such as AB7, but not AB12. “AB*” can match AB followed by any number of characters. These criteria are patterns, not regular expressions; COUNTIF does not support the richer pattern syntax used by some other tools.

If a cell contains an actual asterisk or question mark that you need to find, escape it with a tilde. With codes in A2:A20, =COUNTIF(A2:A20,"A~*B") counts cells containing the literal text A*B. The tilde is part of COUNTIF’s criterion syntax; it is not a character Excel expects to find in the cell.

Building criteria from another cell

Using a cell reference makes a count easier to change without editing the formula. If E2 contains Apple and the list is in A2:A9, =COUNTIF(A2:A9,E2) counts exact matches to the value in E2. Changing E2 to Pear changes the criterion automatically. This is convenient for a summary table where each row holds a different category to count.

To use a cell’s contents in a partial match, join the wildcard characters to the reference with ampersands. With the same list in A2:A9 and the search word in E2, =COUNTIF(A2:A9,"*"&E2&"*") counts cells containing the text in E2. If E2 contains app, the formula matches entries such as Apple, Pineapple, and green apple. If E2 is blank, the pattern becomes two asterisks and can match every text value, so consider whether a blank search cell should be allowed.

Criteria can also include comparison operators. For example, a condition built from a cell can join the operator and reference with an ampersand; this is useful for numeric counts as well as text criteria. When you want to count a literal text value that begins with a comparison symbol, be careful: COUNTIF may interpret symbols such as > or < as operators. A helper column or a different formula may be a clearer choice for unusual text values.

Counting text with more than one condition

COUNTIF handles one condition. When a row must meet multiple conditions at the same time, use COUNTIFS. For example, suppose regions are in A2:A20 and order statuses are in B2:B20. The formula =COUNTIFS(A2:A20,"North",B2:B20,"Open") counts rows where the region is North and the status is Open. Each additional range must correspond in size and shape to the first range.

COUNTIFS combines its conditions with AND logic: a row must satisfy every supplied criterion to be counted. A text criterion can also use wildcards, and a criterion can refer to a cell. If you instead need rows that meet one condition or another, consider separate COUNTIF results and add them, taking care not to count overlapping rows twice. For more complex OR logic, a helper column can make the rule easier to inspect.

Case sensitivity and alternatives

A common surprise is that COUNTIF does not distinguish uppercase and lowercase letters. A criterion of Apple will count apple and APPLE as well as Apple. For most category lists this is helpful, since capitalization differences usually should not create separate categories. If capitalization is meaningful in your data, use a case-sensitive method instead.

Assume the text to check is in A2:A9 and the case-sensitive target is in E2. The formula =SUMPRODUCT(--EXACT(A2:A9,E2)) counts exact, case-sensitive matches; if E2 contains Apple in the sample list, it returns 2 because only the two cells written as Apple match exactly. EXACT compares text with case sensitivity, and SUMPRODUCT adds the resulting TRUE/FALSE comparisons as a count.

For a case-sensitive partial match, the logic is more involved than COUNTIF’s wildcard criterion. A helper column using a case-sensitive text search can be easier to maintain, especially when other people need to audit the workbook. Use COUNTIF when its case-insensitive behavior is suitable; choose a more explicit formula only when the distinction matters.

Common reasons a text count looks wrong

If the result is unexpectedly low or high, check the data and the criterion before replacing the formula. A visually identical value may contain an extra space, a nonprinting character, or a different punctuation mark. Also check whether the intended search is exact or partial: adding wildcards changes which cells qualify.

  • Leading or trailing spaces: “Apple” and “Apple ” may look alike but are not the same text. If the original values are in A2:A9, enter =TRIM(A2) in B2 and fill it down to clean ordinary leading and trailing spaces before counting the cleaned range. TRIM also reduces repeated internal spaces to one.
  • Nonbreaking spaces: TRIM does not remove every character that looks like a space. Data copied from a web page may contain a nonbreaking space. If cleaning with TRIM does not help, inspect the source or use a deliberate cleanup step for that character.
  • Wrong range: Confirm that the range covers all the data rows and does not include a heading that could match the criterion. In a table that grows over time, a structured table column can be easier to maintain than a fixed range.
  • Wildcard interpreted as a pattern: If the criterion contains * or ?, COUNTIF treats those characters as wildcards unless they are escaped with ~.
  • Unexpected blank criterion: A blank criterion or a reference to an empty cell can lead to results that are not what you intended. Check the criterion cell and decide how the formula should behave when it is empty.

To troubleshoot efficiently, test a simple exact match first, then add a wildcard or cell reference only if needed. If a result still seems wrong, copy a suspect cell’s contents into the formula bar or a helper cell to look for spaces and unusual characters. Filtering the source column for the criterion can also help you see which entries Excel is treating as matches.

Choosing the right formula

Use COUNTIF for one condition on a range, such as counting one exact label or values containing a word. Use COUNTIFS when a row must satisfy multiple conditions. Use a case-sensitive formula when uppercase and lowercase differences matter. The most readable solution is generally the one that expresses the rule directly and can be checked against a few known rows.

Before relying on a count, verify it with a small manual check: identify the cells that should match, compare that number with the result, and test an entry that should not match. This is especially useful when importing data or building a report that will be refreshed. Excel formulas recalculate when the referenced values change, but they cannot tell whether the criteria reflect the business meaning you intended.

In Microsoft 365 for Windows, enter the formula in the cell where you want the result and press Enter. You can copy it down to count different criteria if the data range is fixed appropriately; use absolute references such as $A$2:$A$9 when copying so that the source range does not shift. On Mac, the formula syntax and COUNTIF behavior are the same, though keyboard shortcuts and some interface details differ. Excel for the web also supports COUNTIF, with the web interface rather than the Windows ribbon.

Frequently Asked Questions

Does COUNTIF match text exactly?
Yes, if the criterion has no wildcards, COUNTIF matches the whole cell value rather than just a portion. Its text matching is not case-sensitive, and extra spaces or other characters can still affect a match.

How do I count cells that contain a word?
Put an asterisk before and after the word in the criterion, such as =COUNTIF(A2:A20,"*invoice*") for a list in A2:A20. The asterisks allow any characters before or after the word.

Can COUNTIF distinguish uppercase and lowercase?
No. COUNTIF treats Apple and apple as equivalent. For case-sensitive exact matching, use EXACT with SUMPRODUCT or a suitable helper-column approach.

How do I count text that starts with a particular word?
Use the word followed by an asterisk, such as =COUNTIF(A2:A20,"North*") when the values are in A2:A20. This counts cells beginning with North, including longer text such as North region.

Why does COUNTIF return zero when the text looks the same?
Check for hidden spaces, nonprinting characters, punctuation differences, and whether the formula is checking the intended range. Also confirm that you have not used an exact criterion when you meant to search for text within a longer cell.

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.