Counting Numeric and Text Cells

Wendy has a worksheet where Column A contains some cells that are numeric and others that are text. Some are even blank. Wendy would like to get a count of the cells that contain text and a count of those that contain numbers.

This can be accomplished in a couple of different ways. To get a count of cells that contain text, the most reliable method is to use either of these formulas:

=COUNTIF(A2:A123,"*")
=SUMPRODUCT(--ISTEXT(A2:A123))

If you want the number of cells that contain numbers, then you can also choose from two formulas:

=COUNT(A2:A123)
=SUMPRODUCT(--ISNUMBER(A2:A123))

Both return the same result, but you may want to use the second formula for consistency if you use SUMPRODUCT to count the number of cells containing text.

There is one thing to remember when counting numeric cells—Excel considers dates and times to be numeric values. That is because dates and times are stored, internally, as a numeric serial number.

If you want to take the counts a step further, you may want to count the number of cells that contain blanks or the number that contain error values. These counts can be derived with these two formulas:

=COUNTBLANK(A2:A123)
=SUMPRODUCT(--ISERROR(A2:A123))

Astute readers will notice that none of these formulas utilize the COUNTA function. That is because the function isn't particularly fussy about what it returns—as long as the cell contains something, it is included in the returned count. Thus, subtracting the result of the COUNT function (cells with numbers) from the COUNTA function (cells containing anything) won't render the number of text cells because a cell could also contain an error value or a logical value of some type.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13969) applies to Microsoft Excel 2007, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.