How to Count Cells with Text in Excel

Excel spreadsheets are a good way to retailer and analyze information, and so they typically have cells with a mixture of numbers and textual content. To grasp your information extra, it’s good to differentiate the cells with textual content. This course of is rather a lot simpler than it sounds.

How to Count Cells with Text in Excel

This text explains find out how to choose a cell vary after which use the “COUNTIF” perform to rely the cells containing textual content. Let’s get began!

Learn how to Depend Cells with Textual content in Excel utilizing Home windows, Mac, or Linux

The method to rely cells that include textual content inside your Excel spreadsheet on Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel 365 in Home windows, Mac, or Linux is similar. Right here’s find out how to do it.

  1. Click on on any “empty cell” inside your spreadsheet to insert the formulation.
  2. Sort or paste the perform =COUNTIF (vary, standards) with out quotes, which counts the variety of cells containing textual content inside a selected cell vary.
  3. For “vary,” enter the cell vary you wish to verify. Sort the primary and final cells separated by a colon. For instance, to rely cells A2 to A9, you’d enter the next textual content string: “A2:A9.”
  4. For “standards,” kind "*" with quotes. This half counts the variety of cells that include textual content throughout the specified vary. The entire formulation ought to seem like the next: “=COUNTIF (A2:A9, “*”).”
  5. Now, press “Enter” to use the formulation. The consequence will show within the formulation’s cell.

Learn how to Depend Cells with Textual content in Excel on the iPhone App

To rely the variety of cells containing textual content inside your spreadsheet utilizing the Excel app on an iPhone, do the next:

  1. Launch the “iPhone Excel App.”
  2. Faucet on “Open” to view your saved spreadsheets, then choose the particular “Excel file” to open it.
  3. “Double-tap” on an “empty cell” within the spreadsheet to enter the “COUNTIF” formulation, or you’ll be able to “long-press” an “empty cell” after which faucet “Edit” from the pop-up menu.
  4. Within the empty cell, kind within the following: “=COUNTIF (vary, standards).” This formulation counts the variety of cells containing textual content inside your cell vary.
  5. For the “vary” half, kind the “cell vary” you wish to rely. Enter the primary and final cells separated by a colon. To calculate the variety of cells inside D2 to D12, kind the next: “D2:D12.”
  6. For the “standards,” kind “*” with quotes. This half counts the variety of cells with textual content within the vary. The entire formulation ought to look one thing like the next: “=COUNTIF (D2:D12,“*”).”
  7. Now, faucet “Enter” to use the created formulation. The consequence seems within the formulation’s cell.

Learn how to Depend Cells with Textual content in Excel on the Android App

To rely the variety of cells which have textual content in your spreadsheet utilizing the Android Excel app, do the next:

  1. Launch the “Android Excel app.”
  2. The “Latest” menu opens by default. Faucet on “Open” within the bottom-right part to open the “Locations” menu, which is sort of a file browser.
  3. Browse and faucet on the specified file to open it robotically.
  4. “Double-tap” on an “empty cell” to enter the “COUNTIF” formulation. Alternatively, “long-press” any “empty cell,” then faucet “Edit” from the pop-up menu.
  5. Enter the cell vary you want to rely for the “vary” a part of the formulation: “=COUNTIF (vary, standards)” with out quotes. This formulation calculates the variety of cells with textual content contained in the cell vary.
  6. Enter the cell vary you want to rely for the “vary” a part of the formulation. Embrace the primary and final cells separated by a colon. To calculate cells E2 to E12 from one column, enter the next textual content string: “E2:E12.”
  7. For the “standards” a part of the formulation, kind “*” with quotes. This a part of the textual content string counts the variety of cells with textual content within the specified vary, together with multiple row. Your full formulation ought to look one thing like this: “=COUNTIF (A2:E12,“*”).”
  8. Now faucet “Enter” to use the formulation. The consequence seems within the formulation’s cell.

Learn how to Depend Cells with Particular Textual content in Excel

Through the use of the “COUNTIF” perform, you’ll be able to rely the variety of cells that include particular textual content strings, similar to “Excel,” “John,” or “John Meyers.” The formulation is much like counting cells with any textual content, however you modify the formulation’s “standards” half to seek for particular textual content. On this instance, you’ll see the variety of occasions the phrase “Excel” seems in a selected cell vary:

  1. Click on on an “empty cell” to kind the formulation.
  2. Within the empty cell, kind the next string with out quotes: “=COUNTIF (vary, standards).”
  3. Enter the cell vary you want to rely for the “vary” a part of the formulation. Enter the primary and final cells separated by a colon. To calculate cells A2 to A20, kind the next string with out quotes: “A2:A20.”
  4. For the “standards” part of the formulation, kind "Excel" with quotes. This a part of the textual content string counts the variety of cells with “Excel” within the specified vary. Your accomplished textual content string ought to seem like the next: “=COUNTIF (A2:A20, "Excel").”

Learn how to Depend Cells with Duplicate Textual content in Excel

Apart from counting cells with textual content and particular textual content, you’ll be able to rely the variety of cells with duplicate content material.

Within the following instance, we’re searching for duplicate pupil grades.

  • Column A – lists college students (A2:A10)
  • Column B – lists pupil’s grades (A, B, or C)
  • Column D –lists out there grades, together with D2 for “A,” D3 for “B,” and D4 for “C.”
  • Column E – lists the rely of every grade.

Depend Cells with Duplicate Textual content, Together with First Occasion

To rely the variety of cells in your spreadsheet with situations of grade “A,” “B,” or “C,” together with the primary occasion, enter the next formulation:

  1. For the grade letter “A,” click on cell “E2” and kind the next textual content string: “=COUNTIF(B2:B10,D2).”
  2. For situations of grade “B,” click on cell “E3” and kind the next textual content string: “=COUNTIF(B2:B10,D3).”
  3. For situations of grade “C,” click on cell “E4” and kind the next: “=COUNTIF(B2:B10,D4).”

You now have the rely for duplicate grades, together with the primary occasion listed in column “E.”

Learn how to Depend Cells with Colour Textual content in Excel

Excel doesn’t have a selected formulation to rely cells primarily based on their textual content shade. To get round this, filter the outcomes after which calculate the cells.

  1. Open the spreadsheet you want to analyze.
  2. Proper-click a cell with the textual content of the colour you want to rely.
  3. Choose “Filter,” then “Filter by Chosen Cell’s Font Colour” to filter the cells with the chosen textual content shade.
  4. Subsequent, inform Excel to rely your information vary. In case your textual content fills cells from B2 to B10, enter the next formulation: “=SUBTOTAL(B2:B10).”

When you hit “Enter” to use the filter, Excel will solely show the cells containing that shade and conceal the remaining values.

The “SUBTOTAL” perform solely returns the rely for the chosen textual content shade and excludes the values within the hidden rows in comparison with “SUM.”


In closing, the Excel software does an excellent job of storing your information and facilitating evaluation. It handles textual content in addition to numbers. It has over 4 hundred capabilities, together with “COUNTIF.” This perform helps discover the overall of cells containing particular info, like cells with textual content, and the variety of occurrences for specific textual content strings each time it’s good to type by means of a bunch of knowledge to search out what you want.

Leave a Reply

Your email address will not be published. Required fields are marked *

Press ESC to close