Excel: How to Count Text vs. Numbers in a Range Correctly

How-to basics · Updated November 19, 2026

With values in A2:A20, count numeric cells with =COUNT(A2:A20) and count cells whose values are text with =SUMPRODUCT(--ISTEXT(A2:A20)). These counts classify what Excel actually stores, so a number entered as text is counted as text, not as a number.

Quick formulas for text and numeric values

For a straightforward count, use Excel’s built-in COUNT function for numbers and combine ISTEXT with SUMPRODUCT for text. With the data you want to inspect in A2:A20, enter the first formula in any empty cell to count numeric values:

=COUNT(A2:A20)

For the same range, this formula counts cells that contain text values:

=SUMPRODUCT(--ISTEXT(A2:A20))

SUMPRODUCT totals the TRUE results produced by ISTEXT. The double unary operator, written as two minus signs, converts TRUE to 1 and FALSE to 0 so the results can be added. These formulas can sit outside the data range; for example, put them in B2 and B3 if those cells are clear.

If you only need a quick text count, COUNTIF with a wildcard is another option. Assuming the values are in A2:A20, use:

=COUNTIF(A2:A20,"*")

The asterisk matches text strings. For a careful audit, prefer the ISTEXT formula: it makes the classification explicit and is easier to extend when you need to distinguish text from other kinds of cell contents.

What COUNT, ISTEXT, and COUNTA actually count

Excel does not decide whether a value is text by how it looks on screen. It classifies the stored value. COUNT counts numeric values in a range, including dates, times, and percentages, because Excel stores those as numbers. It does not count ordinary text, blank cells, or logical values such as TRUE and FALSE in a referenced range.

ISTEXT checks one value at a time and returns TRUE if that value is text. SUMPRODUCT makes it practical to total those TRUE results across a range without setting up a separate helper column. A text value can be a word, an ID such as 0045, or a numeric-looking entry that Excel has stored as text.

COUNTA is different: it counts cells that are not empty. It includes numbers, text, logical values, and errors. It is therefore useful for estimating how many cells have content, but it does not answer how many contain text or numbers. A cell containing a formula that displays an empty string can also complicate a COUNTA result; that cell is not necessarily treated like a truly empty cell.

A small example: why the counts differ

Suppose A2:A8 contains these entries: North, the number 12, the text value 0045, a genuinely blank cell, a date, TRUE, and 12 units. The date was entered as a date rather than as text. In this example, COUNT returns 2: it counts 12 and the date, since both are numeric values to Excel.

For this same A2:A8 range, SUMPRODUCT with ISTEXT returns 3. It counts North, 0045, and 12 units. It does not count the blank cell, the numeric 12, the date, or TRUE. COUNTA returns 6 because six of the seven cells have entries, even though those entries are a mixture of types.

This illustrates why the two category counts do not always add up to the number of nonempty cells. Logical values and errors are neither numbers counted by COUNT nor text counted by ISTEXT. If a range contains such values, they form additional categories. Also, formatted appearance is not a reliable guide: applying a date format to a number changes how Excel displays it, not its underlying numeric type.

Numbers stored as text are a common source of surprises

A value such as 123 can be stored as a number or as the text string “123.” The two may look identical in a cell, but COUNT includes the numeric value and excludes the text version. ISTEXT does the reverse. This commonly happens after importing a CSV or copying data from a website or another system. A leading apostrophe can also make an entry text; Excel does not display that apostrophe in the cell.

Leading zeros are a useful reason to keep digits as text. An ID such as 0045 may be a label, not a quantity, so converting it to a number could remove the zeros. Decide what the data means before changing its type. If the entries represent quantities for arithmetic, convert numeric text to numbers; if they are identifiers, keeping them as text is usually appropriate.

To inspect a suspicious cell, select it and look at the formula bar and the warning indicators Excel may display. You can also test individual cells with ISTEXT or ISNUMBER. When you do convert data, check a few results and preserve a copy of the original column, especially if leading zeros or other formatting carry meaning. Conversion behavior can depend on decimal and date conventions in the source data.

