Download Practice Workbook
Method 1 – Using COUNTA Function
Steps:
- Go to cell C10 and insert the following formula:
=COUNTA(B5:C9)-COUNT(B5:C9)
- Press Enter and this will calculate the total number of cells with text and display the result in C10.
Read More: Count If Cell Contains Text in Excel (5 Easy Approaches)
Method 2 – Applying COUNTIF Function
Steps:
- Double-click on cell C10 and enter the following formula:
=COUNTIF(B5:C9,"*")
- Press Enter to get the result.
Read More: Count If a Cell Contains Any Text in Excel (4 Methods)
Method 3 – Utilizing ISTEXT Function
Steps:
- Double-click on cell C10 and insert the formula below:
=COUNT(IF(ISTEXT(B5:C9),1))
- Press Enter key to get the result.
How Does the Formula Work?
- ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
- IF(ISTEXT(B5:C9),1): This returns an array of 1 and FALSE depending on the cell containing text value.
Read More: COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)
Method 4 – Counting with SUM Function
Steps:
- Go to cell C10 and enter the following formula:
=SUM(IF(ISTEXT(B5:C9),1))
- Press the Enter key or click on any blank cell.
It will give the result.
How Does the Formula Work?
- ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
- IF(ISTEXT(B5:C9),1): This returns an array of 1 and FALSE depending on the cell containing text value.
Read More: How to Count Specific Words in a Column in Excel (2 Methods)
Method 5 – Counting Text Cells by SUMPRODUCT Function
Steps:
- Enter the following formula inside cell C10:
=SUMPRODUCT(IF(ISTEXT(B5:C9),1))
- Press Enter key to get the result.
How Does the Formula Work?
- ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
- IF(ISTEXT(B5:C9),1): This returns an array of 1 and FALSE depending on the cell containing text value.
Method 6 – Utilizing SIGN Function in Excel
Steps:
- Go to cell C10 and enter in the formula below:
=SUMPRODUCT(SIGN(ISTEXT(B5:C9)))
- Press Enter to get the result.
How Does the Formula Work?
- ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
- SIGN(ISTEXT(B5:C9)): This portion returns an array of 1 and 0 when the cell is positive or 0.
Method 7 – Excluding Cells with Space
In the following sample dataset, cell B8 has a single space which the previous methods will count as text. For this, we need a different way to count cells with text in excel but excluding any cell with space.
Steps:
- Go to cell C10 and insert the following formula:
=COUNTIF(B5:C9,"><")
- Press Enter and this will calculate the total number of text cells.
Method 8 – Counting Cells After Filtering
We have filtered the sample dataset and you can see that row 9 is not present. Regular formulas will count this row as well. So we will use a different formula to count only filtered cells with text in excel.
Steps:
- Double-click on cell C10 and enter the below formula:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("B"&ROW(B5:C10)))*(ISTEXT(B5:C10)))
- Press the Enter key and you should get the number of cells with text data.
How Does the Formula Work?
- ISTEXT(B5:C10): The ISTEXT function checks each cell in the range and returns TRUE if a cell contains text, FALSE otherwise.
- INDIRECT(“B”&ROW(B5:C10)): INDIRECT function to set the individual references of all cells in the specified range.
- SUBTOTAL(103, INDIRECT(“B”&ROW(B5:C10)))*(ISTEXT(B5:C10)): This portion gives back an array of 1 and 0 which indicates the existence of text in a cell or otherwise.
Read More: How to Count Filtered Cells with Text in Excel (3 Methods)
How to Count Cells In Excel
Steps:
- Go to the Developer tab and select Visual Basic.
- Select Insert in the VBA window and click on Module.
- Enter in the formula below in the new window:
Sub CountCells()
Range("C10") = Application.WorksheetFunction.CountA(Range("B5:C9"))
End Sub
- Open the macro from the Developer tab by clicking on Macros.
- In the Macro window, select the CountCells macro and click Run.
- The VBA code will calculate the total number of cells inside cell C10.
How to Count Cells with Numbers in Excel
Steps:
- Double-click on cell C10 and enter the following formula:
=COUNT(B5:C9)
- Press the Enter key and this will find the number of cells with a number equal to 5.
Read More: How to Count Cells with Specific Value in Excel (5 Easy Methods)
Related Articles
- Count Frequency of Text in Excel (4 Effective Ways)
- Excel VBA to Count Cells Containing Specific Text
- How to Count Cells in Excel with Different Text (5 Ways)
