Whatever your reason, Excel has several functions that can help you count cells with text in your spreadsheet. In this article, we’ll show you how to use them.
Use COUNTIF With Wildcards
The easiest way to count cells with any text is to use the COUNTIF function with wildcards as search arguments for your formula. Using the asterisk (*) as a wildcard, you can match any character sequence and count any cells in your spreadsheet that include any text strings.
Here’s an example. If you want to count how many cells in the range B2 to B20 have text, you can use this formula: This will return the number 4 because, in this hypothetical scenario, four cells in the range have text values. You can also use COUNTIF with other wildcards. For instance, you could use the question mark (?), which matches any single character, or the tilde (~), which escapes a wildcard character.
Use COUNTA Minus COUNT
Another way to count cells with text is to use the COUNTA function minus the COUNT function. The COUNTA function counts all non-empty cells in a range, while the COUNT function counts only numeric values. By subtracting one from the other, you get the number of cells that contain non-numeric values, such as text or errors.
For example, if you want to count how many cells in the range B2 to B20 include text or errors, you can use this formula: This will return 6, because six non-empty cells in this hypothetical range aren’t numeric.
Use SUMPRODUCT with ISTEXT
Another way to count cells with text is to use a formula that uses the SUMPRODUCT function with the ISTEXT function. The ISTEXT function returns TRUE for cells that contain text and FALSE for others. The SUMPRODUCT function then adds these logical values as numbers (TRUE = 1 and FALSE = 0).
For example, if you want to count how many cells in the range B2 to B20 that only contain text (excluding errors), you can use this formula: This will return 6 because six cells in the range contain text only in this scenario. This allows you to use SUMPRODUCT with the nested ISTEXT function to identify the cells that contain text. If a numerical value was included in this cell range, SUMPRODUCT would ignore it.
Better Data Analysis in Excel
You can quickly count cells with text in your Excel spreadsheet using the steps above. You can choose whichever one suits your situation best, but they should all achieve the same effect. One interesting thing you can do with text cells in Excel is to change the title case to make text upper or lower case. If you’re new to Excel, you might want to learn top Excel tips to supercharge your spreadsheets. You should also consider learning some Excel keyboard shortcuts to help speed up your productivity. Want to clean up your spreadsheet? You can find and delete blank rows in Excel to tidy up your data. You can also search for duplicates in Excel, allowing you to remove any extra data causing errors in your calculations. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
