COUNTIF in Excel for Text: Practical Examples and Tips

Formulas & functions · Updated November 20, 2026

Yes. In Excel, use COUNTIF to count cells that match text: with status values in A2:A20, count cells equal to Paid with =COUNTIF(A2:A20,"Paid"). COUNTIF can also find text that contains a word, match a pattern with wildcards, or use a criterion written in another cell.

What COUNTIF does with text

COUNTIF checks a range of cells 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 tells Excel what to look for. The function works with text, numbers, dates, and comparison conditions, but a single COUNTIF applies just one condition to its range.

For text, the criterion is usually written inside double quotation marks. A criterion such as "Paid" asks Excel to find cells whose contents match that word. COUNTIF is not case-sensitive, so it treats Paid, PAID, and paid as the same text for this purpose. It does not, however, ignore extra spaces or other differences in the text.

COUNTIF is available in Microsoft 365 for Windows, Mac, and Excel for the web, as well as many earlier Excel versions. The function and examples below work the same way across these platforms. Depending on your regional settings, Excel may require semicolons instead of commas between formula arguments; use the separator your installation accepts.

Count an exact text match

Use a word or phrase as the second argument when you want to count cells containing that text as their complete value. For example, with A2:A10 containing the statuses Paid, Pending, Paid, Cancelled, and Paid, the following returns 3:

With statuses in A2:A10, count cells exactly equal to Paid using =COUNTIF(A2:A10,"Paid").

The match is exact in the sense that extra characters make a different value. A cell containing Paid in full will not match the criterion Paid. Conversely, a cell containing a space after Paid may look identical on screen but may fail an exact match. If the count is unexpectedly low, inspect the source values for leading or trailing spaces, inconsistent punctuation, and spelling variations.

Text criteria must be enclosed in quotation marks when typed directly into the formula. Do not put quotation marks around the cell range. When selecting a range with the mouse while entering a formula, Excel inserts the reference for you; you can also type it directly.

Find text that contains a word

Wildcards let COUNTIF find text when you know only part of the cell value. The asterisk (*) represents any number of characters, including none. Put it before and after a word to find that word anywhere in a cell. With descriptions in B2:B12, count cells that contain the word refund with =COUNTIF(B2:B12,"*refund*"). This counts entries such as Refund requested and Customer asked for a refund.

The question mark (?) represents exactly one character. With product codes in C2:C20, count entries beginning with AX and ending in one character by using =COUNTIF(C2:C20,"AX?"). For instance, AX7 matches, while AX12 does not because there are two characters after AX. The criterion is not case-sensitive.

To search for a literal asterisk or question mark rather than use it as a wildcard, put a tilde (~) before the character. With notes in D2:D30, count cells containing an actual asterisk using =COUNTIF(D2:D30,"*~**"). The first and last asterisks allow other text before and after; ~* means a literal asterisk. The same escaping principle applies to a literal question mark or tilde.

Use a criterion stored in a cell

A criterion can come from a cell instead of being typed into every formula. This makes a count easy to update: change the text in the criterion cell, and the result recalculates. With order statuses in A2:A100 and the status to count in D2, use =COUNTIF(A2:A100,D2). If D2 contains Pending, the formula counts Pending entries in the range.

You can combine a wildcard with a cell reference by joining the wildcard characters to the cell value with the ampersand operator. With product descriptions in B2:B100 and the search word in D2, use =COUNTIF(B2:B100,"*"&D2&"*"). If D2 contains cable, the formula counts descriptions containing cable anywhere. This is useful for a small search box or a summary sheet where the term changes often.

Comparison operators can also be joined to a cell reference. With labels in A2:A100 and the criterion in D2, use =COUNTIF(A2:A100,"<>"&D2) to count cells whose values are not equal to D2. The operator must be inside quotation marks, and the ampersand joins it to the reference. Be aware that a not-equal criterion can include blank cells; use a separate nonblank condition if blanks should be excluded.

Count text entries or nonblank cells

If the goal is to count text entries regardless of what the text says, use the wildcard asterisk as the criterion. With mixed values in A2:A20, =COUNTIF(A2:A20,"*") counts text entries, including values that look like numbers but are stored as text. It does not count numbers stored as numeric values. This is different from counting every nonempty cell, which can include numbers and dates.

To count cells that are not blank, use a not-equal-to-empty criterion. With mixed values in A2:A20, =COUNTIF(A2:A20,"<>") counts nonblank entries, including text and numeric values. A cell containing a space is not truly blank, so it can be counted. Formulas returning an empty string can also make blank-counting results confusing; when that distinction matters, check the actual cell contents and test the specific workbook behavior rather than assuming visually empty cells are genuinely empty.

