< Go Back

Count cells that contain text

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.

Syntax

=COUNTIF(rng,“*”)

Arguments

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.

Explanation

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.

Example

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.

Step 1

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.

Step 2

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:

=COUNTIFS(range,"*",range,"<> ")

Applying this formula to our example, we have:

Figure 3. COUNTIF function returns cells that don’t contain text.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar