Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 6 down vote accepted

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!

share|improve this answer
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

Shorter answer



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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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