Which function is used to count the number of cells that contains something if the criteria are met?

Watch Video – How to Count Cells that Contain Text Strings

Counting is one of the most common tasks people do in Excel. It’s one of the metric that is often used to summarize the data. For example, count sales done by Bob, or sales more than 500K or quantity of Product X sold.

Excel has a variety of count functions, and in most cases, these inbuilt Excel functions would suffice. Below are the count functions in Excel:

• COUNT – To count the number of cells that have numbers in it.
• COUNTA – To count the number of cells that are not empty.
• COUNTBLANK – To count blank cell.
• COUNTIF/COUNTIFS – To count cells when the specified criteria are met.

There may sometimes be situations where you need to create a combination of functions to get the counting done in Excel.

One such case is to count cells that contain text strings.

• Count Cells that Contain Text in Excel
  • Including Blank
  • Excluding Blanks
  • Excluding Blanks but including Logical Values

Count Cells that Contain Text in Excel

Text values can come in many forms. It could be:

• Text String
  • Text Strings or Alphanumeric characters. Example – Trump Excel or Trump Excel 123.
• Empty String
  • A cell that looks blank but contains =”” or ‘ (if you just type an apostrophe in a cell, it looks blank).
• Logical Values
  • Example – TRUE and FALSE.
• Special characters
  • Example – @, !, $ %.

Have a look at the data set shown below:

It has all the combinations of text, numbers, blank, special characters, and logical values.

To count cells that contain text values, we will use the wildcard characters:

• Asterisk (*): An asterisk represents any number of characters in excel. For example, ex*could mean excel, excels, example, expert, etc.
• Question Mark (?): A question mark represents one single character. For example, Tr?mp could mean Trump or Tramp.
• Tilde (~): To identify wildcard characters in a string.

See Also: Examples of using Wildcard Characters in Excel.

Now let’s create formulas to count different combinations.

Count Cells that Contain Text in Excel (including Blanks)

Here is the formula:

=COUNTIF(A1:A11,”*”)

This formula uses COUNTIF function with a wildcard character in the criteria. Since asterisk (*) represents any number of characters, it counts all the cells that have text characters in it.

It even counts cells that have an empty string in it (an empty string can be a result of formula returning =”” or a cell that contains an apostrophe). While a cell with empty string looks blank, it is counted by this formula.

Logical Values are not counted.

Count Cells that Contain Text in Excel (excluding Blanks)

Here is the formula:

=COUNTIF(A1:A11,”?*”)

In this formula, the criteria argument is made up of a combination of two wildcard characters (question mark and asterisk). This means that there should, at least, be one character in the cell.

This formula does not count cells that contain an empty string (an apostrophe or =””). Since an empty string has no character in it, it fails the criteria and is not counted.

Logical Values are also not counted.

Count Cells that Contain Text (excluding Blanks, including Logical Values)

Here is the formula:

=COUNTIF(A1:A11,”?*”) + SUMPRODUCT(–(ISLOGICAL(A1:A11))

The first part of the formula uses a combination of wildcard characters (* and ?). This returns the number of cells that have at least one text character in it (counts text and special characters, but does not count cells with empty strings).

The second part of the formula checks for logical values. Excel ISLOGICAL function returns TRUE if there is a logical value and FALSE if there isn’t. A double negative sign ensures that TRUEs are converted into 1 and FALSEs into 0. Excel SUMPRODUCT function then simply returns the number of cells that have a logical value in it.

These above examples demonstrate how to use a combination of formulas and wildcard characters to count cells. In a similar fashion, you can also construct formulas to find the SUM or AVERAGE of a range of cells based on the data type in it.

You May Also Like the Following Excel Tutorials:

• Count the Number of Words in a Text String.
• Using Multiple Criteria in Excel COUNTIF and COUNTIFS Function.
• How to Count Colored Cells in Excel.
• Count Characters in a Cell (or Range of Cells) Using Formulas in Excel

Which function is used to count the number of cells containing number?

How do you count cells with criteria?

What function used to count the cells with a specified content within a range?

What does Counta () function do?