For a clean text-only count, make sure the data is consistent about whether numeric-looking entries are numbers or text. Excel stores these differently even when they look the same. A cell formatted to display a number with leading zeros, for example, may still contain a numeric value, whereas a code imported as text remains text.

Count text with more than one condition

COUNTIF handles one condition. When a text count also needs another condition, use COUNTIFS, which applies multiple conditions to corresponding ranges. For example, with status values in A2:A100 and departments in B2:B100, count Paid records in the Sales department with =COUNTIFS(A2:A100,"Paid",B2:B100,"Sales"). The result counts rows where both conditions are true.

Each criteria range in COUNTIFS should cover the same rows and columns as the others. If one range starts at row 2 and another starts at row 3, the conditions no longer describe corresponding records and may return an error. COUNTIFS can also use wildcard text criteria and cell references, following the same general rules as COUNTIF.

Do not add separate COUNTIF results when you need both conditions to apply to the same row. Adding the count of Paid records to the count of Sales records answers a different question: it combines two totals rather than finding records that are both Paid and Sales. Choose COUNTIFS when the conditions must be satisfied together.

Common COUNTIF problems and how to fix them

  • The count is too low: Check for extra spaces, spelling differences, punctuation, or inconsistent labels. An exact criterion such as Paid will not match Paid in full. Use a contains-text wildcard only if a partial match is intended.
  • The count is too high: A criterion such as *paid* also matches longer entries like Unpaid or Paid late. Narrow the criterion or use an exact match if those entries should not count.
  • The formula displays an error or is rejected: Check that the range and criteria are separated correctly, quotation marks are balanced, and your Excel locale uses the argument separator you entered. In some regional settings, formulas use semicolons rather than commas.
  • Some cells look blank but are counted: A space, an invisible character, or a formula returning an empty string is different from a truly empty cell. Imported data may contain nonprinting characters that are hard to spot.
  • The formula does not update as expected: Confirm that the referenced range includes the new rows and that calculation is set to update automatically. A fixed reference such as A2:A20 will not expand just because data is added below row 20.

When data comes from another system, cleaning it before counting may be more reliable than making the criterion increasingly complicated. A helper column can remove unwanted leading or trailing spaces with TRIM, or standardize labels so that variations are easier to identify. Review cleaned values before relying on the total, because cleanup can change the text the workbook uses elsewhere.

COUNTIF compared with other counting options

Use COUNTIF when one range and one condition are enough. Use COUNTIFS when several conditions must all be true, such as a particular status and department. These functions are usually clearer and easier to maintain than building a more elaborate formula for a straightforward count.

For a case-sensitive count, COUNTIF is not suitable because it ignores letter case. A formula using SUMPRODUCT and EXACT can compare text with case sensitivity. With text values in A2:A20 and the case-sensitive target in D2, use =SUMPRODUCT(--EXACT(A2:A20,D2)). If D2 contains Paid, this counts Paid but not PAID or paid. This approach is more specialized, so use it only when capitalization genuinely distinguishes values.

A PivotTable is another option when you need a summary of many text categories, rather than a single count. It can group records by a field such as status and display a count for each category. For a quick, formula-based total that updates when its criteria change, COUNTIF is often more direct. For an ongoing dataset, Excel Tables can also help formulas include added rows when the table grows.

Frequently Asked Questions

Is COUNTIF case-sensitive in Excel?
No. COUNTIF treats uppercase and lowercase versions of the same text as equal. For a case-sensitive comparison, use a formula based on EXACT, such as the SUMPRODUCT example above.

How do I count cells that contain a word anywhere?
Place an asterisk on each side of the word in the criterion, such as =COUNTIF(B2:B12,"*refund*"). The asterisks allow other characters before and after the search term.

Can COUNTIF count more than one word or condition?
One COUNTIF tests one condition. For multiple conditions that must all be true, use COUNTIFS. If you mean count cells matching either of two separate words, you can add two COUNTIF results, but make sure a cell matching both terms will not be counted twice.

Why does COUNTIF miss text that looks like a match?
Common causes include extra spaces, hidden characters, different punctuation, or a criterion that requires an exact match when the cell contains additional words. Check the underlying values, not just how they appear on screen.

Can COUNTIF count text in a different worksheet?
Yes. Use a range reference that includes the sheet name. For example, with status values in A2:A20 on a sheet named Orders, count Paid entries with =COUNTIF(Orders!A2:A20,"Paid"). If a sheet name contains spaces, surround the sheet name with single quotation marks in the reference.

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.