Count cells with text, COUNTIF function and wildcards
For me, the simplest solution is using the COUNTIF function and wildcards. We have an asterisk that represents any text string, even an empty one. We also have a question mark that represents just one single sign (Fig. 1).
Fig. 1 Wildcards
Let’s start with counting all cells with text. First, we have to select a proper range and press the F4 key to lock it. Then, we have to write an asterisk in double quotes so that it counts any text string, even an empty one (Fig. 2).
=COUNTIF($B$2:$B$11,”*”)
Fig. 2 Proper range and an asterisk
And just like that we have 4 cells with text. In cell B6 I have pasted an empty text string and even if we see nothing, Excel counts it as a cell with an empty text string. But, what can we do if we don’t want to count empty text strings? We can copy our formula and add a question mark next to the asterisk. It doesn’t matter if it’s before or after the sign (Fig. 4).
=COUNTIF($B$2:$B$11,”?*”)
Fig. 4 Formula modification
This way we counted all cells that have at least one sign (Fig. 5).
Fig. 5 Results
If we want to count all not empty cells, we can change our criteria to the <> signs (Fig. 6).
=COUNTIF($B$2:$B$11,”<>”)
Fig. 6 Adding <> signs
However, the simplest solution in most cases would be using the COUNTA function, where we don’t have to worry about any criteria (Fig. 7).
Sometimes, we need to count all cells containing certain text. In our example, it will be a product name.
Count all cells containing certain text | Excel Tips #27
Let’s assume that we want to count all cells containing the name of eggs. In the In the basket column, we have cells where the name of eggs appears at the beginning, in the middle, or at the end of the text. Sometimes, the cell contains only eggs (Fig. 1).
Fig. 1 Eggs in many locations
In Excel, it’s a simple task because we have the COUNTIF function, where we can use wildcards. Let’s start writing the function. We have to select a range, which is the In the basket column, F4 to lock it, then let’s write criteria. If we want to count only cells that contain only the word eggs, we would writeD2 in the criteria, however, we want to count the cells which contain eggs and other products as well. That’s why we have to add a wildcard, i.e. an asterisk (*) that replaces any number of characters, even an empty text string. So, we’re putting the asterisk in double quotes, then we’re writing an ampersand to combine it with eggs, then D2, where eggs are. As we can see, eggs can be placed also at the end, that’s why we have to combine it with another asterisk in double quotes. Our criteria look as follows (Fig. 2).
=COUNTIF($B$2:$B$20,”*”&D2&”*”
Fig. 2 Our criteria
When we press the F9 key, we can see how our criteria really look (Fig. 3).
=COUNTIF($B$2:$B$20,”*Eggs*”
Fig. 3 Our criteria after F9
By pressing Ctrl + Z, let’s return to the previous version, and close our formula (Fig. 4).
=COUNTIF($B$2:$B$20,”*”&D2&”*”)
Fig. 4 Our whole formula
After entering and copying the formula down, we can see how many cells contain certain product (Fig. 5).
Today, we want to count how many times we sold apples, pears and oranges. We are going to use a small table to check our results, because with a bigger table it isn’t going to be easy. We will use the COUNTIF function. This function has got only two arguments. The first one is a range, where we will be checking our conditions or criteria. In our case, the criterion is the name of our product, so we are selecting the cell with the product name. Such a function will check each cell in the range, whether it contains the name or not.
COUNTIF function — how many times we sell apples
=COUNTIF(B2:B10,F2)
Fig. 1 COUNTIF function
If a cell contains the name, the function will add 1 to the counter. We can see that there are four 1s.
Fig. 2 Four 1s
We also have to lock the range by pressing the F4 key, so that it stays in the same place. Criteria, however, should not be locked because we want them to change while copying the formula down, i.e. we want a given product to change.
=COUNTIF($B$2:$B$10,F2)
Fig. 3 Locked range
After entering and copying down, we have our results. We can see that apples have been sold four times, pears three times and oranges two times. It will also work with bigger tables. All we need to do is to write a bigger range.
