Count Cells with Text

Learn the formula that will count cells with text in an Excel spreadsheet. The counting formula will take a range of cells and tell you how many of those cells contain text. You will also be able to retrieve the number of cells that contain a certain word or words in their text. Using these formulas is good when you are filling out large spreadsheets, or need to organize and isolate data within a large worksheet. Your goal in the example is to get the total number of cells with text, along with the number of cells that contain just certain text.

Download the example spreadsheet here to begin.

Open the example and you will see a contact list with names, emails, phone numbers, and different states. To learn how to count cells with text, start by going to cell B20 and typing in this formula:

=ROWS(B6:E15)*COLUMNS(B6:E15)

This formula tells Excel to look at all the cells in the rows and columns starting from B6 to E15 and count how many contain a piece of text. When you hit enter, it should give you answer of 40 cells.

Next, learn to use the COUNTIF formula to have your spreadsheet searched for particular text and return the number of cells that contain that particular text. Go to cell C20 and input this formula:

=COUNTIF(B6:E15,”CA”)

This means that in the spreadsheet, it is look at all the cells from B6 to E15 for the text “CA”. The result is the number 3, because of the 40 text entries, 3 of them have CA, California, as their content.

To use these formulas in your own spreadsheet, change the cells (B6 and E15) to your own range of cells you want searched. You can also experiment in the example by changing the text searched for, and changing the cells you want searched.