I'm having the following set of data:
enter image description here

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?

2 Answers 2


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.


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!

  • 1
    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
    Jan 9, 2013 at 22:42

Shorter answer



Join returns a single string.
Split returns a range having one cell for each "word".
Counta returns the number of "words".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.