COUNTIF for X in a Cell: Exact or Partial Match

Formulas & functions · Updated November 21, 2026

To count cells whose entire value is x, use =COUNTIF(A2:A8,"x"); to count cells that contain x anywhere, use =COUNTIF(A2:A8,"*x*"). If you mean counting how many times the character x appears inside one cell, COUNTIF alone cannot do that; a LEN-and-SUBSTITUTE formula is the better fit.

The right formula depends on what “x in a cell” means: an exact value, part of a longer entry, or a repeated character within a single entry. The examples below are for Excel for Microsoft 365 on Windows. The formulas also work in current Excel for Mac and Excel for the web; the main difference between platforms is the interface, not these formulas.

What COUNTIF Does

COUNTIF checks each cell in a specified range against one condition and returns the number of cells that meet it. Its syntax is COUNTIF(range, criteria): the range is the cells to inspect, and the criteria describe what should count. The function returns one total, rather than marking each matching cell or counting every matching character.

For example, imagine A2:A8 contains x, X-ray, box, fox, maximum, a blank cell, and X x. An exact-value test counts only the cell whose entire content is x. A contains test counts entries where x appears anywhere, including inside words. COUNTIF’s text comparisons are not case-sensitive, so it treats x and X as the same letter for these tests.

COUNTIF is useful for quick summaries, inventory checks, survey responses, and quality checks. It can count text, numbers, dates, and other values when the condition is written appropriately. For this question, the important distinction is whether x must be the whole cell value or can be part of a longer string.

Count Cells Equal to x

Use an exact text criterion when a cell must contain only x. With the example values in A2:A8, the formula below returns 1: A2 contains x, while entries such as X-ray and X x have additional characters and do not qualify.

With the values to check in A2:A8, enter this formula in another cell: =COUNTIF(A2:A8,"x"). The result is 1.

Quotation marks around x tell Excel that the criterion is text. Do not put quotation marks around the range, and make sure the range covers the data rather than the cell containing the formula. If data is added later, extend the range or use an Excel Table so the formula can include new rows more conveniently.

Although this is an exact whole-cell comparison, it is not case-sensitive. It counts a cell containing X in the same way as x. If uppercase and lowercase must be treated differently, COUNTIF is not sufficient by itself; a case-sensitive method such as EXACT combined with an array calculation is needed.

Count Cells That Contain x

To count a letter anywhere in a cell, put an asterisk before and after it in the criterion. In Excel criteria, an asterisk represents any number of characters, including no characters. Thus, *x* means that any text may appear before or after x. In the example range, this counts x, X-ray, box, fox, maximum, and X x, for a total of 6. The blank cell is not counted.

With the example data in A2:A8, this formula returns 6: =COUNTIF(A2:A8,"*x*").

The two asterisks matter. The criterion x* means the cell must start with x, while *x means it must end with x. For instance, a range containing x, xylophone, and box would give different results for those three criteria. Use the version that matches the rule you need, rather than adding wildcards automatically.

COUNTIF treats the wildcard match as a text search and does not distinguish x from X. It also counts each qualifying cell only once, even if x appears several times in that cell. A cell reading “xoxo” contributes 1 to the result for *x*, not 2. To count occurrences rather than qualifying cells, use the method below.

Count x Occurrences Inside One Cell

To count repeated characters inside one cell, subtract the length of the text after removing x from the original text length. Because SUBSTITUTE is case-sensitive, converting the text to lowercase first makes this example count both x and X. For a cell containing “Xoxo x,” there are three x characters, so the formula returns 3.

With the text to inspect in A2, enter this formula in another cell: =LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2),"x","")). For A2 containing Xoxo x, the result is 3.

LEN counts characters, and SUBSTITUTE creates a version of the text with each lowercase x removed. LOWER makes uppercase X lowercase before that replacement. The difference between the two lengths is the number of removed characters. If you need to count only lowercase x and ignore uppercase X, use =LEN(A2)-LEN(SUBSTITUTE(A2,"x","")). With A2 containing Xoxo x, that case-sensitive version returns 2.

This approach counts occurrences in one cell; it does not count how many cells in a range contain at least one x. For that range-level question, COUNTIF with *x* is simpler. Keeping those two tasks separate avoids a common mistake: using a formula that counts matching cells when the goal is to count characters.

