This article will look into the different formulas you can use to count text in Excel. Keep reading this article to save time counting cell items in Excel the next time you use it!
How to Count Cells With Text in Excel?
Excel has functions to assist you in counting cell items. Although there are other functions such as COUNT, COUNTA, COUNTIFS, and COUNTBLANK, you need to use the COUNTIF function to count cells with text in Excel. Furthermore, if you think using functions is not your cup of tea, you can always use the Find tool to look for the number of times a text has been repeated on the spreadsheet. In this article, we will be looking at both the ways you can look for text and how you can customize your counter using formulas.
Find Tool
The find tool is the quickest way to find specific items in the cell. Along with locating the value, Excel also provides you with the number of cells in which the value exists. You can use this tool if you’re looking for a fast and convenient way of counting specific values. Here are the steps you can follow to use the Find tool in MS Excel:Excel will display all the cells that contain the value. On the bottom right corner of the window, you can see the number of cells with the data item you entered.
COUNTIF Function
The COUNTIF function is a conditional function that only counts data when it meets specified criteria. You will have to set a range to count data from and criteria that the item must meet for Excel to count it. You can customize the COUNTIF function to count particular data using different formulas. To count a set value, you might have to use the wildcards, the Asterisk (*), and the Question mark (?) symbols in the formula. The placement of the Asterisk symbol determines what value to count in the cell:
If you place the Asterisk symbol at the end, Excel will count the values starting with your criteria. If you place the symbol in the beginning, Excel will take values ending with the criteria into account. If you want Excel to count the value with the criteria mentioned anywhere, you need to open and close the criteria with the Asterisk symbol. You can also use only the Asterisk symbol to count all cells that include only text.
The Question mark symbol fills in the missing characters in your criteria. This is handy if you’re looking to count multiple data with a few different characters. For instance, p?m will count both pam and pom. You can furthermore combine Asterisks and Question mark symbols in your criteria. If you only use the combination of Asterisk and Question mark, Excel will exclude cells that contain only space while counting cells with text.Now, let’s move on to how to use the COUNTIF functions to count criteria in MS Excel.
Count All Cells with Text
You can count all cells with text (including space) using the COUNTIF function. For this, you will need to use the mentioned wildcard, Asterisk. In an empty cell, enter the following formula:=COUNTIF(range, “*”)
Count Cells with Text Excluding Space
When you use the formula to count all cells with text, Excel also counts empty cells that have space in it. You need to use the Asterisk and Question mark symbol in the COUNTIF formula to count cells with text excluding space.Enter the formula in this format in an empty cell:=COUNTIF(range, “*?”)
Count Cells with Specific Data
If you want Excel to count cells with specific data, you can pass only that value as a criterion in the COUNTIF formula. Use this format in an empty cell in your worksheet:=COUNTIF(range, “criteria”)
Count Cells Starting with Criteria
You can prompt Excel to record the number of values that begin with the criteria passed. For this, you need to end the criteria with the Asterisk symbol. Here is the format you can enter in an empty cell:=COUNTIF(range, “criteria*”)
Count Cells Ending with Criteria
If you want Excel to count data that only end with the criteria, you need to place the Asterisk symbol at the beginning of your criteria in the COUNTIF formula.Use this formula format in an empty cell:=COUNTIF(range, “*criteria”)
Count Cells with Criteria
Open and close your criteria with the Asterisk symbol in the COUNTIF formula to prompt Excel to count cells with your criteria. Excel will count cells containing the criteria either in the data’s beginning, end or middle.In an empty cell, enter the formula in this format:=COUNTIF(range, “criteria”)
Count Similar Cells
If you want to use count cells that are similar but contain a few different characters, you can use the wildcard, the Question mark symbol. Simply replace the distinct characters with ? from the criteria in the COUNTIF formula. Enter the formula in this format in an empty cell:=COUNTIF(range, “cr??eria”)
