## Tips & Tricks 116 - Count the Number of Words in a Cell / Range

Suppose you have been given the following and you need to count the number of words in a cell or in a range.

**Formula for calculating number of words in a cell -**

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

**Formula for calculating number of words in a range -**

=SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))+(TRIM(A1:A100)<>""))