Google Sheets: Count Cells With Specific Text in a Range

To count cells that exactly match text in Google Sheets, use =COUNTIF(A2:A100,"Paid"); to count cells that contain the text anywhere, use =COUNTIF(A2:A100,"*Paid*"). Replace the range and text with your own values. COUNTIF ignores capitalization, so both formulas treat “Paid” and “PAID” as matches.

What COUNTIF counts in Google Sheets

COUNTIF checks each cell in a range against one condition and returns the number of cells that meet it. It is the simplest option when you want to count a word, phrase, status, or other text value. The first argument is the range to inspect; the second is the criterion, written in quotation marks when you type the text directly.

For examples below, imagine that A2:A9 contains these statuses: Paid, unpaid, Paid, Awaiting payment, a blank cell, Paid – late, REFUNDED, and PAID. This small list illustrates an important distinction: “matches the text” can mean either equals the text or contains it as part of a longer entry. Choose the criterion to fit the question you are asking.

COUNTIF is not limited to words. It can also count numbers, dates, and comparisons, but this guide focuses on text. In a Google Sheets browser tab, enter a formula in an empty cell and press Enter; the result updates when cells in the referenced range change. The examples use comma separators, as in an English-language Sheets locale. Some spreadsheet locales use semicolons between arguments instead.

Count cells that exactly match a word or phrase

For an exact text match, put the desired word or phrase in the criterion. With statuses in A2:A9, this formula returns 3 because three cells are Paid when capitalization is ignored. “Paid – late” is not an exact match, and neither is “unpaid.” A phrase with spaces works the same way: it must match the whole cell value, apart from COUNTIF’s case-insensitive comparison.

If your search term is in a cell rather than typed into the formula, refer to that cell as the criterion. For example, with statuses in A2:A9 and the word Paid in D1, =COUNTIF(A2:A9,D1) returns 3. This approach makes the search term easy to change without editing the formula. If D1 is empty, the result instead counts blank cells, so make sure the criterion cell contains the text you intend to search for.

An exact criterion is useful for clean categories such as “Approved,” “Pending,” or “Cancelled.” It can also reveal inconsistent data: if someone entered “Paid – late” or added an extra word, that cell will not count as an exact Paid status. If you want to include those variations, use a partial-text criterion instead.

Count cells that contain text anywhere

Google Sheets supports wildcard characters in COUNTIF criteria. An asterisk (*) stands for any number of characters, including no characters. Put one before and after a search term to match that term anywhere in a cell. With the example statuses in A2:A9, =COUNTIF(A2:A9,"*Paid*") returns 5: it matches Paid twice, unpaid, Paid – late, and PAID. This count is larger than the exact-match count because “unpaid” contains the letters “paid.”

That example shows why the wording of your question matters. If you mean the standalone status “Paid,” an “contains” search may include words such as “unpaid.” COUNTIF does not interpret a word boundary in an ordinary wildcard criterion. If your data uses labels that overlap, prefer an exact match or choose a more distinctive phrase.

The wildcard question mark (?) represents one character. For instance, with codes in A2:A20, =COUNTIF(A2:A20,"A?C") counts values with A, exactly one character, and then C. It can match ABC or A7C, but not AC or ABBC. This can help with predictable code patterns, though it is not a substitute for a regular expression when the matching rule is more elaborate.

Enter and adapt the formula

