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.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
When working with names or a different series of words, you may need to pull the initial letters from each word in the ...
Discover MoreWhen using an Excel worksheet to store data (such as names and phone numbers), you may need a way to easily look up a ...
Discover MoreIt's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...
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