How To Count Cells With Text in Google Sheets

Did you know you don't need to manually scan through rows of data to count cells with text in Google Sheets?

This is a vital skill that can save you valuable time. Google Sheets offers several built-in functions like COUNTIF and SUMPRODUCT to make this process seamless. In this guide, you'll learn multiple ways to count cells with text in Google Sheets, from basic formulas to advanced functions.

Text vs. Non-Text Cells: What Counts?

Before learning specific formulas, you need to understand what "text" means in Google Sheets:

• Text includes any non-numeric values, such as words, letters, and symbols.

• Cells containing spaces or empty strings ("") from formulas may also be counted as text.

• Numbers formatted as text will still be considered text.

• Blank cells are not counted unless explicitly included in the function.

Now, let's go through the methods to count text cells in Google Sheets.

Ways to Count Cells With Text in Google Sheets

You count cells with text values in Google Sheets in various ways. Below, we'll explore various approaches:

1. Using the COUNTA function.

The simplest way to count non-empty cells is by using the COUNTA function. However, this Google Sheets function counts cells with text and numbers. So, it isn't ideal for situations where only text cells are needed.

Formula: =COUNTA(range)

How it works:

• COUNTA counts all non-blank cells in the specified range.

• It includes both text and numbers but doesn't count blank cells.

Example:

If we use =COUNTA(A1:A5), the result will be 4 (excluding the blank cell).

2. Using the COUNTIF function (Recommended).

The COUNTIF function is the right option for counting only cells that contain text (excluding numbers and blank cells). So, it is an upgrade on the COUNTA function. See the COUNTIF function syntax below:

Formula: =COUNTIF(range, "*")

How it works:

• COUNTIF counts only the cells that meet the specified criterion.

• The "*" wildcard ensures only text-based entries are counted (excluding numbers and blanks).

• This is the best approach when you want to exclude numeric values from the count.

Example:

The formula excludes the numbers and blanks.

3. Using SUMPRODUCT and ISTEXT for higher accuracy.

For more accuracy, you can combine SUMPRODUCT with ISTEXT. This ensures higher accuracy. You can use it when you have mixed data types and want a strict count of text-only cells.

Formula: =SUMPRODUCT(--ISTEXT(range))

How it works:

• ISTEXT(range): Returns TRUE for text cells and FALSE for non-text cells.

• --ISTEXT(range): Converts TRUE to 1 and FALSE to 0.

• SUMPRODUCT then sums up the values, giving an accurate count of text cells.

Example:

Using =SUMPRODUCT(--ISTEXT(A1:A4)), the result will be 2, as only two cells contain text.

Advanced Techniques for Counting Cells in Google Sheets

Some cell counting tasks in Google Sheets may require advanced techniques. Good examples include when you are dealing with multiple conditions, partial matches, or case-sensitive text. In this section, you will learn some advanced cell counting techniques.

1. Counting cells that contain specific text.

You can count cells containing specific text with the COUNTIF Google Sheets function. See what the formula looks like below:

Formula: =COUNTIF(range, criteria)

• range – The data range where you want to count occurrences.

• criteria – The text you’re looking for. Remember that this formula only counts exact matches.

Example:

Suppose you have sample data of fruits in column A (A2:A10). To count how many times "Apple" appears in column A, use:

=COUNTIF(A2:A10, "Apple")

The result is 3 since the exact match "Apple" appears thrice.

2. Counting cells that contain partial text.

If you need to count cells that contain a word but may have other text as well (e.g., "Green Apple" or "Apple Juice"), you can use wildcards in COUNTIF. This can be useful for analyzing customer reviews where a keyword appears in different contexts (e.g., counting mentions of "great" in feedback comments).

Wildcard symbols in Google Sheets:

Example:

This counts any cell that contains "Apple" as part of a longer string, like "Green Apple" or "Apple Juice".

3. Counting cells based on multiple criteria.

The Google Sheets COUNTIFS function is the best option for counting cells based on multiple conditions. For instance, you can execute this conditional count to filter results based on both product name and category when tracking sales data.

Example:

Suppose you have a dataset with the categories "Product" and "Category." To count only "Apple" in the "Fruit" category, use:

=COUNTIFS(A2:A10, "Apple", B2:B10, "Fruit")

The output is 3 since three Apples belong to the "Fruit" category.

4. Counting case-sensitive text using SUMPRODUCT and EXACT function.

Google Sheets formulas like COUNTIF and COUNTIFS are not case-sensitive. Hence, "Apple" and "apple" are treated the same. This can be problematic when working on case-sensitive inventory tracking or unique case-based identifiers.

Example:

To distinguish between "Apple" and "apple," use this formula:

=SUMPRODUCT(--EXACT(A2:A7, "Apple"))

Final Thoughts

Counting cells with text in Google Sheets doesn't have to be a strenuous task. Whether you're handling simple counts or applying advanced techniques for partial matches and multiple criteria, these formulas in this article can help. So, carefully review the article to find the solution that suits your needs.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ

1. Can I count only unique text values in Google Sheets?
Yes, you can count unique text values by combining the COUNTIF and UNIQUE functions like this: =COUNTA(UNIQUE(A2:A10)). This ensures only distinct text entries are counted, ignoring duplicates.

2. Why is my COUNTIF formula not working correctly?
Check if your criteria use the correct syntax, especially when counting partial text (use wildcards like =COUNTIF(A2:A10, "Apple*")). Also, ensure your data doesn’t contain unintended spaces or number formatting issues.

3. How do I count case-sensitive text in Google Sheets?
Use the SUMPRODUCT and EXACT functions like =SUMPRODUCT(--EXACT(A2:A10, "Apple")) to distinguish between uppercase and lowercase entries. This method ensures "Apple" and "apple" are treated as different values.

Related Articles

How to Enter New Line in a Cell in Google Sheets

How to Freeze a Column in Google Sheets

How to Change Cell Size in Google Sheets