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