# How to word count multiple rows in Google Sheets?

I have a spreadsheet similar to:

```            A          B          C
1   "text a1"  "text b1"  "text c1"
2   "text a2"  "text b2"  "text c2"
3   "text a3"  "text b3"  "text c3"
4   "text a4"  "text b4"  "text c4"
```

...and I would like to generate a word-count across all rows and columns. How can I do that?

I know a single cell can be counted this way: Count the number of words in a string in Google Spreadsheet, using the SPLIT() function. However, SPLIT() doesn't work when combined with an ArrayFormula() so it can't be used to aggregate multiple rows.

What can I do?

First one has to define "a word". I'll use the definition of a word as "a maximal substring of non-whitespace characters", consistent with the linked post.

One can count words in a range by using `regexreplace` twice:

1. Replace every word with a single non-whitespace character, such as "a".
2. Remove all whitespace characters.
3. Find the length of the resulting string

Here is the formula, in which A1:B4 can be replaced by any other range.

``````=sum(arrayformula(len(regexreplace(regexreplace(A1:B4,"\S+","a"),"\s",""))))
``````

This approach can be adjusted in a straightforward way to other definitions of word. For example, if a word is defined as a substring of consecutive characters [A-Za-z], then change the character classes accordingly: inner regexreplace deals with what's acceptable in a word, the outer regexreplace deals with the rest.

``````=sum(arrayformula(len(regexreplace(regexreplace(A1:B4,"[A-Za-z]+","a"),"[^A-Za-z]",""))))
``````

After experimenting a bit, this seems like a fine/sneaky approach:

1. Concatenate all columns together into a single string
2. Use REGEXPREPLACE() to replace all words into single characters
3. Use LEN to count how many characters are present to get a word count

So:

```=SUM(
ArrayFormula(
LEN(
REGEXREPLACE(A:A & if(isBlank(A:A),""," ") &
B:B & if(isBlank(B:B),""," ") &
C:C, "[^ ]+[ ]*","x")
)
)
)
```

Does anybody have another way of approaching this?

• This works okay for three columns; not as convenient if there are twenty (and the formula has to be rewritten based on the number of columns involved). – user79865 Dec 5 '15 at 4:44
• Good point @NormalHuman! – Jordan Dec 6 '15 at 21:49