# How to count numbers stored as text in Excel

Count cells that are text but turn into a number when converted: `=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))`.

**Excel:** `=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Invoice | Amount |  | Text numbers |
| INV-01 | 1250 |  |  |
| INV-02 | 980 |  |  |
| INV-03 | 1430 |  |  |
| INV-04 | n/a |  |  |
| INV-05 | 715 |  |  |
| INV-06 | 1005 |  |  |
| INV-07 | 64.5 |  |  |
| INV-08 |  |  |  |
| INV-09 | 890 |  |  |
| INV-10 | 2210 |  |  |

Formula in E1: `=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))`

Result: 3

## 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.

## Other ways

- `=SUMPRODUCT(--ISTEXT(B2:B11))`: Counts every text cell. (Returns 4: it also counts "n/a".)
- `=COUNTA(B2:B11)-COUNT(B2:B11)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-numbers-stored-as-text