Sometimes, we need to count all cells containing certain text. In our example, it will be a product name.
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).
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 write D2 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&”*”
When we press the F9 key, we can see how our criteria really look (Fig. 3).
=COUNTIF($B$2:$B$20,”*Eggs*”
By pressing Ctrl + Z, let’s return to the previous version, and close our formula (Fig. 4).
=COUNTIF($B$2:$B$20,”*”&D2&”*”)
After entering and copying the formula down, we can see how many cells contain certain product (Fig. 5).
