Text
Count occurrences of a specific word in a cell or range (whole-word safe)
Count case-insensitive space-delimited tokens or non-overlapping literal substrings in a small text range.
Count occurrences rather than matching cells
COUNTIF counts matching cells, not every repetition inside them. A length-difference calculation can count repetitions by removing the target and dividing the number of removed characters by its length.
For the token version, doubling ordinary spaces gives adjacent tokens separate boundaries. This is a space-delimited token matcher, not a natural-language word parser: error, and error. do not match error. Normalize punctuation, tabs and line breaks explicitly if your data requires that behavior.
Use a nonempty single token for the first formula. The substring version counts non-overlapping occurrences after joining cells with a space; a target containing spaces can match across a cell boundary. TEXTJOIN also has spreadsheet text-length limits, so this is not an unlimited log-analysis pipeline.
| Input Text | Target Keyword | Substring Match (Unsafe) | Whole-Word Count (Exact!) |
|---|---|---|---|
| "cat cat catalog cat" | "cat" | 4 (Counts "catalog"!) | 3 (Skips "catalog", catches adjacent "cat cat"!) |
| Range of 3 support notes | "error" | COUNTIF = 2 cells | 4 total whole-word occurrences |
Whole-word keyword count across an entire range (adjacent-repeat safe)
Matches case-insensitively using ordinary-space boundaries and handles adjacent repetitions. Punctuation is retained.
=IF(OR(COUNTBLANK(C2)=1,LEN(TRIM(C2&""))=0),"Enter a keyword",LET(raw, LOWER(TRIM(TEXTJOIN(" ", TRUE, A2:A4))), s, " " & SUBSTITUTE(raw, " ", " ") & " ", w, " " & LOWER(TRIM(C2)) & " ", (LEN(s) - LEN(SUBSTITUTE(s, w, ""))) / LEN(w)))Substring occurrence count (when partial matches ARE desired)
Counts non-overlapping literal substring occurrences, ignoring case.
=IF(OR(COUNTBLANK(C2)=1,LEN(TRIM(C2&""))=0),"Enter a keyword",LET(raw, LOWER(TEXTJOIN(" ", TRUE, A2:A4)), k, LOWER(C2), (LEN(raw) - LEN(SUBSTITUTE(raw, k, ""))) / LEN(k)))Try the example
Use the original values or change them to test your understanding.