How to Count Cells With Text in Google Sheets

How to Count Cells With Text in Google Sheets

Use COUNTIF with a single asterisk as the criterion. The asterisk is a wildcard matching any sequence of characters, so it counts every cell holding a text value.

Numbers, dates and truly empty cells are excluded, because the asterisk only matches text. That is usually what people mean by counting cells with text.

One edge case is worth knowing. A formula that returns an empty string still leaves a text value in the cell. If you want to count only cells with at least one visible character, use the question mark wildcard as well.

COUNTA vs COUNTIF: Why They Give You Different Numbers

COUNTA counts every cell that is not empty, whatever it holds. COUNTIF with an asterisk counts only cells holding text. On any range mixing words with numbers, the two disagree, and the gap is exactly the number of non-text values.

Here is the range used for every example on this page. Eight cells, A2 to A9.

Run both formulas against it and you get two different answers.

Neither result is wrong. They answer different questions. Use COUNTA when you want to know how much of the column is filled in. Use COUNTIF with an asterisk when a number sitting in a text column should not be counted as text.

Google Sheets Formula to Count Cells With Specific Text

Put the word in the criterion and wrap it in asterisks to match anywhere in the cell. Drop the asterisks and COUNTIF matches only cells whose entire contents are that word.

The contains version returns 4 because it matches bug report, Bug, debugging tools and bug. The exact version returns 2, matching only Bug and bug, since COUNTIF ignores case. Those two asterisks are the entire difference between the two answers.

To count cells that only start with the word, put the asterisk on one side.

The Three COUNTIF Wildcards

COUNTIF supports exactly three wildcard characters. Knowing the third one saves you when your data contains the first two.

That last one reads badly and matters a lot. To count cells containing a literal asterisk, you escape it with a tilde and still need the surrounding wildcards. Without the tilde you are asking for cells containing anything, which is every text cell in the range.

Is COUNTIF Case Sensitive in Google Sheets?

No. COUNTIF ignores case entirely, so bug, Bug and BUG all satisfy the same criterion. There is no argument or flag that changes this. If case matters, you have to leave COUNTIF behind.

For a case sensitive exact match, use EXACT inside SUMPRODUCT.

It returns 1, not 2, because A3 holds Bug with a capital B. For a case sensitive contains match, use FIND rather than SEARCH. FIND respects case and SEARCH does not.

The double minus converts TRUE and FALSE into 1 and 0 so SUMPRODUCT can add them up. ISNUMBER is there because FIND returns an error when the text is absent, and an error would break the sum.

Why Your COUNTIF Count Is Too High

Because wildcards match substrings, not words. The criterion for bug also matches debugging, bugfix, bugs and debug. In the sample range that is why the contains count is 4 while only 3 cells actually use bug as a word.

COUNTIF has no whole word option. REGEXMATCH does, through word boundary markers.

Two pieces are doing real work. The prefix in parentheses makes the match case insensitive. Appending an empty string to the range converts numbers and dates to text, because REGEXMATCH throws an error on a non-text value and one error kills the whole formula.

This drops debugging tools and keeps bug report, Bug and bug. If your counts have always looked slightly inflated, substring matching is almost certainly the reason.

Count Cells With Text Matching Two Conditions

COUNTIFS takes range and criterion pairs and counts rows satisfying all of them. Every range must be the same size, or you get an error rather than a wrong number.

That counts rows where column A mentions bug and column B says Open. The same wildcards work in every criterion. To count rows matching either condition instead of both, add two COUNTIF results together and subtract a COUNTIFS for the overlap.

Count Every Occurrence, Not Every Cell

COUNTIF counts a cell once no matter how many times the word appears inside it. If one cell says bug three times, COUNTIF still adds 1. For a true occurrence count you need length arithmetic.

It measures the text, deletes every instance of the word, measures again, then divides by the word length. LOWER is there because SUBSTITUTE is case sensitive, unlike COUNTIF. Without it, Bug slips through uncounted.

This formula still counts substrings, so debugging contributes an occurrence. It is the least readable formula on this page, and it is also the only native way to get the number.

Which Formula Should You Use

Every one of these is free and native. Reach for them first. The rest of this page is for the counts they cannot express.

When Your Count Is Wrong: Three Things to Check

Most bad counts trace back to one of these, and none of them raise an error.

