Count If Contains in Google Sheets: Your Ultimate Guide

SenseBridge

2 min read

·

24-10-2024

2

0

Share

Google Sheets is a powerful tool that allows users to manipulate data effectively. One of the most useful functions available in Google Sheets is the COUNTIF function, which helps to count the number of cells that meet a specific condition. In this guide, we’ll focus on how to count cells that contain specific text using the COUNTIF function. Let’s dive in! 🚀

Understanding the COUNTIF Function

Before we explore how to count cells that contain certain text, it's important to understand the syntax of the COUNTIF function:

COUNTIF(range, criterion)

• range: The group of cells that you want to count.
• criterion: The condition that you want to apply to count the cells.

Basic Example of COUNTIF

Let's say you have a list of fruits in a column, and you want to count how many times the word "Apple" appears:

   A
1  Apple
2  Banana
3  Apple
4  Orange
5  Grape

To count the number of "Apple," you would use:

=COUNTIF(A1:A5, "Apple")

This formula would return 2, as "Apple" appears twice.

Counting Cells That Contain Text

To count cells that contain a specific substring rather than an exact match, you can use wildcards with the COUNTIF function. Wildcards are special characters that help you match patterns.

Wildcards in COUNTIF

There are two wildcards you can use:

• Asterisk (*): Represents any number of characters.
• Question mark (?): Represents a single character.

Example of Using Wildcards

Suppose you have the following data:

   A
1  Apple Pie
2  Banana Bread
3  Cherry Tart
4  Apple Strudel
5  Grape Juice

If you want to count how many items contain "Apple", you would use:

=COUNTIF(A1:A5, "*Apple*")

This will return 2, as there are two entries that contain the word "Apple".

Example Table of COUNTIF with Wildcards

Here's a quick reference table to see how different wildcards can be applied with the COUNTIF function:

Note: Remember that COUNTIF is case-insensitive, so it doesn't differentiate between "apple" and "Apple".

Case-Sensitive Count (Alternative Method)

If you need to count cells based on case sensitivity, you'll need to use the ARRAYFORMULA combined with EXACT. Here’s how you can do that:

For counting "Apple" case-sensitive, use:

=SUM(ARRAYFORMULA(EXACT(A1:A5, "Apple")))

This formula will only count cells that exactly match "Apple" in terms of case.

Conclusion

Now that you have a comprehensive understanding of how to use COUNTIF to count cells containing specific text, you can apply this knowledge to your data analysis in Google Sheets. Whether you are managing your personal finances, tracking inventory, or analyzing survey data, the ability to count cells based on their content is invaluable. Happy counting! 📊