Excel spreadsheet is known to be one of the best data storing and analyzing tools. Cells together make spreadsheets that consist of text and numbers. For more understanding, you have to differentiate cells filled with text.
So, practically how would you count cells with text in Excel? You can use a few different formulas to count cells containing any text, empty cells, and cells with specific characters or just filtered cells. All these formulas can be used in Excel 2019, 2016, 2013, and 2010.
At first, Excel spreadsheets were limited to dealing with numbers, however now we can use these sheets to store as well as modify text. Do you really want to know how many cells are filled with text in your data sheet?
For this purpose, you can use multiple functions in Excel. Depending on the situation, you can use any function.
Here you will find the tricks to count text in Excel. Let’s have a look at how you can count if a cell contains text in different conditions.
Before moving forward to learn how Excel counts cells with text, why not have a quick overview of what a Count If the function is.
COUNTIF Function
To be honest, counting cells containing text in a spreadsheet is not an easy task. That’s why the Count if the function is there to help you in this case. Using asterisk wildcards, you can count the number of cells having specific text with COUNTIF function.
The basic purpose of asterisk wildcards is to correspond to any characters or numbers. Putting them before and after any text, you can simply count the number of cells in which the text is found.
Here is the general formula for the COUNTIF function:
=COUNTIF (Range, “*Text*”)
Example:
=COUNTIF (A2:A11, “*Class*”)
The last name “Class” when put in the asterisk, the COUNTIF formula simply calculates all string values within the range containing the name “Class.”
How to Count Number of Cells with Text in Excel
You can count if cell contains any text string or character by using two basic formulas.
Using COUNTIF Formula to Count All Cells with Text
The COUNTIF formula with an asterisk in the criteria argument is the main solution. For instance:
COUNTIF (range, “*”) (as mentioned above)
For more understanding of this formula, let’s have a look at which values are included and which are not:
Counted Values
- Special characters
- Cells with all kinds of text
- Numbers formatted as text
- Visually blank cells that have an empty string (“”), apostrophe (‘), space, or non-printing characters
Non-Counted Values
- Numbers
- Errors
- Dates
- Blank cells
- Logical values of TRUE or FALSE
For instance, in the range A2:A10, you can count if the cell contains text, apart from dates, numbers, logical values, blank cells, and errors.
Try using one of the following formulas:
COUNTIF(A2:A10, “*”)
SUMPRODUCT(–ISTEXT(A2:A10))
SUMPRODUCT( ISTEXT(A2:A10)*1)
Count If Cell Contains Text without Spaces and Empty Strings
Using the above-mentioned formulas can help you count all cells containing any text or character in them. However, in some situations, it might be tiring and confusing as certain cells may look blank, but in reality, they are not.
Keep in mind that empty strings, spaces, apostrophes, line breaks, etc are not easily countable with the human eye. However, they are not difficult to count using formulas.
Using the COUNTIFS function will let you exclude “false positive” blank cells from the count. For instance, in the range A2:A7, you can count cells with text that overlooks the space character:
COUNTIFS(A2:A7l”*”A2:A7,”<>”)
If your target range has any formula-driven data, it might be possible that some of the formulas can ultimately be an empty string (“”). You can even ignore these empty strings by replacing “*” with “*?*” in the criteria 1 argument:
=COUNTIFS(A2:A9,”*?*”, A2:A9, “<>”)
Here the question mark shows that you need to have at least one text character. You know that an empty string does not have characters in it, and it does not meet that’s why it is not included. Also, remember that blank cells starting with an apostrophe (‘) are not included as well.
Below in the image, you can see a space in A7 cell, an apostrophe in A8, and an empty string (=””) in A9.
How to Count IF Cells Contain Specific Text
As you already know that COUNTIF function is best for counting all cells containing text, you can even COUNTIF cell contains specific text. Suppose, we need to count the number of times the word used “Excel” in a specific cell range:
- Open the spreadsheet in Excel you want to check.
- Click on a blank cell to enter the formula.
- In the blank cell, you need to type “=COUNTIF (range, criteria)”.
Using this formula will count all the number of cells having specific text within a cell range.
- You have to enter the cell range you need to count for the “range”. Now, add the first and last cells separated with a colon. Enter “A2: A20” to count cells.
- Now type “Excel” for the “Criteria.” It will help you count the number of cells using “Excel” in a certain range. Your formula will look like “=COUNTIF (A2:A20, “Excel”).
How To Count Cells with Color Text in Excel
Do you know Excel does not provide a formula to count cells with colored text?
However, still you cannot do this manually as counting all colored cells one by one is not an easy thing. By filtering the results, you can execute this process. Follow the steps given below:
- Go to the spreadsheet you want to analyze.
- Right-click a cell with the text of the color you want to count.
- Select “Filter,” and then “Filter by Selected Cell’s Font Color” to filter the cells with the selected text color.
- Get ready for counting the data range. Here is the formula if your text is from cell B2 to B20: “=SUBTOTAL (B2:B20)”
After pressing the “Enter” key, your filter will be applied and Excel will start showing the cells having that specific color and the remaining value–s will be hidden.
In the hidden rows, the “SUBTOTAL” function will not add the values. That’s why it will only count the selected color text.
Summing Up Count If Cell Contains Text
For storing and analyzing the data, Excel is a huge platform that provides multiple functions for several problems. Both text and numbers are dealt in Excel. Around four hundred functions can use the COUNTIF function. It helps in finding the sum of cells containing specific data.
