Count Cells with Text in Excel (Table of Contents)
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 character/string in it. Ideally, excel was built and developed to work with numbers only. Thus, you can have some of the formulae/methods to count the numbers. This article will look into some of the methods that will help us count cells with text/string values in them.
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?
Count cells with Text in Excel are 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:
All we want is to check the number of text values this entire 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 that there are 7 text values in 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 also have to 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 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 so that we can find out if the given cell value is a text or not. 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.
Here in this formula, the ISTEXT function checks if 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, 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, all we want is to find out how many times 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 COUNTIF function.
Now, use “Andy” as 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 are having 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 range as A2:A10 under 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 have to use SUMPRODUCT in combination with the EXACT function to get exactly the value you wanted. Start typing the SUMPRODUCT formula in excel and add a double minus sign, the same as we did in example 2.
Use EXACT function as an argument under SUMPRODUCT.
Add “ANDY” as criteria and set range as A2:A12 under EXACT function.
Then press Enter Key.
This is from this article. Let’s wrap the 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 actually 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 apostrophe (‘) will be considered as texts and also be 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 along with a few practical examples and a downloadable Excel Template. You can also go through our other suggested articles –
