If you want to count words in Google Sheets using an array formula — whether it’s per row or across multiple cells — you’re in the right place. This tutorial shows you how to split text into words and count them accurately using formula-based solutions like MAP, LAMBDA, REGEXREPLACE, and SUBSTITUTE.

Normally, we combine SPLIT and COUNTA to count words in a cell. But this doesn’t scale well when used in an ArrayFormula across rows.

So let’s break it down and see how you can reliably count words across multiple rows in Google Sheets — using formulas that return clean, row-wise results.

Why SPLIT + COUNTA Doesn’t Work Well in Array Formulas

If you’ve tried this before, you may have used something like:

=COUNTA(SPLIT(A2, " "))

It works well for a single cell. But as soon as you try:

=ArrayFormula(COUNTA(IFERROR(SPLIT(A2:A, " "))))

…you’ll notice it returns a single total count, not one per row. That’s because COUNTA doesn’t return an array output in this context — it collapses everything into one result.

So how do we get it working for each row individually? Let’s walk through a few robust solutions.

Sample Dataset

Here’s some sample data you can enter in column A (starting from cell A2):

3 Array Formula Methods to Count Words in Google Sheets

1. Count Words Per Row (Using MAP + LAMBDA)

This is the cleanest solution and uses the new MAP and LAMBDA functions.

=MAP(A2:A, LAMBDA(r, IF(r="",,COUNTA(SPLIT(r, " ")))))

Explanation:

• SPLIT(r, " ") – splits each sentence into words.
• COUNTA(...) – counts the resulting words.
• IF(r="",,...) – avoids showing 0 for empty rows.
• MAP(...) – applies the logic row-by-row.

This is the cleanest and most readable solution to count words in Google Sheets, especially when working with array data. However, it may impact performance on larger datasets due to the use of MAP and LAMBDA.

2. Regex-Based Workaround

In this method, we first use TRIM to remove leading, trailing, and multiple consecutive spaces. Then we use REGEXREPLACE to remove all characters except spaces. This leaves behind only the spaces between words. Since the number of words is equal to the number of spaces plus one, we add 1 to get the final count.

=ArrayFormula(IF(LEN(A2:A), LEN(REGEXREPLACE(TRIM(A2:A), "[^\s]", "")) + 1, ))

Explanation:

• TRIM(A2:A) – removes leading and trailing spaces, and collapses multiple spaces between words into a single space
• REGEXREPLACE(..., "[^\s]", "") – removes all characters except for space characters
• LEN(...) – counts the number of spaces left in each row
• + 1 – accounts for the last word (word count = number of spaces + 1)
• IF(LEN(A2:A), ..., ) – skips completely blank rows by returning nothing

This method offers better performance compared to the LAMBDA solution, especially on larger datasets — but it’s not as clean or intuitive to read and maintain.

3. Count Words by Measuring Space Difference (SUBSTITUTE Approach)

This method counts the number of spaces in a sentence by comparing the length of the text before and after removing spaces. Since the number of spaces is one less than the number of words (assuming proper spacing), we add 1 to get the word count.

We also use TRIM to clean up the input — it removes leading/trailing spaces and collapses multiple spaces into a single space between words.

=ArrayFormula(IF(LEN(A2:A), LEN(TRIM(A2:A)) - LEN(SUBSTITUTE(TRIM(A2:A), " ", "")) + 1, ))

Explanation:

• TRIM(A2:A) – removes leading/trailing spaces and reduces multiple spaces between words to a single space
• LEN(TRIM(A2:A)) – gives the character count of the cleaned text (with spaces)
• SUBSTITUTE(TRIM(A2:A), " ", "") – removes all remaining single spaces from the cleaned text
• Subtracting lengths – calculates the number of spaces in each row
• + 1 – accounts for the last word (words = spaces + 1)
• IF(LEN(A2:A), ..., ) – ensures blank rows return nothing instead of 1

This is the fastest method among the three, making it ideal when working with large datasets and where precise formatting has already been enforced or cleaned with TRIM.

Which Word Count Formula Should You Use in Google Sheets?

Related Resources

• How to Count Comma-Separated Words in a Cell in Google Sheets
• Count Words and Insert Blank Rows in Google Sheets
• Excel Word Count: Beyond SUBSTITUTE
• Split Numbers from Text Without Delimiters in Google Sheets