Count Occurrences of Words

Question

I'm having the following set of data:

A simple COUNTA would yield n=4. Using the above-mentioned formula will give also n=4.

The answer needs to be however n=6: Jacob Jan (1x), Jacob (2x), Jan (3x)

How can I count the number of occurrences of each word in column A?

Answer 1

Hm, that's an interesting question. If you're looking for just the n of terms in the array, you can do this without splitting the terms.

=SUM(ARRAYFORMULA(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4, ",", "")) + 1))

Alright, here's what's happening in this formula.

• In A1, we take the len() of the string and subtract the len() of the string after substituting the comma. In this case we get 9 - 9 = 0. We then add 1 to get 1 (the number of terms in the cell)
• We can use arrayformula() on this to apply the same subtraction function to every cell in the given range (in this case A1:A4)
• The final piece is to sum() the numbers from the resulting range and get the total # of terms that are split on the comma.

Let's look at cell A2 to see a case of when the comma is used. We take len(A2) which is 10 and subtract len(A2) after substituting the comma for a null character "" which is 9. We get 10 - 9 = 1 then we add 1 to the total to get 2 which is equal to the number of terms in the cell.

Hope this helps!