How to count cells with text in Excel

Numerous count functions are available in Excel, and in most circumstances, they would be sufficient. The Excel count functions are listed below:

COUNT - To determine how many cells contain numbers.

COUNTA- To count the number of non-empty cells.

To count a blank cell, useCOUNTBLANK.

COUNTIF/COUNTIFS- To count the number of cells that satisfy the stated conditions.

Look at the dataset displayed below:

There are all possible text, number, blank, special character, and logical value combinations in it.

We will utilise the wildcard characters to count the number of cells that have text values in them:

Asterisk (*): In Excel, an asterisk stands in for any amount of characters. Ex*, for instance, might also stand for excel, excels, example, expert, etc.

One single character is represented by aquestion mark (?). Tr?mp, for instance, might refer to Trump or Tramp.

To detect wildcard characters in a string, use the Tilde (~)

Let's now develop formulae to count various combinations.

Excel Count Text-Containing Cells (including Blanks):

Here is the equation:

=COUNTIF(A1:A11,”*”)

The COUNTIF function is used in this calculation, and the criterion includes a wildcard character. Asterisk (*), which stands for any amount of characters, counts every cell that contains text characters.

Even cells with empty strings are counted (empty strings can occur from formulas returning = or cells with apostrophes). Even though an empty string seems to be a blank cell, this algorithm counts it.

The values of logic are not counted.

Excel Count Text-Containing Cells (excluding Blanks):

Here is the equation:

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

The criterion argument in this formula consists of two wildcard characters together (question mark and asterisk). This signifies that the cell must contain at least one character.

The empty string (an apostrophe or =) or cells with an empty string are not counted by this formula. An empty string does not meet the requirements and is not counted since it contains no characters.

Furthermore, logical values are not counted.

Count Text-Containing Cells (excluding Blanks, including Logical Values)

Here is the equation:

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

Wildcard characters (* and?) are combined in the formula's first portion. This calculates the quantity of cells with at least one text character (counts text and special characters, but does not count cells with empty strings).

The formula's second step looks for logical values. The ISLOGICAL function in Excel returns TRUE if a logical value exists and FALSE otherwise. As long as there is a double negative sign, TRUE values are changed to 1 and FALSE values to 0. Following that, the SUMPRODUCT function in Excel only returns the number of cells that have a logical value.

The aforementioned examples show how to count cells by combining formulae and wildcard characters. In a similar way, you can create formulae that determine the SUM or AVERAGE of a group of cells based on the type of data included inside.

Did you learn about how to count cells with text in an Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.