How to count numbers stored as text in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))Count cells that are text but turn into a number when converted: =SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11)).
- Result on the example
- 3 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
ISTEXT finds the cells that hold text. The double minus tries to turn every cell into a number: "980" becomes 980, but "n/a" becomes an error, which ISNUMBER rejects. Multiplying the two tests keeps cells that are text and convert cleanly: three amounts that SUM and COUNT would silently skip.
=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))Result3
B2:B1110 rows × 1 col: 1250, "980", 1430, "n/a", …A range: 10 cells in one column.
ISTEXT(B2:B11){10 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …ISTEXT: TRUE if the value is text.- value
B2:B1110 rows × 1 col: 1250, "980", 1430, "n/a", …
- value
-B2:B11{10 rows × 1 col} -1250, -980, -1430, #VALUE!, …Makes the value negative.
--B2:B11{10 rows × 1 col} 1250, 980, 1430, #VALUE!, …Makes the value negative.
ISNUMBER(--B2:B11){10 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
--B2:B11{10 rows × 1 col} 1250, 980, 1430, #VALUE!, …
- value
ISTEXT(B2:B11)*ISNUMBER(--B2:B11){10 rows × 1 col} 0, 1, 0, 0, …The * operator multiplies.
SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))3SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
ISTEXT(B2:B11)*ISNUMBER(--B2:B11){10 rows × 1 col} 0, 1, 0, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11)). We ran it in Google Sheets mode on the same data and it returns 3.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--ISTEXT(B2:B11)) | 4Different result | Counts every text cell. Returns 4: it also counts "n/a". |
| =COUNTA(B2:B11)-COUNT(B2:B11) | 4Different result | Non-empty cells minus real numbers: a quick check of how much in the column isn't a number. Returns 4: it also counts "n/a". |
Common mistakes
- SUM ignores text numbers without warning, so totals come out low. This count tells you whether a column is affected.
- A text number with a thousands separator or currency sign ("1,250" or "$980") converts only if it matches your regional settings.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count numbers stored as text
E1, count the amounts in B2:B11 that are numbers stored as text.Questions
How do I convert them to real numbers?
Select the column and use Data, Text to Columns, Finish, or the warning triangle's Convert to Number. In a formula, =VALUE(B2) or =--B2.
How do I add them up anyway?
Convert inside the sum: =SUMPRODUCT(IFERROR(--B2:B11,0)). In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.