1. Numbers stored as text. A value pasted from a CSV can look like 42 and still be a text string. COUNTIF with an asterisk counts it, COUNTA counts it, and your text count runs high. Check with ISTEXT before trusting the number.
2. Formulas returning an empty string. These cells look blank but hold a text value. COUNTIF with an asterisk counts them. Switch the criterion to a question mark followed by an asterisk to require at least one character.
3. Trailing spaces. An exact match criterion fails against a value with a stray space, so the count comes back low for no visible reason. Run the column through TRIM to remove extra spaces in Google Sheets before counting.

One more, if your sheet has slowed down. COUNTIF over a whole column reference like A:A scans every row, including the empty ones. Bound the range to the rows you actually use.

When Counting Rules Are Not Rules at All

Everything above assumes you can state the rule as a pattern. Sometimes you cannot. Counting how many support tickets mention a delivery problem is not a string match, because customers write late, never arrived, still waiting and where is my order. No wildcard covers that set, and listing every phrasing is a losing game.

That is the case for FITS, where you describe the count instead of encoding it.

Fill that down a column, then count the results with the native formula you already know.

Be honest about the trade. This runs an AI call per cell, so it is slower than COUNTIF and it is not free at volume. It is also not deterministic, which means two runs can disagree on a borderline ticket in a way REGEXMATCH never would. Do not use it to count the word bug. Use it when the thing you are counting is a meaning rather than a string.

Count Occurrences of a Word Row by Row

A single total is useful for a report. More often you want a count next to every row, so you can sort or filter by it. Drag this down column B.

Two details matter here. Ask for only a number, or you get a sentence that breaks your SUM. And say whether plurals count, because "bugs" is a judgment call the AI will make either way.

Now =SUM(B2:B) gives the column total, and sorting by column B puts the worst rows on top. If the next step is editing those rows, see bulk replacing text in Google Sheets. If you would rather the zero-count rows disappeared from view, see how to hide rows in Google Sheets, including why SUM keeps counting them.

Frequently Asked Questions

How do I count cells with text in Google Sheets?

Use =COUNTIF(A2:A9, "*"). The asterisk is a wildcard matching any sequence of characters, so it counts every cell holding a text value. Numbers, dates and blank cells are not counted. If you want every non-empty cell regardless of type, use =COUNTA(A2:A9) instead.

What is the difference between COUNTA and COUNTIF in Google Sheets?

COUNTA counts every cell that is not empty, including numbers, dates, booleans and errors. COUNTIF with the asterisk criterion counts only cells holding text. On the sample range above, COUNTA returns 7 and COUNTIF returns 5. COUNTA also counts a formula returning an empty string, which is the most common reason the two disagree.

What is the Google Sheets formula to count cells with specific text?

Use =COUNTIF(A2:A9, "*bug*") to count cells containing the word anywhere. Use =COUNTIF(A2:A9, "bug") to count cells whose entire contents are that word. Use =COUNTIF(A2:A9, "bug*") for cells starting with it. The asterisks separate a contains match from an exact match.

Is COUNTIF case sensitive in Google Sheets?

No. COUNTIF ignores case, so bug, Bug and BUG all match the same criterion. For a case sensitive exact match use =SUMPRODUCT(--EXACT(A2:A9, "bug")). For a case sensitive contains match use FIND rather than SEARCH, because FIND respects case and SEARCH does not.

Why does my COUNTIF count too many cells?

Because wildcards match substrings, not whole words. The criterion for bug also matches debugging, bugfix and bugs. COUNTIF has no whole word option. Use REGEXMATCH with word boundary markers inside SUMPRODUCT instead, and append an empty string to the range so non-text cells do not throw an error.

How do I count how many times a word appears inside a cell?

COUNTIF cannot, because it counts cells rather than occurrences. Use the SUMPRODUCT formula with LEN and SUBSTITUTE shown above. It measures the text, removes the word, measures again, then divides by the word length. Wrap the range in LOWER to make it case insensitive, since SUBSTITUTE is case sensitive by default.

Related Reading

COUNTIF is fast, free and correct for the majority of these jobs. Reach for =FITS() only when the counting rule is a meaning rather than a pattern. For more examples, see our AI formulas for content marketers guide, or learn to extract the domain from a URL the same easy way. If you want the topics rather than a raw count, see how to do keyword extraction in Google Sheets. Counting is one entry on a long list. Read the full guide to automating Google Sheets tasks you used to need regex for. A count is often just the input to an ordering, so see how to rank and sort automatically once you have the numbers. Counts also come out wrong when one thing is spelled three ways, which is what grouping similar text fixes before you count. Counting emojis is its own trap, because they are stored as surrogate pairs and joined sequences, which is why counting emojis in Google Sheets needs a different approach entirely.