Counting text splits into two very different jobs. You might want the number of cells that contain a word. Or you might want every occurrence, even when one cell holds the word three times.
Google Sheets uses a different formula for each. Neither is fun.
The Old Way (COUNTIF and SUMPRODUCT)
To count cells that contain a word, you use COUNTIF with wildcard asterisks.
=COUNTIF(A1:A10, "*bug*")To count total occurrences across cells, COUNTIF cannot help. You reach for this instead.
=SUMPRODUCT((LEN(A1:A10) - LEN(SUBSTITUTE(A1:A10, "bug", ""))) / LEN("bug"))It measures the text, deletes the word, measures again, then divides. Clever, but nobody remembers it. And SUBSTITUTE is case sensitive, so "Bug" slips through unless you wrap the range in LOWER(). Searching "cat" also counts "category" and "scatter". Every fix makes the formula longer.
The FITS Way (Say What You Mean)
With FITS, you write the question. The AI figures out the counting.
=FITS("Count how many cells contain the word bug in " & A1:A10)Need every occurrence instead, and case does not matter? Just say it.
=FITS("Count total occurrences of the word bug, ignoring case, in " & A1:A10)You control whole-word versus partial matching by describing it. No wildcards. No LEN math. Anyone reading the sheet knows exactly what you counted.
When to Use Each
COUNTIF is fast and free for simple partial matches. Reach for =FITS() when the counting rules get fuzzy or case matters. For more examples, see our AI formulas for content marketers guide, or learn to extract the domain from a URL the same easy way.
Stop Memorizing Formulas
FITS puts plain-English AI formulas inside Google Sheets. Ask your question. Get the number. Free tier included.