COUNTIF Equals Text in Excel: Examples and Tips

Formulas & functions · Updated November 22, 2026

To count cells that equal a particular text value, use COUNTIF with the range and the text in double quotation marks. With the text to check in A2:A10, enter =COUNTIF(A2:A10,"Apple"); it counts cells whose entire contents equal Apple, without distinguishing uppercase from lowercase.

What COUNTIF Means by Equal to Text

COUNTIF has two arguments: the range to examine and the criterion that determines which cells count. A text criterion such as Apple is enclosed in straight double quotation marks when typed directly into a formula. In this basic form, Excel compares the criterion with each cell and counts matches.

For example, suppose A2:A10 contains Apple, Green Apple, Apple, apple, Pineapple, a blank, Apple followed by a space, Orange, and Apple. The formula above returns 4. It counts the three cells displaying Apple with that capitalization and the cell displaying apple, because COUNTIF does not distinguish letter case. It does not count Green Apple or Pineapple, which contain extra text, or the value with a trailing space.

In ordinary use, the criterion Apple means that the cell’s whole value must match Apple. You do not have to add an equals sign to make a basic text comparison exact. If you prefer to make the equality operator explicit, it can be included inside the criterion:

With the same sample values in A2:A10, this formula also returns 4: =COUNTIF(A2:A10,"=Apple").

The equals sign in this example is part of COUNTIF’s criteria syntax, not a second Excel formula operator. The formula still begins with its own equals sign, as all normal Excel formulas do.

How to Enter the Formula

In Excel for Microsoft 365 on Windows, select the cell where you want the result, type the formula, and press Enter. Replace A2:A10 with the cells in your own worksheet and replace Apple with the exact text you want to count. The range can be on another worksheet if you include the sheet name in the reference.

For example, if the values are in cells B2 through B500, change the range to B2:B500. Keep the range fixed to the relevant data: including a heading can affect results if a criterion happens to match the heading. If your data is stored in an Excel table, a table reference can make the formula easier to read and can expand as table rows are added.

When typing a text criterion directly into the formula, use straight quotation marks, not curly typographic quotation marks. Excel generally inserts the correct straight marks when you type them in the formula bar. If your regional settings use semicolons between function arguments, Excel may display semicolons rather than commas; the formula examples here use commas.

Use a Cell as the Text Criterion

Instead of typing the word into the formula, you can place the desired text in a worksheet cell. This is useful when the criterion changes often, when several formulas should use the same value, or when another person needs to update the selection without editing formulas.

With the data in A2:A10 and the criterion Apple entered in D2, use =COUNTIF(A2:A10,D2); the result is 4 for the sample data above. Do not put quotation marks around D2 in the formula: doing so would search for the literal text D2 rather than use the value in that cell.

A criteria cell can also hold a wildcard pattern or an operator-based criterion. For example, a cell containing Apple* asks COUNTIF to match values that begin with Apple. This is one reason to check the contents of a criteria cell when a result seems unexpected: the cell may contain a wildcard, an extra space, or an operator as well as the text.

Match Text That Contains a Word

Exact equality is not the same as finding text anywhere in a cell. COUNTIF supports two wildcard characters in criteria: an asterisk (*) represents any number of characters, including none, and a question mark (?) represents one character. Use them when you want a partial match rather than a whole-cell match.

With the example values in A2:A10, use =COUNTIF(A2:A10,"*Apple*") to count cells containing Apple anywhere in the text. It returns 7: Apple, Green Apple, the lowercase apple, Pineapple, the value with a trailing space, and the other exact Apple entries. The wildcard pattern is case-insensitive, just like a regular COUNTIF text criterion.

Use a wildcard only when that broader match is intended. For instance, Apple* matches text beginning with Apple, while *Apple matches text ending with Apple. A criterion of *Apple* can count words such as Pineapple, which may not be a desired match if the goal is to count only the separate word Apple. COUNTIF does not interpret spaces as word boundaries.

If the text being searched for includes a literal asterisk or question mark, put a tilde (~) before that character in the criterion to treat it as ordinary text rather than a wildcard. This distinction matters in product codes and labels that contain those symbols.

