As a student or professional using excel, one of the many things you would be doing is counting. Especially because it has a lot to do with the arrangement and interpretation of data.
rng – refers to the range of the cells in Excel.
“*” – refers to a wildcard that is the same as any number of characters. This is what the asterisk stands for.
The COUNTIF function and a wildcard are used in Excel to count the number of cells that contain text characters. Mind you, the formula also counts the cells that contain empty strings. An empty string in itself could be so because a formula returned =“”. It could also be an empty string because there’s an apostrophe in it. So, the formula counts it.
Assuming we have a range of mixed values which include those with text and otherwise and we are tasked with counting the cells that do not have text in them.
Make a list of all the values in the range as we have in the image below. The other four columns contain the following parameters:
- Range: the range of values for which the counting will happen.
- Criteria: the “*” wildcard character.
- Count: the cells where the formula would be entered.
Figure 1: List of values in the range and COUNTIF parameters.
Then, you use the formula as applicable to our example here. The COUNTIF function counts the cells that contain text and returns as shown in the image.
Figure 2. COUNTIF function counts the cells that contain text.
Using the COUNTIFS function
The COUNTIFS function counts cells that don’t contain text and gives another perspective to counting. Here’s the formula:
Applying this formula to our example, we have:
Figure 3. COUNTIF function returns cells that don’t contain text.