Google Sheets COUNTIF Contains Text: Formula and Examples

Yes. In Google Sheets, use =COUNTIF(A2:A100,"*text*") to count cells in A2:A100 that contain “text” anywhere; the asterisks are wildcards, and COUNTIF does not distinguish uppercase from lowercase. Replace the range and search term with your own values.

How the basic formula works

COUNTIF checks a range against one condition and returns the number of cells that meet it. Its general form is =COUNTIF(range, criterion). To find a substring rather than an exact match, put an asterisk before and after the term in the criterion. An asterisk represents any number of characters, including no characters.

For example, suppose A2:A8 contains North, Northwest, South, northern, East, North, and a blank cell. With those values in A2:A8, =COUNTIF(A2:A8,"*North*") returns 4. It counts North twice, Northwest, and northern. The match is not case-sensitive, and the matching letters can appear anywhere in a cell.

COUNTIF returns a number, not a list of the matching cells. If you want to see which records matched, you can use a filter or a separate formula that returns rows; COUNTIF is specifically useful when the result you need is a tally.

Find text at the beginning or end of a cell

You do not have to search the whole cell. Put the wildcard on only the side where additional characters are allowed:

=COUNTIF(A2:A100,"North*") counts cells that start with North.
=COUNTIF(A2:A100,"*west") counts cells that end with west.
=COUNTIF(A2:A100,"*North*") counts cells with North anywhere in the cell.

With North, Northwest, South, northern, East, North, and a blank in A2:A8, =COUNTIF(A2:A8,"North*") returns 4: each of the four entries beginning with those letters qualifies. With the same data, =COUNTIF(A2:A8,"*west") returns 1 because Northwest ends in west. The wildcard may match spaces and punctuation too; it is not limited to letters.

Use a cell reference for a changeable search term

A cell reference makes it easy to change the search without editing the formula. Assume the values are in A2:A8 and E2 contains North. Use =COUNTIF(A2:A8,"*"&E2&"*"). The ampersands join the wildcard characters to the value in E2, making the criterion equivalent to "*North*". For the example list above, the result is 4.

This is useful for a small search box, a report where the requested term changes often, or a list of search terms maintained on another sheet. If E2 is blank, the criterion becomes two asterisks. That is not a reliable way to ask for all cells or for blank cells; use an explicit blank criterion such as "" when you need to count empty cells, or test the search input separately before displaying a result.

Understand wildcards and exact matches

The two wildcard characters most people need are * and ?. An asterisk matches zero or more characters; a question mark matches exactly one character. For instance, a criterion of "?at" can match cat or hat, while "*at" can match any text ending in at, including cat and format. Wildcards are part of the criterion, not characters typed into the cells.

Without wildcards, COUNTIF normally checks for an exact criterion. Assuming product names are in A2:A10, =COUNTIF(A2:A10,"mug") counts cells whose value is mug, rather than every cell containing those letters. The wildcard version =COUNTIF(A2:A10,"*mug*") also matches names such as travel mug or mug stand. Both approaches ignore case.

COUNTIF with wildcards is a substring search, not a whole-word search. A search for cat can match cat in category or scatter. If you need word boundaries, choose a regular-expression approach and define the boundaries deliberately; ordinary asterisks do not distinguish separate words.

Count cells containing more than one condition

Use COUNTIFS when records must satisfy multiple conditions. Suppose A2:A6 contains product descriptions and B2:B6 contains statuses: red mug/Complete, blue mug/Pending, red plate/Complete, mug stand/Complete, and red mug/Pending. With descriptions in A2:A6 and statuses in B2:B6, =COUNTIFS(A2:A6,"*red*",B2:B6,"Complete") returns 2. The qualifying rows are red mug/Complete and red plate/Complete.

You can also apply more than one text condition to the same range. If descriptions are in A2:A6, =COUNTIFS(A2:A6,"*red*",A2:A6,"*mug*") counts cells that contain both red and mug. Each condition must be true for the same cell in that row. COUNTIFS combines criteria as AND conditions; it does not mean “red or mug.” For an OR count, add separate COUNTIF results, taking care not to count entries that meet both conditions twice.

Search for a literal asterisk or question mark

