Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Remove Duplicates In Excel Column Or Row?
  • How to create dynamic reference table name in Excel
  • How to do a t-Test in Excel?
  • How to Create Thermometer Chart in Excel
  • Excel Line Chart

References

  • How to use Excel ROW Function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to create dynamic named range with OFFSET in Excel
  • How to get last row in numeric data in Excel
  • Approximate match with multiple criteria in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation exists in list
  • Excel Data validation don’t exceed total
  • Excel Data validation allow uppercase only
  • Excel Data validation unique values only

How to count total words in a range in Excel

by

If you want to count the total words in a range of cells, you can do with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function.

Formula

=SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range," ",""))+1)

Note: The formula inside SUMPRODUCT will return 1 even if a cell is empty. If you need to guard against this problem, you can add another array to SUMPRODUCT as below. The double hyphen coerces the result to 1’s and 0’s. We use TRIM again to make sure we don’t count cells that have one or more spaces.

=SUMPRODUCT((LEN(TRIM(B3:B7))-LEN(SUBSTITUTE(B3:B7," ",""))+1),--(TRIM(B3:B7)<>""))

Explanation

In the example above, we are using:

=SUMPRODUCT(LEN(TRIM(B3:B7))-LEN(SUBSTITUTE(B3:B7," ",""))+1)

Here’s how the formula works:

For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of spaces + 1. We’re using TRIM to remove any extra spaces between words, or at the beginning or end of the text.

The result of all this calculation is a list of items, where there is one item per cell in the range, and each item a number based on the calculation above. In other words, we have a list of word counts, with one word count per cell.

SUMPRODUCT then sums this list and returns a total for all cells in the range.

 

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IF function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function

Date Time

  • Basic timesheet formula with breaks in Excel
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • How to show last updated date stamp in Excel
  • Calculate date overlap in days in Excel
  • Convert date to text in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Group times into unequal buckets in Excel
  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to create dynamic worksheet reference in Excel
  • How to calculate percent change in Excel
  • Lock Cells in a Worksheet Excel
  • Advanced Number Formats in Excel
  • Common Errors in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning