# Count Occurrences of Words

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?

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!

• You make it sound easy. I've googled around, tried myself, but this is the first working example I've seen. Great work. – Jacob Jan Tuinstra Jan 9 '13 at 22:42

``````=Counta(Split(Join(",",A1:A4),","))
`Join` returns a single string.
`Split` returns a range having one cell for each "word".
`Counta` returns the number of "words".