Blanks, formulas, errors, and other values

“Blank” can mean more than one thing in a spreadsheet. A cell that has never had content is genuinely empty. A formula can instead return an empty string, often by using a pair of quotation marks as its result. That cell looks blank, but it contains a formula. COUNT ignores both cases, while functions that test for text or nonempty content may treat the formula result differently.

Errors such as #N/A are not text values for ISTEXT and are not numbers for COUNT. TRUE and FALSE are logical values, not numeric values in a range reference for COUNT. If you need a complete breakdown, consider counting errors and logical values separately rather than assuming every non-number is text. This matters in quality checks: a low text count does not prove that every remaining cell contains a valid number.

COUNTBLANK can help identify empty-looking cells, but remember that it can count formulas returning an empty string as blank. If your goal is to count cells with a particular visible or calculated result, define whether formula-generated empty strings should qualify before choosing a function.

Choosing the right method for your task

  • Count real numeric values: use COUNT. It includes dates and times stored as Excel numbers, but not numeric text.
  • Count values stored as text: use SUMPRODUCT with ISTEXT for a direct type test across a range.
  • Make a quick count of text entries: COUNTIF with the asterisk wildcard can work, but verify behavior if formulas return empty strings or the range has unusual contents.
  • Count all cells that are not empty: use COUNTA, understanding that this includes text, numbers, logical values, and errors.
  • Count only genuinely numeric entries for calculations: COUNT is a useful starting point, but check whether numeric-looking text needs conversion first.

For a recurring report, keep the text and number counts in separate labeled cells rather than relying on one total. If you expect every populated entry to be either text or a number, compare those counts with the total and investigate any difference. That extra check can reveal errors, TRUE or FALSE values, or formulas returning empty strings.

A reliable checking workflow

  1. Select the exact range to audit and exclude headings unless you intend to count them as text.
  2. Use COUNT to establish the number of numeric values, then use SUMPRODUCT with ISTEXT to count text values.
  3. Compare those results with COUNTA if you also want the number of nonempty entries. A mismatch can be valid when the range contains errors, logical values, or formula-generated empty strings.
  4. Investigate numbers stored as text before converting them. Preserve leading zeros when the values are identifiers, and confirm imported decimal and date formats.
  5. After cleaning or replacing data, recalculate or check that calculation is set to Automatic if the displayed results do not update.

When your data grows, use a range that includes the added rows or place the data in an Excel table so formulas can be easier to maintain. Avoid selecting entire columns unnecessarily in large workbooks if performance is an issue. The central distinction remains the same: Excel’s count functions respond to stored value types, not to the visual style of the cell.

Frequently Asked Questions

How do I count only text cells in Excel?

Use SUMPRODUCT with ISTEXT on the range you want to check. It counts cells whose values are text, including numeric-looking entries stored as text. If your range contains formulas that return empty strings, decide whether those results should count as text for your purpose.

Does COUNT include dates and percentages?

Yes. Excel stores dates, times, and percentages as numeric values, so COUNT includes them when they are in the referenced range. Their display formats do not change their underlying type.

Why does COUNT ignore a number that looks numeric?

The entry may be stored as text, often because it was imported, pasted, or entered with a leading apostrophe. COUNT excludes it, while ISTEXT identifies it as text. Check the source and intended meaning before converting it.

What is the difference between COUNT and COUNTA?

COUNT counts numeric values in a range. COUNTA counts cells that are not empty, including text, numbers, logical values, and errors. Use COUNT for numeric entries and COUNTA when you want a broader count of cells with content.

How can I count cells that contain numbers stored as text?

First identify text values that look numeric, then decide whether they should remain text or become numbers. A blanket conversion can remove leading zeros from IDs and may interpret dates or decimals according to regional settings, so check the data before changing it.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.