Because * and ? have special meaning in a criterion, use a tilde (~) to search for one as a literal character. For example, assume A2:A5 contains Plan*, Plan A, *Plan, and Plan**. With that range, =COUNTIF(A2:A5,"*~**") returns 2: it counts Plan* and Plan**, where an actual asterisk appears somewhere in the cell. The first and last asterisks in the criterion are wildcards; ~* represents the literal asterisk.

Likewise, to search for a literal question mark, escape it with a tilde. This distinction matters in text containing symbols, filenames, or shorthand. If a search term comes from a cell and could itself contain * or ?, concatenate wildcards only when those symbols are intended to act as wildcards; otherwise, escape them in the criterion or use a method designed for literal matching.

Case-sensitive matching and alternatives

COUNTIF is case-insensitive, so a criterion for cat counts cat, Cat, and CAT alike. If capitalization matters, use FIND, which is case-sensitive, together with SUMPRODUCT. Suppose A2:A5 contains Cat, catfish, scatter, and CAT. With those values in A2:A5, =SUMPRODUCT(--ISNUMBER(FIND("cat",A2:A5))) returns 2: it finds lowercase cat in catfish and scatter but not in Cat or CAT. FIND looks for a literal sequence, so * and ? do not act as wildcards in its search text.

For more structured patterns, Google Sheets also provides REGEXMATCH, which checks text against a regular expression. For example, regular expressions can express boundaries or alternatives that are awkward with COUNTIF wildcards. They use their own syntax and can treat capitalization differently depending on the pattern, so they are not automatically a simpler replacement. Use COUNTIF for straightforward, case-insensitive substring counts; use another function when you specifically need case sensitivity or pattern rules.

The common COUNTIF wildcard pattern also works in Excel, but this page describes Google Sheets in a web browser. Excel has additional tools and functions of its own; do not assume that every Sheets-specific function or menu is available in Excel.

Fix common counting mismatches

If your result seems too high or too low, check the details of the data and criterion before changing functions:

Check the range. Make sure it includes every intended row and does not include headings or unrelated notes. COUNTIF counts matching cells even when their rows are hidden or filtered out.
Check whether you need wildcards. A criterion such as "cat" asks for an exact match; "*cat*" asks for those letters anywhere in the cell.
Look for spaces and punctuation. A trailing space or a hyphen can affect an exact match. A substring search may still match, but punctuation can change whether the letters appear in the expected sequence.
Remember that case is ignored. Changing cat to CAT will not narrow a COUNTIF result. Use a case-sensitive method if capitalization separates categories in your data.
Check wildcard symbols in the data. An unescaped * or ? in the criterion is interpreted as a wildcard, not necessarily as a literal symbol.
Confirm that the formula is counting cells, not visible rows. COUNTIF does not automatically exclude filtered-out records. If the task is specifically to count only visible matches, a different approach is needed.

A useful diagnostic is to try the criterion on a short range with a few known examples. If a cell does not match as expected, inspect its full contents and compare the intended exact, partial, or case-sensitive rule. This is often quicker than building a more complicated formula around a misunderstood criterion.

Frequently Asked Questions

Does COUNTIF in Google Sheets distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive. For example, a contains-text criterion for cat matches Cat and CAT as well as cat. Use FIND with an array-aware counting formula when you need a case-sensitive substring check.

How do I count cells that do not contain a word?
For a simple non-match count, use the not-equal operator with wildcards. If the values are in A2:A100, =COUNTIF(A2:A100,"<>*cat*") counts cells that do not contain cat. Check how blanks should be handled for your task; if blank cells must be excluded explicitly, add a nonblank condition with COUNTIFS.

Can I count text from another cell?
Yes. If A2:A100 is the data range and E2 contains the search term, use =COUNTIF(A2:A100,"*"&E2&"*"). The asterisks allow other characters before and after the term. If E2 may contain wildcard symbols that should be literal, account for them rather than letting COUNTIF interpret them as patterns.

Does COUNTIF count only visible or filtered rows?
No. COUNTIF evaluates the cells in the specified range, including rows hidden manually or excluded by a filter. Use a different formula or workflow if the count must reflect only the currently visible records.

Why does COUNTIF return zero when I can see the word?
Common causes include searching for an exact match instead of using wildcards, selecting the wrong range, using a term with an unexpected space or punctuation mark, or expecting a case-sensitive distinction. Try "*term*" for a contains search and verify the range and underlying cell text.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.