Count Cells Containing Text in Google Sheet

Conditional counts are frequently required whenever you are trying to draw insights from a big dataset. One such instance is when you want to count cell any text/specific text across a group of cells.

While you can use Google Sheet Filters to easily get it done, there are also smart ways to getting this done using formula.

In this tutorial, we’ll cover all these questions in detail. Stay Tuned.

Count Cells Containing any Text in Google Sheet

If you are just looking to count cells which contain any kind of text entries and ignore the rest then this section is for you.

To get this done, we will be using COUNTIF Function along with Wildcard Character(*).

Say, you have the below data range, from which you want to count only cells containing any text(highlighted ones).

Sample Data of different data types - Google Sheet

We will use the below formula to get the results:

=COUNTIF(B3:B11,"*")
Counting Cells containing Cells in Google Sheet

As you can see above, we exactly got the result(3) we wanted. Let’s now breakdown the formula to understand what’s going on behind the curtains.

Firstly, COUNTIF Function is meant to return conditional counts. It takes in a range(B3:B11) and a criterion(“*”). Only cells in the range that fullfill/pass the criterion will be counted.

In above case choice of range is a no-brainer. Criterion is “*”, which is nothing but a wildcard character. * simply represents a group of characters. Hence, all cell which had any group of characters were counted.

Count Cells Containing Specific Text in Google Sheet

Taking a step forward lets try to now count cells only containing a specific text(wholly/partially).

In the below example we want to find all cells containing “Apple”. We have again called out COUNTIF to the rescue. This time the criterion will be “Apple” as we are just looking to count cells containing “Apple”.

Count Cells containing specific text in Google Sheet

Note: The text matching is not case-sensitive, hence an Apple, apple or APPLE would give the same results.

So what happens, when you need a count, but you want to just check a partial match of a text.

Say, in below example you want to count all entries containing apple(highlighted ones).

Count Cells containing specific text in Google Sheet

As you can see, we made a minor modification to the previous formula. Now the critetion is “*Apple*”. Appending the wildcard character asterisk(*), means that any number of characters can come before/after the word “Apple”.

This effectively converts the criterion from a specific match to a partial match.

Visual Demo of using COUNTIF

Before we end the topic lets see how to implement the above solutions in action.

That’s it on this topic. Keep browsing SheetsInfo for more such useful information 😊

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Liked the content?? Then share the word!!!

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply