How to count the total words in a range

Word-count across many cells at once — content sheets, survey answers, translations.

The formula

How it works

Per cell, words = spaces + 1: LEN minus LEN-without-spaces counts the spaces, TRIM guards against padding, and SUMPRODUCT totals it across the range — 2 + 1 + 3 = 6 here. The known flaw: an empty cell still contributes +1. The blank-safe version subtracts them: add -SUMPRODUCT(--(TRIM(A2:A4)="")) or filter blanks out first with the FILTER-based patterns. For one cell only, the simpler count-words-in-a-cell recipe covers it.

Verified, not just documented

We ran =SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 6 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 6 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

SUMPRODUCT · LEN · TRIM · SUBSTITUTE — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

• How to count the number of words in a cell
• How to count items in a comma-separated cell
• How to count occurrences of a substring in a cell
• How to extract the file name from a path or URL
• How to extract the last word from a cell

Related comparisons

• SUBSTITUTE vs REPLACE: by text or by position
• TRIM vs CLEAN: the import-cleanup pair (and what both miss)
• SUM vs SUMPRODUCT: when the extra power is worth it