How to count cells with text in Excel

Excel spreadsheets are one of the best ways to store and analyze data. Spreadsheets can consist of cells with a combination of numbers and text. To better understand your data, you may need to differentiate cells with text.

In this article, we will show you how to select a range of cells and then use the COUNTIF function to count cells with text. Also, you can learn how to find specific, duplicate, and color text in your spreadsheet.

How to Count Cells with Text in Excel on Windows PC

To count cells in your spreadsheet with text through your Windows computer:

1. Click on an empty cell in your spreadsheet to insert the formula.
   
2. Type in the empty cell “=COUNTIF (range, criteria)”. This formula is used to count the number of cells containing text in a range of cells.
   
3. In "range", enter the range of cells you want to check. Enter the first and last cells separated by a colon. For example, to count cells A2 through A9, you would enter “A2:A9”.
   
4. In "criteria", enter "*". This counts the number of cells with text in the range. The full formula should look like “=COUNTIF(A2:A9, “*”)”.
   
5. Now press Enter or Return key to apply the formula. The result will be displayed in the formula cell.
   

How to Count Cells with Text in Excel on a Mac

The steps to count cells with text in your spreadsheet through macOS are the same as with Excel for Windows:

1. Launch Excel then open the spreadsheet you want to analyze.
2. Select an empty cell on your spreadsheet to enter the formula.
3. In the empty cell, type “=COUNTIF (range, criteria)”. This formula counts the number of cells with text in a range of cells.
4. In "range", enter the range of cells you want to view. Enter the first and last cells divided by two points. For example, to count cells B2 through B10, you would enter “B2:B10”.
5. In "criteria", enter "*". This counts the number of cells with text in the range. For example, your complete formula should look like “=COUNTIF (B2:B10, “*”)”.
6. On your keyboard, press the return or enter key to apply the formula. The result will be displayed in the selected cell.

How to count cells with text in Excel 365

To count cells with text in your spreadsheet using Excel 365, you would use the same COUNTIF function used in Excel for Windows and macOS. Here's how:

1. Open the Excel spreadsheet you want to review.
2. Click on a blank cell to type the formula.
3. In the empty cell, type: “=COUNTIF (range, criteria)”. This formula will count the number of cells containing text in a specified range of cells.
4. In "range", enter the range of cells you want to view. Enter the first and last cells separated by a colon. For example, to count cells C2 through C11, enter “C2:C1”.
5. In "criteria", enter "*". This counts the number of cells with text in the range. For example, your full formula should look something like “=COUNTIF(C2:C11, “*”)”.
6. Now press Enter or Return key to apply the formula. The result will be displayed in the formula cell.

How to count cells with text in Excel on iPhone app

To count the number of cells in your spreadsheet containing text using the Excel app via iPhone:

1. Open the spreadsheet you want to work on by tapping "Open" to view your saved spreadsheets, then tap the file to open it.
2. Double-tap an empty cell in the worksheet to enter the COUNTIF formula. Or you can long press on an empty cell and then press "Edit" in the context menu.
   
3. In the blank cell type, “=COUNTIF (range, criteria)”. This formula will count the number of cells containing text in a range of cells.
   
4. For "Range", type the range of cells you want to count. Enter the first and last cells separated by a colon. To count cells D2 through D12, enter “D2:D12”.
   
5. For "criteria", type "*". This counts the number of cells with text in the range. The full formula should look something like: “=COUNTIF (D2:D12, “*”)”.
   
6. Now press Enter or Return key to apply the formula. The result will be displayed in the formula cell.
   

How to count cells with text in Excel on Android app

To count the number of cells containing text in your spreadsheet using the Excel application via your Android device:

1. Open the spreadsheet you want to review by tapping "Open" to see your saved spreadsheets, then tap the file to open it.
   
2. Double-tap a blank cell to enter the COUNTIF formula. Alternatively, long-press an empty cell, then tap "Edit" from the pop-up menu.
   
