How to count the total words in a range
✓ Verified in LibreOffice 25.8.7.3Word-count across many cells at once — content sheets, survey answers, translations.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1) | Assumes no fully blank cells in the range — each blank inflates the count by 1 (see below). |
| Google Sheets | =SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1) | Identical. |
| LibreOffice Calc | =SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1) | Identical. |
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. Every formula here is confirmed by actually executing it.