How to count cells with text in Excel

Excel spreadsheets are a great way to store and analyze data, and generally contain cells with a combination of numbers and text. To further understand your data, you need to highlight cells with text. This process is much easier than it looks.

This article explains how to select a range of cells and then use the COUNTIF function to count the number of cells that contain text. Let’s get started!

How to count cells containing text in Excel on Windows PC

To count the number of cells containing text within an Excel spreadsheet using a Windows computer, do the following:

1. Click a blank cell in the spreadsheet to insert the formula.
   
2. Type or paste the job “=COUNTIF (range, criteria)“ Without quotes, which counts the number of cells containing text within a given cell range.
   
3. For Range, enter the range of cells you want to check. Write the first and last cells separated by a colon. For example, to count the number of cells from A2 to A9, you can enter the following text string: “A2:A9. ”
   
4. For Criteria, type "*" with quotes. This pane counts the number of cells that contain text within the specified range. The full formula should look like this:=COUNTIF (A2:A9, “*”). ”
   
5. Now press “Enters” to apply the formula. The result will be displayed in the formula cell.
   

How to count cells containing text in Excel on a Mac

The steps for counting cells containing text within a spreadsheet via macOS are the same as in Excel for Windows:

1. release “Excel”, Then open the spreadsheet you want to analyze. You can also just “double click” The file if it has Excel as the default program.
2. select file “empty cell” into the spreadsheet to enter the formula.
3. In the empty cell, type the following:=COUNTIF (range, criteria). This formula counts the number of cells in the specified range with the text inside.
4. For Range, type the range of cells you want. Enter the first and last cells, divided by a colon. For example, to count the number of cells from B2 to B10, the following should be written: “B2:B10. “
5. For the Criteria pane, type “*” with quotes. This pane counts the number of cells that contain text in the range you typed. For example, the completed formula should look similar to “=COUNTIF (B2:B10, “*”). “
6. On the keyboard, press “EntersApply the formula. The result appears in the selected cell.

How to count cells containing text in Excel 365

To count the number of cells containing text in a spreadsheet using Excel 365, you can apply the same “COUNTIF” function used in Excel for Windows and macOS. Here’s how:

1. open the Excel spreadsheets You want to check.
2. Click “empty cell” to write the formula.
3. In the empty cell, write: “=COUNTIF (range, criteria). This formula counts the number of cells that contain text within the specified range of cells.
4. For the Range pane, type the range of cells you want. Enter the first and last cells separated by a colon. For example, to count the number of cells from C2 to C11, type the following text string: “C2:C1. “
5. For Criteria, type or paste “*” with quotes. This pane counts the number of cells (in the specified range) that contain text. For example, the completed formula should look like this:=COUNTIF (C2:C11, “*”). “
6. Now pressEntersApply the formula you just created. The result will be displayed in the formula cell.

How to count cells containing text in Excel on iPhone app

To count the number of cells containing text within a spreadsheet using the Excel app on your iPhone, do the following:

1. Play file iPhone Excel app.
2. tap on “to open” To view your saved spreadsheets, then select File “excel file” to open it.
3. “double click” on me “empty cell” In the spreadsheet to enter the formula “COUNTIF”, or you can “long press” a “empty cell” then press “release” from the popup menu.
   
4. In the empty cell, type the following:=COUNTIF (range, criteria). This formula counts the number of cells that contain text within a cell range.
   
5. For the “Scope” part, type File “cell range” want to count. Enter the first and last cells separated by a colon. To count the number of cells inside D2 through D12, type the following: “D2:D12. ”
   
6. for the “criteria” type “*” with quotes. This pane counts the number of cells that contain text in the range. The full formula should look like this:=COUNTIF (D2:D12, “*”). ”
   
7. Now, tap on “Entersto apply the generated formula. The result appears in the formula cell.
   

How to count cells containing text in Excel on Android app

To count the number of cells containing text in a spreadsheet using the Android Excel app, do the following:

