How to Count Cells with Specific Text in Google Sheets

Google Sheets offers several COUNTIF and COUNTIFS formulas that allow you to efficiently track specific data. Whether you need to count cells containing a specific word, a character in a word, or a character pattern within cells, these functions can be extremely useful. This guide will help you understand how to use these functions effectively to meet your data analysis needs.

Using Wildcard Asterisk in COUNTIF

The COUNTIF function in Google Sheets can be combined with the wildcard asterisk (*) to find the number of cells containing a specific text. For example, if you want to count the number of cells containing the word Burger in a range such as B3:B12, you can use the following formula:

COUNTIF(B3:B12, "*Burger*")

In this example, the “*Burger*” is a pattern where the asterisks indicate that the search for "Burger" can be anywhere in the text. If you want to find how many cells contain the word Cake in the range B3:B16, specified in cell D8, you can use:

COUNTIF(B3:B16, D8)

Here, D8 contains the text "Cake". This formula will dynamically count the cells that contain "Cake" anywhere within the range B3:B16.

Counting Specific Sets of Characters

To count the number of cells in a range that contain a set of characters, you can use the COUNTIF function. For instance, if you want to count how many cells in the range A1:A99 contain the word yellow anywhere within them, you can use:

COUNTIF(A1:A99, "*yellow*")

The asterisks act as wildcards, instructing Google Sheets to look for the word "yellow" within any part of each cell in the range.

For a more precise count, you can use the same COUNTIF function but without the asterisks to ensure that only cells containing exactly “abc” in their entirety are counted. Thus, the formula:

COUNTIF(A1:A10, "abc")

will count cells that contain the exact string "abc" and exclude cells that contain "abc" as part of a larger string, like "abcd" or "xabc".

Using COUNTIFS for Multiple Criteria

If you need to count cells based on multiple criteria, you can use the COUNTIFS function. This function allows you to combine several conditions to narrow down your search.

For example, if you want to count cells in the range B1:B10 that contain the word “abc” and also contain the word “123” in the same cell, you can use:

COUNTIFS(B1:B10, "*abc*", B1:B10, "*123*")

This formula will count only the cells in the range where both conditions are met.

Conclusion

Google Sheets provides powerful functions like COUNTIF and COUNTIFS to help you efficiently analyze and count specific text patterns within your data. By utilizing wildcard characters and multiple criteria, you can tailor your search to meet the exact requirements of your data analysis. Whether youre a beginner or a seasoned user, these functions will enhance your data management capabilities, making it easier to extract valuable insights from your spreadsheets.