Learn three ways to count cells with text in Excel

The basics of counting cells with text

What is the purpose of counting cells with text? It allows you to obtain from a table or database the number of cells that contain the text you are looking for, whether it is a particular word, the initial characters, a specific ending, or a set of requirements!

How can you count cells with text in Excel? It can be done in three ways with the help of three different functions, depending on the number of requirements you want to count the cells:

COUNT YES
COUNT IF SET
SUMPRODUCT

If you want to enhance your learning about the COUNTIF function, I recommend you look at the following post.

And if you want to clarify your doubts regarding the IF function in general, you can do so in the following link.

1. COUNTIF to count cells with text

COUNTIF is a function that counts the number of cells that satisfy a desired rule.

Syntax:

=COUNTIF(range; criterion)

Arguments:

Range: set of cells that you want to count. (Mandatory).

Criterion: requirement that the cells must meet to be counted. The criterion for counting cells with text must be written between quotes (“”). (Mandatory). To establish criteria, signs such as “=”, “>”, “<” can be used. “Wildcard characters” are used to refer to the text.

What are wildcards in counting cells with text?

They are a set of characters that replace other characters in their place. There are two wildcard characters:

The question mark (?): Replaces only one character individually. For example, if “S?” As a criterion for counting cells with text, all words that have a letter in the place of the question mark will be counted, such as: “Yes”, “I know”, “His”.
The asterisk (*)- Replaces a set of characters collectively. If “S*” is now used as a criterion for counting cells with text, all words starting with the letter “S” will be counted, such as: “Soup”, “Saturday”, “Sound”.

Ninja Tips:

The criteria for counting cells with text are not case sensitive.
The COUNTIF function does not count the logical expressions “FALSE” and “TRUE”.
The COUNTIF function does not count blank cells. Only the account if it has (´).
Wildcard characters cannot replace numbers for counting.

How to count cells with text with the COUNTIF function?

Let's imagine that we have the following table in Excel, where there are 2 groups of hotels.

For example, we want to count only the cells that have text in column “B”, ignoring the numbers “1” and “2”. For this we use the COUNTIF function. We must select the range, and establish the criteria, as seen in the image. We use the criterion “*” because the asterisk represents a set of characters, thus omitting the numbers.

We obtain the result: there are 8 hotels in total!

How do we do it if we want to know how many of the hotel names start with the letter “A”?

We do the same as before, but now we change the criteria. Now the requirement will be “A*”, as the image shows. The “*” will indicate all the characters that could come after the letter “A”.

The result is three, that is, three hotels begin with the letter “A”; Arena's Hotel, Atlanta Hotel and Atlantis.

How do we do it if we now want to know how many hotels end with “Hotel”?

Now the criteria for will be different: “*Hotel”, where “*” can be replaced by any set of characters.

The result is four, as we see in the image. There are four cells that meet this requirement to be counted: Beach Hotel, Arena's Hotel, Atlanta Hotel and Hugo's Hotel.

We can also use the asterisk at the beginning and end to count cells with text. For example, if we want to know how many hotels include the term “´s”, we apply “*´s*” as a criterion, as shown in the image below.

In this way the result is two: Arena's Hotel and Hugo's Hotel.

We can make multiple variations with the wildcard sign to count cells with text! And thus obtain the number of cells with the requirement we are looking for.

2. COUNTIF SET to count cells with text

COUNTIF SET is a function that is used to count the number of cells that meet more than one established criterion.

Syntax:

=COUNTIF.SET(criteria_range1; criterion1; criteria_range2; criterion2…)

Arguments:

Criteria_range1: first set of cells that you want to count for criterion 1. (Mandatory).

Criterion1: requirement that the cells selected for criterion 1 must meet to be counted. (Mandatory).

criteria_range2; criterion2: set of cells and additional requirements. (Optional). The function allows a maximum of 127 criteria. Each additional range must have the same number of rows and columns as the first range (range_criteria1). Otherwise, the function does not allow it.

We continue with the previous example, but this time we have information about the area where the hotels are located and the number of stars each hotel has, as we see in the following table.

For example, we want to see the number of hotels in the Downtown area. We apply the COUNTIF.SET function, select the range, and apply the “Center” criterion to count cells with text.

We see that the result is three: Esmeralda, Oriental and Hugo's Hotel are in the downtown area.

Now we want to count cells with text with more than one criteria. We want to see how many hotels in the downtown area have more than four stars, how do we do it? We add a second criterion with the respective range to the function we had before. Our second criterion will be “>4”, as we see in the image.

The result is two: the Esmeralda hotel and the Hugo's Hotel meet these two requirements.

Likewise, we can apply it to both the northern zone and the southern zone, with the criteria we want to count the cells as text.

3. SUMPRODUCT to count cells with text

The SUMPRODUCT function adds the products of respective matrices. In this case, it can be used to count cells with text that meet more than one criterion, just like the previous COUNTIF SET function.

Syntax:

=SUMPRODUCT(matrix1; matrix 2; matrix 3…)

Arguments:

Matrix 1: set of cells that you want to multiply and then add. (Mandatory).

Matrix 2, 3…: set of cells that you want to multiply and then add.

The SUMPRODUCT function will take the logical values to deliver the results:

TRUE * TRUE = 1
TRUE * FALSE = 0
FALSE * TRUE = 0
FALSE * FALSE = 0

In this way, the SUMPRODUCT function returns the sum of the 1s and the 0s.

In our example, we want to see how many hotels in the downtown area have more than four stars, just like the previous case, but this time to count cells with text, we do it with the SUMPRODUCT function, how do we do it? We multiply the two criteria associated with their respective ranges: “Center” and “>4”, as shown in the image. It gives us a result of 2.How does the SUMPRODUCT function perform the calculation?

Only Esmeralda and Hugo`s Hotel obtained “TRUE” in both results, therefore, each of them obtained the value of 1, the sum of both being 2.

We see that it gives us the same result as with the COUNTIF SET function! Therefore, these two functions are similar for counting cells that do not have numerical data.

Common error of counting cells with text

The result is zero, but incorrect: the COUNTIF and COUNTIF SET functions return a result of zero when the criteria are not enclosed in quotes. For this you must verify that the criteria are in quotes.