In Google Sheets, you can write a formula to count words from a cell or a range of cells. And in this tutorial, we will understand this in detail.
Count Words from a Cell
If you want to count words from a single cell, you can use a formula combining COUNTA and SPLIT functions.
And the formula is:
=COUNTA(SPLIT(A1," "))- SPLIT functions split the text from the cell using the space in between.
- And then, COUNTA counts all those split strings to get the count of the words by counting the strings.
You can also combine it with the IF to only get the count of words from the cells with a value.
Get the Words Count for the Entire Range/Column/Row
If you want to get the count of words for a complete range, column, or row, in that can use you can use a formula where you can specify that entire range instead of a single cell.
This formula is the same, but we wrap it with the ARRAYFORMULA.
=ARRAYFORMULA(COUNTA(SPLIT(A1:A4," ")))This formula also uses COUNTA and SPLIT functions, but as we are referring to a range of cells, we need to use the ARRAYFORMULA function that can take an entire range/column/row to calculate the count of words in it.
Get a Count for Specific Word
Now, let’s say you want to get a count for a specific word; in that case, you need to use COUNTIF instead of COUNTA.
=ARRAYFORMULA(COUNTIF(SPLIT(A1:A4," "),"is"))Here we have used SPLIT to split the values from the range of single strings, and then we have used COUNTIF only to count the word “is” from all the strings.
In the end, we wrapped the formula with ARRAYFORMULA to convert this formula into an array formula.
Get the Word Count without a Specific Word
You must write the same formula differently if you want to count all words without a specific word. And the formula will be:
=ARRAYFORMULA(COUNTIFS(SPLIT(A1:A4," "),"<>is",SPLIT(A1:A4," "),"<>"))
In this formula, we have used COUNTIFS instead of COUNTIF. And in this formula, COUNTIFS counts two criteria:
- Count texts which are equal to “is”.
- And then counting the cells does not equal a blank “<>”.
In both criteria, we have used split to split a string into single values, and after, COUNTIFS uses the criteria we have discussed above. And we have used the ARRAYFORMULA to make this formula work as an array formula.