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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel Data Analysis and Business Modeling today!
Sometimes it can be confusing to figure out the source of an error that is displayed in your worksheet. Excel provides a ...
Discover MorePostal codes in Canada consist of six characters, separated into two groups. This tip explains the format and then shows ...
Discover MoreGot a large group of people listed in a worksheet and you want to make sure that each person has met with every other ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-03-21 09:14:45
Alex Blakenburg
Note:
• From Excel 2021 on Sum would have worked instead of SumProduct
• IsText will include empty strings ie any formula results returning ""
• IsBlank will not include empty strings
2026-03-21 09:10:17
Alex Blakenburg
@Wim Excel - the double minus signs are normally referred to as the "double unary" operator. The ISTEXT or ISNUMBER with a range in the brackets returns an array of TRUEs and FALSEs.
For SumProduct to add them up they need to be converted to 1s and 0s respectively. Any arithmetic operation will achieve this conversion.
So these would also have worked ISTEXT(A2:A123)+0 or ISTEXT(A2:A123)*1. Minus Minus has a similar affect turning True into -1 then back to 1. Mostly people think it looks neater but I also once read that it is faster but don't quote me on that.
2026-03-21 05:27:16
Wim Excel
Thanks for this tip. What do the -- (double minus signs) do in the second version?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments