Count Cells with Text in Excel

Excel spreadsheets are widely used in many fields to store and analyze data. Usually, the cells present in excel are a combination of numbers and text. To perform the calculation effectively and to understand the data, the user needs to know which cell contains data and which partition is without data. We can create a formula in Microsoft Excel to count the cells that carry text.

In this tutorial, let’s cover the step-by-step implementation of counting cells with text.

Steps to Count Cells with Text Using Formula

Step 1: Add the required data in the range of cells.

STEP 2: To insert the formula select the cell in the worksheet. And enter the formula function COUNTIF (range, criteria), where the range parameter is used to count the cells within chosen cells.

STEP 3: Type the formula mentioned above in the image. The range includes from A2, which is separated by colon A7 (A2:A7) strings. For criteria, add "*" with quotes. The requirements are used to select the cell range which contains the text.

Finally, the formula will look like =COUNTIF (A2:A7,"*")

STEP 4: Press Enter Key. The result will display in the selected cell as shown below.

How to Count Cell in an Excel with Specific Text?

A similar method is followed to count cells with specific text, and a slight change in the formula is done. Instead of criteria, type the required text in the double quote.

STEP 1: Type the formula as =COUNTIF (A2:A7, “ROSE”). Press Enter

STEP 2: The result will be displayed in the above images.

How to count Blank and Non-blank Cells?

To check the blank and Non-blank cells specific formula is used as follows. COUNTBLANK, COUNT and COUNTA prevent the empty and Non-blank cells. It is one of the statistical functions used to check these functions.

COUNT is a function used to check how many cells contain numeric data in the range of cells.

COUNTBLANK- This function checks the number of blank cells in a cell range.

COUNTA- It counts the number of populated cells in the range.

Example1: Count Blank cells in the selected list.

STEP 1: Enter the data in the spreadsheet as follows.

STEP 2: Select the cell where the result wants to display. Enter the formula as =COUNTBLANK (A2:A10)

STEP 3: Press Enter key. The result is displayed in the required cell, which is blank. Here the number of empty cell present is 2. In the required cell the result is displayed as 2.                                                                                                     

Example 2: How to check the Non-Blank Cell?

One can check the Non-Blank cell utilizing using the formula. Cells which contain text, formula, space and numbers are not counted. The procedure is as follows =COUNTA (A2:A10), where A2:A10 is the range of cells. Select the cell where the result is to be displayed. Press Enter key after typing the formula and the result will be displayed in the specified cell.

The result will be displayed in the box as shown below.

The result is displayed as seven, where there are seven non-blank cells.