Counting Every Occurrence of a Word in Multiple Cells

One way to achieve this would be to create a new column that would count the number of occurrences of a word in a cell and sum up that column.

For instance if A1 is the word to search, column B is the source of your data and column C is the count of words for column B:

A1 = "blah"

B1 = "And he said blah blah blah"; C1 = COUNTMATCHES(B1, $A$1), thus C1 returns 3

B2 = "And he only said blah"; C2 = COUNTMATCHES(B2, $A$1), thus C2 returns 1

B3 = "And he said hello"; C3 = COUNTMATCHES(B3, $A$1), thus C3 returns 0

Then you use the SUM() function on column C to sum up all occurrences of this word which, in this example, would return 4.

Tried out this new (to me) function, with the intention of fitting the the calculations in 6x6's example into a single formula.

Successful, but that success also revealed a weakness in the function when chosen for this task.

Like many functions, COUNTMATCHES does not differentiate between a 'word' and 'a string of letters containing the ordered set of letters in that word.'

Doesn't negate the usefulness of COUNTMATCHES, but does remind us that there are issues that may result in an over-count, or, if the count target is redefined, an undercount, missing words that are in the sample, but are not preceded by characters added to the word to avoid doggerel while including the plural, and possessive forms.

Regards,

Barry

There are other Text function you can use to parse text but they are a little more involved. Please look at the REGEX and REGEX.EXTRACT; they are a lot more powerful but tricky to use as they require knowledge in the use of "regular expressions" (https://en.wikipedia.org/wiki/Regular_expression).

Haven't got to those yet. Still messing around with the X... functions. All in good time :-)

Thanks for the reminder!

Barry