1. Play file Android Excel Application.
2. Open the spreadsheet you want to check by clicking “to open” To see your saved spreadsheets, then click the desired file to open it automatically.
   
3. “double click” on me “empty cell” To enter the “COUNTIF” formula. Instead, “long press” Which “empty cell” then press “release” from the popup menu.
   
4. Enter the range of cells you wish to calculate for the “Range” portion of the formula:=COUNTIF (range, criteria)No quotes. This formula counts the number of cells that contain text within a cell range.
   
5. Enter the range of cells you want to calculate for the “Range” portion of the formula. Include the first and last cells separated by a colon. To count cells E2 through E12 from a single column, enter the following text string: “E2:E12. ”
   
6. For the Criteria part of the formula, type “*” with quotes. This part of the text string counts the number of cells containing text in the specified range, including more than one row. Your full formula should look like this:=COUNTIF (A2:E12, “*”). ”
   
7. Now press “Enters” to apply the formula. The result appears in the formula cell.
   

How to count cells in Excel that contain specific text

With the “COUNTIF” function, you can count the number of cells containing certain text strings, such as “Excel”, “John” or “John Meyers”. The formula is similar to counting cells containing any text, but you change the “criteria” part of the formula to find specific text. In this example, you’ll see how often the word “Excel” appears in a specific cell range:

1. release “Excel” on your device and open the custom file, or run a file a file Direct if set to open Excel by default.
2. Click “empty cell” to write the formula.
3. In the empty cell, type the following string without the quotes: “=COUNTIF (range, criteria). ”
   
4. Enter the range of cells you want to calculate for the “Range” portion of the formula. Enter the first and last cells separated by a colon. To count cells A2 through A20, type the following string without the quotes:A2:A20. ”
   
5. For the Criteria section of the formula, type "Excel" with quotes. This part of the text string counts the number of cells containing “Excel” in the specified range. The completed text string should look like “=COUNTIF (A2:A20, "Excel").
   

How to count cells with duplicate text in Excel

Besides counting cells with specific text and text, you can count cells with duplicate content.

In the following example, we are looking for duplicate student scores.

• Column A – Lists Our Students (A2: A10)
• Column B – Lists each student’s grades (A, B, or C)
• Column D – Lists all available scores, including D2 for “As”, D3 for “Bs” and D4 for “Cs”.
• Column E – Lists the number of each score.

Count cells with repeating text including the first instance

To count the number of cells in the spreadsheet with instances of class A, B, or C, including the first example, enter the following formula:

1. For grade A, click on cell E2 and type the following text string:=COUNTIF (B2 : B10, D2)“.
   
2. For instances of degree ‘B’, click on cell E3 and type the following text string:=COUNTIF (B2 : B10, D3)“.
   
3. For cases of degree ‘C’, click on cell E4 and type the following:=COUNTIF (B2:B10, D4). ”
   

You now have the number of duplicate estimates, including the first instance listed in column “E.”

How to count cells with colored text in Excel

Excel does not have a specific formula for counting the number of cells based on the color of their text. To get around this, filter the results and then count the cells.

1. Open the spreadsheet you want to analyze.
   
2. Right-click a cell with the text of the color you want to calculate.
   
3. Choose “purifier,” and then “Filter by the color of the selected cell lineFilters cells with the selected text color.
   
4. Next, tell Excel to calculate the range of your data. If the text fills cells B2 through B10, enter the following formula: “=SUBTOTAL (B2:B10)“.
   

Once you press “EntersTo apply the filter, Excel will only display cells containing that color and hide the remaining values.

The ‘SUBTOTAL’ function will exclude values ​​in hidden rows and, therefore, return only the number of the specified text color.

---

In conclusion, Excel does a great job of storing your data and making analysis easier. It deals with text as well as numbers. It has more than four hundred jobs, including “COUNTIF”. This function helps to find the total cells containing specific information such as cells containing text and the number of repetitions of a specific text.