3. In the empty cell, enter “=COUNTIF (range, criteria)”. This formula counts the number of cells with text in a range of cells.
   
4. In "range", enter the range of cells you want to count. Enter the first and last cells separated by a colon. To count cells E2 through E12, enter “E2:E12”.
   
5. For "criteria", type "*". This counts the number of cells with text in the specified range. Your full formula should look something like: “=COUNTIF (A2:E12, “*”)”.
   
6. Now press Enter or Return key to apply the formula. The result will be displayed in the formula cell.
   

How to count cells with specific text in Excel

By using the “COUNTIF” function, we can count the number of cells with specific text. In this example, we'll find the number of times the word "Excel" appears in a specific range of cells:

1. Open the spreadsheet in Excel that you want to review.
2. Click on a blank cell to type the formula.
   
3. In the empty cell, type “=COUNTIF (range, criteria)”. This formula will count the number of cells containing specific text in a range of cells.
   
4. For "Range", enter the range of cells you want to count. Enter the first and last cells separated by a colon. To count cells A2 through A20, enter "A2:A20".
   
5. For "Criteria", type "Excel". This counts the number of cells with "Excel" in the specified range. Your formula should look something similar to “=COUNTIF(A2:A20, “Excel”)”.
   

How to count cells with duplicate text in Excel

In the following example, we are looking for duplicate student grades. Our spreadsheet is configured as follows:

• Column A – lists our A2:A10 students
• Column B – lists each student's grades (A, B, or C)
• Column D – lists available notes. D2 for “As”, D3 for “Bs” and D4 for “Cs”.
• Column E – lists the number of each note.

Count cells with duplicate text including first instance

To count the number of cells in your spreadsheet with grade 'A', 'B' or 'C' instances – including the first instance – enter the following formulas:

• For Grade “A” instances, click cell E2 and type the formula “=COUNTIF(B2:B10,D2)”.
  
• For Grade “B” instances, click cell E3 and type the formula “=COUNTIF(B2:B10,D3)”.
  
• For grade “C” instances, click cell E4 and type the formula “=COUNTIF(B2:B10,D4)”.
  

You now have the number of duplicate notes, including the first instance listed in the "E" column.

Count cells with duplicate text excluding first instance

To count the number of cells in your spreadsheet with grade 'A', 'B' or 'C' instances – excluding the first instance – enter the following formulas:

• For Grade “A” instances except the first instance, click cell E2 and type the formula “=COUNTIF($B$2:$B$10,D2)-1”.
  
• For grade “B” instances except the first instance, click cell E3 and type the formula “=COUNTIF ($B$2:$B$10,D3)-1”.
  
• For grade “C” instances except the first instance, click cell E4 and type the formula “=COUNTIF($B$2:$B$10,D4)-1”.
  

You now have the number of duplicate notes excluding the first instance listed in the "E" column.

How to count cells with colored text in Excel

Excel doesn't have a formula to count cells based on the color of their text. As a workaround, filter the results and then count. Here's how:

1. Open the spreadsheet you want to analyze.
   
2. Right-click a cell with text of the color you want to count.
   
3. Select "Filter" and then "Filter by font color of selected cell" to filter cells with the selected text color.
   
4. Then tell Excel to count your data range. If your text is listed from cell B2 to B20, enter the following formula: “=SUBTOTAL (B2:B20)”.
   

Once you hit "Enter" to apply the filter, Excel will only display cells containing that color and hide the remaining values.

The "SUBTOTAL" function will exclude values ​​from hidden rows, thus returning the count for the selected text color only.

Find cells with text

The Excel application does a great job of storing your data and facilitating analysis. It handles both text and numbers.

Its more than four hundred functions include the "COUNTIF" function. This function is useful for finding the count of cells containing specific information such as cells with text, and the number of occurrences for specific text.

Were you able to find out what you needed to know about your spreadsheet data? How well do you find Excel in general? Share your opinions in the comments section below.