We will use COUNTIF function to count the cells with text. We enter criteria and cell range in the parameters of COUNTIF function
Syntax of COUNTIF: =COUNTIF(cell range, criteria)
If you want to learn more about the COUNTIF function, click here for the tutorial.
In this tutorial, we will take two approaches when counting cells with text.
- Count Cells with any text.
- Count Cells with a specific text.
Count cells with any text
We will be using a wild card in the criteria for the COUNTIF function. To count cells with text, we will use asterisk(*) in the criteria.
We will use an example, we have a list of items that need to be counted.
Enter the formula on any cell: =COUNTIF(
This function has two parameters, first, cell range will be specified and then the criteria will be entered.
Enter the cell range, by selecting the cells or you can also enter the cell range manually. The formula becomes: =COUNTIF(A2:A9,
The cell range you choose can be different.
Now, we enter the criteria, we will use asterisk(*) character as a wild card. This will only count cells with text and will not count numbers.
Enter asterisk(*) in the second parameter of the function in parenthesis, so the formula becomes: =COUNTIF(A2:A9, "*")
Apply the formula. we see that all the items with text has
Important tips for Asterisk(*) wildcard for COUNTIF function
- Asterisk(*) wild card in the COUNTIF criteria is used to count any cell with alphabets.
- It will not count cells with numbers only.
- It will count cells with alphanumeric text.
Count Cells with a specific text
To count a cell with a specific text, we only need to enter that text in parenthesis for the criteria of the COUNTIF function.
We will use another example to demonstrate how it works. We have list of items that are labelled as Orange, Apple and Banana. We will use COUNTIF function to count how many cells have those items.
For this example, we want to count the number of oranges. So enter the formula: =COUNTIF(
Enter the range, and then in the second parameter, enter the specific text, you want to count. The formula becomes: =COUNTIF(B12:B21, "Orange")
We can see that, it returns the number. Similarly, we can enter this formula for other items.
More about counting cells: