Excel counts word occurrences two different ways depending on where the word lives: COUNTIF(range, "*word*") returns the number of cells that contain the word anywhere inside them, while SUMPRODUCT(--ISNUMBER(SEARCH("word", range))) returns the number of cells that contain the word as a substring. Neither formula by itself reports how many times the word appears across the entire dataset when individual cells may contain the word more than once — for that you need a longer SUBSTITUTE-based formula, or you copy the cells out of Excel into a literal matcher that returns both the total count and the zero-based starting positions of every match. This guide walks through the formula path that stays inside Excel and the browser-based path that gives exact positions, then explains the overlap and case-sensitivity controls that quietly change the answer in ways most readers never expect.
For datasets that mix free-form text across many cells, counting word occurrences inside Excel needs more than a single-cell formula. The wildcards in COUNTIF tell you how many rows contain the word; SUMPRODUCT tells you how many rows contain at least one occurrence. Neither tells you that "the" appears 14 times in row 4 and 3 times in row 7 unless you add a per-cell SUBSTITUTE formula or move the text out of Excel entirely.
Why COUNTIF Alone Misses Word Occurrences Inside a Cell
Excel's three counting functions look interchangeable until you notice the difference between counting cells and counting words. COUNT counts numbers only. COUNTA counts non-blank cells of any type. COUNTIF counts cells that match a criterion, and by default that criterion applies to the entire cell value. If cell A1 contains the sentence "the quick brown fox jumps over the lazy dog" and you write =COUNTIF(A1:A1, "the"), Excel returns 0, because the cell value as a whole is not equal to the string "the". To force COUNTIF to count words inside text, you add wildcards: =COUNTIF(A1:A1, "*the*") returns 1 because exactly one cell in the range contains "the" surrounded by anything. That is one cell matching, not two word occurrences. For larger ranges, =COUNTIF(A1:A100, "*the*") reports how many of the 100 cells contain "the" anywhere — still a cell count, not a word count.
If you actually need the second number — the total number of times a specific word appears across the data, including multiple times inside a single cell — COUNTIF cannot give it to you directly. You either build a more advanced formula or move the text out of Excel.
Counting Words Inside Cells With SUMPRODUCT and SEARCH
The standard Excel formula for counting how many cells contain a specific word as a substring uses SUMPRODUCT together with ISNUMBER and SEARCH. The shape is:
=SUMPRODUCT(--ISNUMBER(SEARCH("the", A1:A100)))
Each SEARCH call returns the position of "the" inside each cell, or an error when the word is not found. ISNUMBER converts that into TRUE or FALSE. The double-unary (--) turns the booleans into 1s and 0s, and SUMPRODUCT sums them, giving you the total count of cells that contain the word at least once. SEARCH is case-insensitive by default; swap it for FIND when you want a case-sensitive match.
If a cell contains the word more than once and you want every occurrence counted, the formula grows. A common shape is:
=(LEN(A1) - LEN(SUBSTITUTE(A1, "the", ""))) / LEN("the")
This computes how many characters disappear when every "the" is removed, divides by the length of the search word, and gives the number of occurrences in that single cell. Wrap it in SUMPRODUCT to apply across a range. SUBSTITUTE is case-sensitive by default; wrap A1 in LOWER, or use SUBSTITUTE(LOWER(A1), "the", "") and compare to a lowered search word, to match case-insensitively.
For ranges where the search word is held in a cell (say B1) instead of typed into the formula, replace the literal with B1 and lock the reference: =SUMPRODUCT((LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100, B1, ""))) / LEN(B1)). This makes the formula reusable for different search words without rewriting it.
Counting Word Occurrences Across Pasted Excel Cells
For ranges where the words live in different cells, or where you simply want to copy a column of text out of Excel and count word occurrences without rebuilding a formula, a browser-based literal matcher does the work locally. The Count Occurrences tool accepts up to one million characters of source text, looks for an exact literal string, and reports the total plus the first 100 zero-based starting positions. Because the search is literal, periods, asterisks, brackets, and emoji need no escaping — an asterisk in your search string matches an asterisk in the text.
- In Excel, select the cells you want to count and press Ctrl+C to copy. If the words live in one column, copy only that column; if they live across a range, copy the whole range.
- Open Count Occurrences in your browser and paste the copied cells into the source field. Line breaks between rows are preserved, so each Excel row stays on its own line.
- Type the exact word you want to count into the search field. Do not add wildcards or asterisks — the search is literal, so a literal asterisk would only match a literal asterisk.
- Choose case-sensitive matching if capitalization matters; otherwise leave it case-insensitive so "The" and "the" are treated the same.
- Choose overlap mode if you want every position the word could start at — useful for sequence analysis — or non-overlap mode if you want the number of separate matches a simple find-and-replace operation would make.
- Read the total count, then scroll through the first 100 zero-based positions to see exactly where each match begins.
All processing happens inside the browser. Neither the pasted source text nor the search word is uploaded, and editing any input or option clears the old output so stale counts cannot be mistaken for current settings.
Overlap vs. Non-Overlap and Why the Same Search Gives Different Numbers
The overlap toggle is the most common source of confusion for readers who compare two counts of the same input. With non-overlap mode, the search advances by the full length of the search string after each match, so finding "aa" in the source "aaaa" returns two matches starting at positions 0 and 2. With overlap mode, the search advances by one JavaScript string unit after each match, so the same input returns three matches starting at positions 0, 1, and 2. This is a single worked example of the two outcomes:
| Source text | Search | Mode | Total | Positions |
|---|---|---|---|---|
| aaaa | aa | Non-overlap | 2 | 0, 2 |
| aaaa | aa | Overlap | 3 | 0, 1, 2 |
For sequence analysis — counting repeated characters, looking for repeated tokens in log lines, or checking how many substrings can start inside a longer one — overlap mode is the right choice. For checking how many replacements a simple find-and-replace operation would make, non-overlap mode reflects that operation's behaviour. The two modes give the same answer only when the search string cannot overlap with itself, for example searching for "the" in "theatre": there is only one valid starting position no matter which mode is selected.
Case Sensitivity, Position Limits, and Other Practical Details
Several small details change the way totals and positions behave, and they matter once you start comparing results between tools or between Excel and the browser.
- Case-sensitive mode compares the entered strings character-for-character. Case-insensitive mode converts both the source text and the search input with English-locale lowercase rules before comparing, but reports offsets from the original text.
- Position numbers are zero-based JavaScript string offsets. Most ordinary English characters consume one offset. Characters that are represented by surrogate pairs — including many emoji — consume two UTF-16 code units, so the offsets of subsequent characters may shift by an extra unit compared to what a human counts as "the next character".
- The total count is always exact. Only the first 100 starting positions are listed, so very dense matches will show a truncated position list while the total still reflects every match in the input.
- An empty search string is rejected because it would match at every boundary and produce an ambiguous result. A search longer than the remaining source naturally returns zero.
- The tool does not trim source text, change line endings, collapse whitespace, or rewrite the input. Case folding is applied only inside the comparison copy.
These rules follow standard JavaScript string behaviour. The underlying lookup uses String.prototype.indexOf repeatedly until no further match is found, advancing by one unit for overlap mode and by the full search length otherwise.
Picking the Right Approach for the Job
The table below summarizes which approach fits which Excel counting scenario. For counts by word rather than a specified string, use Word Counter. For replacing matches rather than just counting them, use Find and Replace.
| Scenario | Recommended approach |
|---|---|
| Count cells that contain a specific word anywhere inside | =COUNTIF(range, "*word*") in Excel |
| Count cells that contain the word at least once as a substring | =SUMPRODUCT(--ISNUMBER(SEARCH("word", range))) |
| Count every occurrence inside each cell, including repeats | =SUMPRODUCT((LEN(range) - LEN(SUBSTITUTE(range, "word", ""))) / LEN("word")) |
| Count literal matches across pasted Excel text and inspect positions | Count Occurrences in the browser |
| Count words by token rather than by a specific string | Word Counter in the browser |
For readers who prefer to stay inside Excel for plain word counts rather than occurrences of a specific word, the 30-second word-count shortcut for Excel covers the related case. For counting occurrences of a number rather than a word inside Excel, the separate guide on counting number occurrences in Excel walks through the matching formula path.
For most readers searching for how to count occurrences of a word in Excel, the practical answer is to decide whether you need cell counts, substring counts, or total occurrence counts, and then pick the formula or tool that matches that specific need. The three give different numbers for the same dataset, and the difference matters once a stakeholder asks why row 4 shows 14 in one report and 1 in another.