Select an unused cell where you want the count to appear.
Type =COUNTIF(, then select or type the range to inspect, such as A2:A100.
Type a comma and the criterion. Use quotation marks around text typed directly into the formula, or refer to a cell that contains the criterion.
Close the parenthesis and press Enter. Check a few matching rows to confirm that the result reflects an exact match or a contains search, as intended.

To keep the range from shifting when you copy the formula, use dollar signs. With text in A2:A100 and a criterion in D1, =COUNTIF($A$2:$A$100,D1) continues to inspect the same range when copied elsewhere. The dollar signs lock the range reference; they do not change how COUNTIF matches text.

For a partial match driven by a cell, combine wildcards with the cell reference. With text in A2:A100 and the search word in D1, =COUNTIF(A2:A100,"*"&D1&"*") counts cells containing the D1 text. In a formula, the ampersand joins the wildcard strings to the cell value. If D1 contains Paid, the formula behaves like the fixed “*Paid*” example.

Count text alongside another condition

Use COUNTIFS when a row must meet more than one condition. Each range-and-criterion pair adds a requirement, and the ranges should be the same size. Suppose A2:A9 contains the statuses listed earlier and B2:B9 contains West, East, West, West, East, North, West, and West. With those layouts, =COUNTIFS(A2:A9,"Paid",B2:B9,"West") returns 3. It counts rows where the status is Paid, without regard to capitalization, and the region is West.

COUNTIFS can also use a partial-text criterion. With product descriptions in A2:A100 and regions in B2:B100, =COUNTIFS(A2:A100,"*tea*",B2:B100,"West") counts West rows whose descriptions contain tea. It may match “teapot” as well as “green tea,” so review whether that broad match is appropriate. You can combine exact and partial criteria, but each additional condition narrows the result to rows satisfying all of them.

One common misunderstanding is that COUNTIFS can count a match in one range or another. Its conditions work as an AND: a row must satisfy every pair. For an OR question, such as counting either Paid or Refunded, add separate counts for the two exact criteria. Make sure the categories cannot overlap, or the same cell could be counted twice.

Case-sensitive matching and regular expressions

COUNTIF is case-insensitive. That is usually convenient for names and statuses entered inconsistently, but it cannot distinguish “Paid” from “PAID.” For a case-sensitive exact comparison, use EXACT with SUMPRODUCT. With the example values in A2:A9, =SUMPRODUCT(--EXACT("Paid",A2:A9)) returns 2, because only the two cells containing precisely Paid with that capitalization qualify. The double minus converts TRUE and FALSE results into values that SUMPRODUCT can add.

For more flexible text patterns, Google Sheets has REGEXMATCH, which checks text against a regular expression. With descriptions in A2:A100, =SUMPRODUCT(--REGEXMATCH(A2:A100,"(?i)tea")) counts cells containing tea, with the inline (?i) making the pattern case-insensitive. Remove (?i) for a case-sensitive pattern. Regular expressions have their own syntax, so use this option when wildcards are not enough, not simply to replace a straightforward COUNTIF.

These approaches are helpful for specialized rules, but they are less readable than COUNTIF and can be harder to maintain. If the criterion is a single word or phrase and capitalization does not matter, COUNTIF remains the clearest choice. For a complex pattern, test the expression on a small set of known examples before relying on the total.

Common problems and ways to check the result

The result is too high: A criterion such as "*paid*" includes partial words like unpaid. Switch to an exact criterion if only the standalone value should count.
The result is zero: Check for leading or trailing spaces, spelling differences, and whether the formula points to the intended column. A cell that looks like Paid may actually contain extra spaces or other characters.
A wildcard seems to match literally: In COUNTIF criteria, * and ? have special meanings. A tilde escapes a wildcard when you need it to be treated as a literal character; for example, "~*" represents a literal asterisk.
The formula reports a parse error: Confirm that the quotation marks and parentheses are paired. Depending on the spreadsheet locale, the argument separator may be a semicolon rather than a comma.
The count changes unexpectedly: Check whether new entries fall inside the referenced range. A fixed range such as A2:A100 will not include data entered below row 100.

For a quick audit, filter the data column or use Google Sheets’ Find feature to inspect examples of the text you are counting. Compare several matching and nonmatching cells with the criterion. Also consider whether blanks, formulas returning an empty string, or imported values with extra spaces need separate treatment; those details can affect what a broad or exact search includes.

Choosing between COUNTIF and other methods

Use COUNTIF for one condition, such as an exact status or a substring. Use COUNTIFS when several conditions must all be true, such as a status and a region. Use a case-sensitive or regular-expression method only when the matching rule requires it. This choice keeps formulas understandable for anyone who later needs to review the sheet.

Excel also has COUNTIF and COUNTIFS, and their basic use is very similar, but this page’s examples and directions are for Google Sheets in a web browser. Menu paths are not needed for these formula-based counts. In either program, a formula result is a live count rather than a one-time tally: editing a matching cell can change the answer automatically.

If the sheet contains a very large data set or many different search categories, consider whether the data should be normalized. For example, keeping a single status in each cell is easier to count reliably than combining status, date, and comments in one text field. Formula design cannot fully compensate for inconsistent or overloaded source data.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF in Google Sheets treats capitalization as equivalent, so Paid and PAID match the same text criterion. Use a case-sensitive method such as EXACT with SUMPRODUCT if capitalization must matter.

How do I count cells containing a word, not just matching it?
Put an asterisk before and after the word in the criterion, as in "*word*". That counts the word anywhere in a cell, including as part of a longer word, so check for unintended partial matches.

Can I use a cell as the search term?
Yes. Set the criterion to a cell reference, such as D1, for an exact match. For a contains search, join asterisks around the reference with ampersands.

How do I count cells that do not contain certain text?
COUNTIF supports the not-equal operator in its criterion. A criterion such as "<>Paid" counts cells that are not equal to Paid, but be aware that blank cells may also meet a not-equal condition. Decide whether blanks should be included before using the total.

Why does counting “paid” include “unpaid”?
A criterion with wildcards on both sides searches for those letters anywhere in a cell. Since unpaid contains paid, it qualifies. Use an exact match when you need the whole cell to equal the status.

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.