How to word count multiple rows in Google Sheets?

Question

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?

Answer 1

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
4. Add up the lengths.

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]",""))))

Answer 2

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?