5 Ways To Use Countif With Text In Google Sheets

Using Google Sheets can be a game-changer for organizing and analyzing data, especially when it comes to working with text. One of the most useful functions in Google Sheets is COUNTIF, which allows you to count the number of cells that meet specific criteria. In this article, we'll explore 5 ways to use COUNTIF with text in Google Sheets, along with practical examples and tips to help you get the most out of this powerful function.

Understanding COUNTIF with Text in Google Sheets

Before we dive into the examples, let's take a look at the basic syntax of COUNTIF:

COUNTIF(range, criterion)

Where:

• range is the range of cells you want to count
• criterion is the condition you want to apply to the range

When working with text, the criterion can be a specific word or phrase, a partial match, or even a regular expression.

1. Counting Cells with a Specific Word

Let's say you have a list of countries in column A, and you want to count how many cells contain the word "USA". You can use the following formula:

=COUNTIF(A:A, "USA")

This will count all cells in column A that contain the exact word "USA".

Example:

Country
USA
Canada
USA
Mexico
USA

Using the formula above, the result would be 3, since there are three cells that contain the word "USA".

2. Counting Cells with a Partial Match

What if you want to count cells that contain a partial match, such as cells that contain the word "US" anywhere in the text? You can use the * wildcard character to achieve this:

=COUNTIF(A:A, "*US*")

This will count all cells in column A that contain the letters "US" anywhere in the text.

Example:

Country
USA
Canada
US Virgin Islands
Mexico
USA

Using the formula above, the result would be 3, since there are three cells that contain the letters "US" anywhere in the text.

3. Counting Cells with a Specific Phrase

Let's say you have a list of text in column A, and you want to count how many cells contain the exact phrase "New York". You can use the following formula:

=COUNTIF(A:A, "New York")

This will count all cells in column A that contain the exact phrase "New York".

Example:

Text
I love New York
New York is great
I hate New York
London is nice

Using the formula above, the result would be 3, since there are three cells that contain the exact phrase "New York".

4. Counting Cells with a Regular Expression

Google Sheets also supports regular expressions in the COUNTIF function. Let's say you want to count cells that contain any word that starts with the letter "N". You can use the following formula:

=COUNTIF(A:A, "^N.*")

This will count all cells in column A that contain any word that starts with the letter "N".

Example:

Text
I love New York
Nice to meet you
London is nice
Hello world

Using the formula above, the result would be 2, since there are two cells that contain a word that starts with the letter "N".

5. Counting Cells with Multiple Criteria

Finally, let's say you want to count cells that meet multiple criteria. For example, you want to count cells that contain the word "US" and also contain the word "A". You can use the following formula:

=COUNTIFS(A:A, "*US*", A:A, "*A*")

This will count all cells in column A that contain both the word "US" and the word "A".

Example:

Country
USA
Canada
US Virgin Islands
Mexico
USA

Using the formula above, the result would be 2, since there are two cells that contain both the word "US" and the word "A".

Gallery of COUNTIF with Text in Google Sheets

In conclusion, the COUNTIF function in Google Sheets is a powerful tool for counting cells that meet specific criteria, especially when working with text. By using the examples and tips outlined in this article, you can unlock the full potential of COUNTIF and take your data analysis to the next level. So, what are you waiting for? Start exploring the world of COUNTIF and discover the possibilities!