Choose the Right Criterion

Use the criterion that describes the position and amount of matching text you want. These examples assume the values being checked are in A2:A8; each formula returns a count of matching cells.

  • Cell equals x: =COUNTIF(A2:A8,"x") counts cells whose full text is x, regardless of letter case.
  • Cell contains x anywhere: =COUNTIF(A2:A8,"*x*") counts cells with x in any position, including inside a longer word.
  • Cell starts with x: =COUNTIF(A2:A8,"x*") counts text beginning with x, such as xylophone, but not box.
  • Cell ends with x: =COUNTIF(A2:A8,"*x") counts text ending in x, such as a value ending with the letter in “box.”

There are also wildcard characters for more specific patterns. A question mark represents exactly one character, so x? matches a two-character value that starts with x. A tilde escapes a wildcard when you want to search for the literal character: for example, ~* represents an asterisk rather than “any sequence.” To find that literal asterisk anywhere, the criterion is *~**: the first and last asterisks allow surrounding text, and the tilde makes the middle asterisk literal.

Common Problems and Checks

If a formula returns zero unexpectedly, first check the range and the criterion. The range must include the cells containing the values, and the text criterion needs quotation marks inside the formula. Also check whether you intended an exact match or a contains match: "x" and "*x*" ask different questions.

  • Extra spaces: A cell containing x followed by a space is not equal to x. A contains test may still find x in it. The TRIM function can remove ordinary leading, trailing, and repeated internal spaces in a cleaned copy of text, but it does not remove every special space character.
  • Numbers stored as text: A value that looks like a number may be stored as text. This matters when counting numeric criteria, though it is usually less relevant to a literal x search.
  • Case: COUNTIF does not distinguish uppercase and lowercase letters. Use another approach if that distinction is essential.
  • Wildcards in the data: Asterisk and question mark have special meanings in criteria. Use a tilde to search for either character literally.
  • Formula separator settings: Some regional Excel settings use semicolons between arguments instead of commas. If Excel rejects an otherwise valid formula, check the list-separator convention used by that installation.

To inspect an apparent mismatch, select the cell and look at its contents in the formula bar. If a value came from a web page or another system, it may include a nonbreaking space or other invisible character. A visible x does not always mean the stored text is exactly the same as a manually typed x.

COUNTIF Compared with Related Options

COUNTIF is the straightforward choice when one condition applies to one range. If you need to count entries that meet two or more conditions—for example, records containing x in one column and belonging to a particular department in another—COUNTIFS is designed for multiple criteria. It is not necessary for a single x test.

For counting characters within one cell, LEN and SUBSTITUTE are a better match because they measure text length before and after removing the target character. For case-sensitive checks, EXACT can compare text while respecting letter case, but a range-level total requires combining it with an array calculation. Choose the approach based on the question rather than trying to make COUNTIF handle every kind of text analysis.

Excel for Windows, Mac, and the web use the same COUNTIF syntax. You can type a formula directly into a cell or the formula bar; no special ribbon command is required. Keyboard shortcuts for entering or editing formulas vary by platform and keyboard settings, so using the formula bar is a reliable alternative when a shortcut differs.

Frequently Asked Questions

Does COUNTIF distinguish x from X?
No. COUNTIF text criteria are not case-sensitive, so a criterion of x also matches uppercase X. Use a case-sensitive comparison method if capitalization changes whether a cell should count.

How do I count cells that have x anywhere in the text?
Use a criterion with an asterisk on each side: =COUNTIF(A2:A8,"*x*"). Replace A2:A8 with the range you need. The result counts matching cells, not the total number of x characters.

How do I count how many x characters are in one cell?
Use LEN minus the length after SUBSTITUTE removes x. To count uppercase and lowercase versions together in A2, use =LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2),"x","")).

Why does COUNTIF with x return zero?
Check whether the cell contains extra characters or spaces, whether the range is correct, and whether you need a contains criterion such as *x* instead of an exact criterion. Imported text can also contain invisible characters.

Can COUNTIF count x only at the beginning of a cell?
Yes. Use =COUNTIF(A2:A8,"x*") for a range in A2:A8. The wildcard after x allows any text to follow it, while x must be the beginning of the cell value.

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.