Case Sensitivity and Exact Matching

COUNTIF is not case-sensitive. A criterion of Apple counts Apple, APPLE, and apple as equivalent text, provided the rest of the cell contents match. This behavior is convenient for ordinary names and categories, but it cannot distinguish labels whose capitalization has a specific meaning.

If capitalization matters, use EXACT with SUMPRODUCT. With the sample values in A2:A10, the following formula returns 3 because it counts only cells whose text is exactly Apple with that capitalization: =SUMPRODUCT(--EXACT(A2:A10,"Apple")). EXACT checks both the characters and their case, and SUMPRODUCT adds the resulting match indicators. It also requires the cell contents to match the full text; Green Apple is not a match.

For a simple case-insensitive equality check, COUNTIF remains easier to read. Choose the case-sensitive alternative only when the distinction is important, such as when codes or identifiers use capitalization to represent different values.

Common Reasons the Count Looks Wrong

A result of zero or a count that is too high often comes from a difference between what a cell displays and what it actually contains. Check these common causes before changing the formula:

  • Leading or trailing spaces: Apple and Apple followed by a space are different text values for an exact match. Spaces can be difficult to notice on screen, especially in imported data.
  • Hidden characters: Data copied from a website or another system may contain nonprinting characters. A visible label can therefore fail to match a clean criterion.
  • Wildcards in the criterion: An asterisk or question mark has special meaning in COUNTIF. A criterion intended as a literal value may match more cells than expected if it includes one.
  • The wrong range: Check that the formula covers all intended data rows and does not accidentally include a heading, total, or unrelated column.
  • Different text: Hyphens, punctuation, accents, and spacing must match for an exact comparison. Similar-looking labels are not necessarily identical.
  • Formula stored as text: If Excel displays the formula itself instead of a result, the cell may be formatted as Text or the entry may begin with an apostrophe. Change the cell format to General and re-enter the formula.

To investigate a suspected space, inspect the source cell in the formula bar or compare it with a known clean value. Functions that clean text can be useful for preparing imported data, but applying cleanup changes to the data may affect other uses of the workbook. Keep an untouched copy of source data when the original values need to be preserved.

COUNTIF Compared with Other Counting Options

COUNTIF is the right choice when one range and one condition are enough: for example, count all cells in a status column that equal Complete. It returns one number and does not identify which cells matched. If you need to filter and inspect matching rows, use Excel’s filtering tools instead of relying only on a count.

Use COUNTIFS when the count must satisfy more than one condition, such as matching a category in one column and a region in another. COUNTIFS applies all supplied criteria together. If the only requirement is a case-insensitive text equality check, COUNTIF is simpler; if matching case is essential, use a case-sensitive method such as the EXACT and SUMPRODUCT example.

For a changing list of categories, you can copy the criteria into separate cells and use a COUNTIF formula for each one. That approach keeps the criteria visible and makes a summary table easier to review. It also avoids repeatedly rewriting the text inside formulas when the categories change.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF treats text criteria as case-insensitive, so Apple and apple count as matches for the same criterion. Use a case-sensitive approach with EXACT if capitalization must be respected.

Do I need to type an equals sign before the text criterion?
No. A criterion such as “Apple” already matches cells equal to Apple. You may write the criterion as “=Apple” to show the equality operator explicitly, but it gives the same result for this basic comparison.

How do I count cells that contain a word rather than equal it?
Use an asterisk before and after the word in the criterion. For example, *Apple* finds Apple anywhere in a cell, including inside a longer word such as Pineapple. That broader behavior is why wildcards should be used deliberately.

Can the text to match come from another cell?
Yes. Refer to the criteria cell as the second COUNTIF argument without quotation marks around the cell reference. When the cell’s value changes, the count updates using the new criterion.

Why does COUNTIF return zero when the text looks identical?
Look for extra spaces, hidden characters, punctuation differences, or an incorrect range. A cell may display the same visible letters while containing an additional character that prevents an exact match.

Can COUNTIF count blank cells?
Yes. COUNTIF can use a blank criterion to count empty cells, but cells containing formulas that return an empty string can make blank-related counts less intuitive. Check how the cells are populated before interpreting that result.

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.