How to Count Cells with Text in Google Sheets (3 Examples)


You can use the following methods to count cells with a particular text in Google Sheets:

Method 1: Count Cells with Any Text

=COUNTA(A1:A10)

Method 2: Count Cells with Specific Text

=COUNTIF(A1:A10, "Text")

Method 3: Count Cells with One of Specific Texts

=ArrayFormula(SUM(COUNTIF(A:A,{"Text1", "Text2"})))

The following examples show how to use each method with the following dataset in Google Sheets:

Example 1: Count Cells with Any Text

We can use the following formula to count the number of cells with any text in column A:

=COUNTA(A2:A12)

The following screenshot shows how to use this formula in practice:

We can see that there are 8 cells with any text in column A.

Example 2: Count Cells with Specific Text

We can use the following formula to count the number of cells with the text “A” in column A:

=COUNTIF(A2:A12, "A")

The following screenshot shows how to use this formula in practice:

We can see that there are 2 rows with the text “A” in column A.

Example 3: Count Cells with One of Specific Texts

We can use the following formula to count the number of cells with the text “A” or “B” in column A:

=ArrayFormula(SUM(COUNTIF(A:A,{"A", "B"})))

The following screenshot shows how to use this formula in practice:

We can see that there are 5 rows with the text “A” or “B” in column A.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

Google Sheets: How to Filter for Cells that Contain Text
How to Use COUNTIF with OR in Google Sheets
Google Sheets: How to Use SUMIF with Multiple Columns

2 Replies to “How to Count Cells with Text in Google Sheets (3 Examples)”

  1. Hi! I will be very grateful if you can help me. Column A lists the names of the categories. They are separated by semicolons, for example, “red; yellow; green”. In column B, you need to count how many times each of the categories occurs in column A. I can’t figure out how to write the formula.

    1. Hi Vlada…Certainly! To count the occurrences of each category listed in column A in Google Sheets, you can use a combination of array formulas and the `SPLIT` and `QUERY` functions. Here’s a step-by-step guide:

      1. **Split the Categories**: Create a helper column (say, Column C) that splits the categories into individual cells.

      2. **Count the Occurrences**: Use the `QUERY` function to count the occurrences of each category.

      ### Step-by-Step Instructions:

      #### Step 1: Split the Categories
      1. In cell C1 (or any other column where you want to create a helper column), enter the following formula:
      “`excel
      =ARRAYFORMULA(FLATTEN(SPLIT(A1:A, “; “)))
      “`
      This will split all the categories in column A into individual cells in column C, separated by semicolons.

      #### Step 2: Count the Occurrences
      2. In column B, use the `QUERY` function to count the occurrences of each category:
      “`excel
      =QUERY(C1:C, “SELECT C, COUNT(C) WHERE C IS NOT NULL GROUP BY C LABEL COUNT(C) ‘Count'”)
      “`

      ### Example

      Assuming your data looks like this:

      “`
      Column A
      red; yellow; green
      red; green
      yellow; green; green
      “`

      After splitting the categories and counting occurrences, the result should be:

      “`
      Column B
      red 2
      yellow 2
      green 4
      “`

      Here is a more detailed breakdown:

      1. **Column C (Helper Column)**:
      “`
      red
      yellow
      green
      red
      green
      yellow
      green
      green
      “`

      2. **Column B (Count Occurrences)**:
      “`
      red 2
      yellow 2
      green 4
      “`

      ### Detailed Formula Explanation:

      – `ARRAYFORMULA`: Allows you to apply a function to a range of cells.
      – `FLATTEN`: Converts a range of cells into a single column.
      – `SPLIT`: Splits the text string in each cell of column A by the semicolon separator.
      – `QUERY`: Performs a SQL-like query to group and count the occurrences of each category.

      This should help you achieve the desired result in counting how many times each category occurs in column A. If you have any further questions or need additional clarification, feel free to ask!

Leave a Reply

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