Updated June 9, 2023
Introduction to Count Cells with Text in Excel
In our day to day life, you must have faced some situations where you want to count out the cells which are having text values or characters/strings in them. Ideally, excel was built and developed to work with numbers only. Thus, you can have some formulae/methods to count the numbers. This article will look into some methods to help us count cells with text/string values.
You will be going through five methods for counting cells with text values. All of them are listed below:
- COUNTIF function + Wildcard character to count cells with text in Excel.
- SUMPRODUCT + ISTEXT functions to count cells with text in Excel.
- COUNTIF function to count cells with specific string/text in Excel.
- COUNTIF function to count cells with partial text values in Excel.
- SUMPRODUCT and EXACT function to count case sensitive text values (Values such as all CAPS).
We will go through each method one by one and step by step.
How to Count Cells with Text in Excel?
Counting cells with Text in Excel is very simple and easy. Let’s understand how to use the Count Cells with Text in Excel with some examples.
Example #1 – Count Cells in Excel using COUNTIF Function and the Wildcard Character
Suppose we have a list of employees and some salary information as below:
We only want to check the number of text values this list contains. We can use a combination of COUNTIF and a wildcard letter (asterisk or *) to find the number of text values this list contains.
In cell B2, start typing the formula for the Excel COUNTIF function.
As the requirement of the COUNTIF function, set range as an array from A2:A12 as a first argument.
Use an asterisk (*) as a second argument which is the criteria you give to the COUNTIF function. Don’t forget to enclose it in double quotes.
Then press Enter Key.
You can see in cell C2; the number 7 denotes 7 text values in the range A2:A12. The key argument here is a wildcard asterisk. It allows the COUNTIF function to count any number of characters except numbers or logical values. You must also note that those will be counted in this formula if numbers are stored as texts.
Example #2 – Count Cells with Text using SUMPRODUCT and ISTEXT Function
SUMPRODUCT, with the help of ISTEXT, also allows you to count the cells having text values in them. In cell B2, start typing the SUMPRODUCT formula.
Now use the minus sign two times under the SUMPRODUCT formula. Don’t worry; I will break this logic for you.
Start typing the ISTEXT formula under this SUMPRODUCT formula to determine whether the given cell value is a text. If the given cell value is a text, it will be mentioned as TRUE. Else, FALSE. That means ISTEXT is a function that returns an array of logical values for a given range of cells.
Use array A2: A12 as an input argument to the ISTEXT function.
Then press Enter Key.
In this formula, the ISTEXT function checks whether each value from array A2:A12 is text or not. If text – TRUE. If not – FALSE. Therefore, we get an array of TRUE and FALSE for each cell. The double minus sign helps the system to convert TRUE and FALSE values into 1’s and 0’s (numeric conversions of TRUE and FALSE, respectively) so that the SUMPRODUCT function can sum them up and give a final count.
Example #3 – COUNTIF Function to Count Specific Text/String
Suppose we have data as the screenshot shown below:
Now, we only want to find out how often the name “Andy” is occurring on this list. Start typing the COUNTIF formula in cell B2.
As the first argument to this formula, put A2:A13 as a range in the COUNTIF function.
Now, use “Andy” as the criteria under the COUNTIF function. It allows a function to count the number of times “Andy” has occurred in the range of cells.
Then press Enter Key.
This is how you can find the count of specific text occurring in the given range of cells.
Example #4 – COUNTIF Function to Count Partial Text
Suppose we have data as shown in the below screenshot.
All we want is the count of Employee ID starting with “DAR”. Start typing the COUNTIF formula in cell B2.
Set the range as A2:A10 under the COUNTIF function.
Under criteria, use “DAR*”. It allows COUNTIF to count all the cells having DAR and anything in front of it.
Then press Enter Key.
Example #5 – Count Case Sensitive Text using SUMPRODUCT and EXACT Function
Suppose you have data as shown in the below screenshot.
You want to find the count of “ANDY” (word Andy with all caps). This is a different scenario. Therefore, you must use SUMPRODUCT with the EXACT function to get your desired value. Start typing the SUMPRODUCT formula in Excel and add a double minus sign, as in example 2.
Use the EXACT function as an argument under SUMPRODUCT.
Add “ANDY” as the criteria and set the range as A2:A12 under the EXACT function.
Then press Enter Key.
This is from this article. Let’s wrap things up with some points to remember.
Things to Remember About Count Cells with Text in Excel
- Logical Values TRUE & FALSE are not counted as text values. Because in computer language, logical TRUE = 1 and logical FALSE = 0, which are numeric values.
- Blank cells will be treated as text values.
- Numbers are not counted using an asterisk (“*”) unless those are stored as text values.
- Blank cells starting with an apostrophe (‘) will be considered texts and counted.
Recommended Articles
This is a guide to Count Cells with Text in Excel. Here we have discussed How to Count Cells with Text in Excel, a few practical examples, and a downloadable Excel Template. You can also go through our other suggested articles –
