Google Sheets Count Cells with Specific Text

Hello and welcome to TeqTog! In this article, I will try to teach you how you can count cells in your Google Sheets which contain specific texts in them.

More often than not, you will need to count all the sheets in your Google spreadsheet which contain a specific text. It is in those cases when knowing how to use the formula for counting cells in Google Sheets with texts come in handy.

Let’s get started!

Count Cells in Google Sheets

Count cells in Google Sheets is a function available in Google Sheets which does what the name suggests: it enables you to identify the number of times a certain text has appeared in your spreadsheet.

By using this useful function, you can identify the number of cells which contain an exact, certain text, do not contain any text or the number of cells which partially contain a text.

Counting cells with texts can mean texts that are information of names or dates.

How to Count Cells That Contain a Specific Text

Let me give you an example where I have a list of names of people and, in that list, some of the names are recurring. So, to find out how many cells have a certain name in my set of data, I will need to count my cells with that name.

Suppose this is my set of data:

How to Count Cells That Contain a Specific Text

I need to know the number of times the name (text) “Seema” appears in my data.

The syntax for the formula for counting cells in Google Sheets with text is as given below:

COUNTIFIF (range, criterion)

For the range, I will give the cell range from B2 till cell B9; because that is where I want Google Sheets to do the counting. Moreover, for the criterion, I will give “Seema”; because that is the text I am counting in my database. If the criterion “Seema” is matched, then Google Sheets will count the number of times the criterion has occurred. This is a kind of conditional counting.

Therefore,

=COUNTIF(B2:B9,"Seema")

What this formula implies is, my spreadsheet will search the entire column “B” from cell no. B2 to B9 to look for the text “Seema”. Afterwards, it will report the number of times that text has appeared.

1. You have to select the cell in which you want your answer. Then, type in the formula for the counting cell function I have shown earlier.

Google Sheets Count Cells with Specific Text
Count Cells That Contain a Specific Text - COUNTIF function result

In the example shown, I have entered the expression “Seema” in the formula in double quotes manually; however, you can select a cell if it contains the same text.

The criterion in this formula is not sensitive to cases. Therefore, I could have entered either “Seema” or “SEEMA” and, the function would have still generated the same response.

The formula used in this example is for any kind of text.

How to Count Cells That Do Not Contain a Specific Text

What if you need to know the number of cells which does not contain a certain text? You may need this result for analytical purposes or comparison purposes. This is where you need to how you can use the count cells with no text function in Google Sheets.

The formula for this function is essentially the same as count cells with text, with just a little alteration to it.

The syntax for the count cells with no specific text function in Google Sheets is:

=countif(B2:B9,"<>Seema")

The first augment in this formula is again the range. So, my spreadsheet will do its task within this cell range. In addition, it will only look for the cell which does not contain the text “Seema” and report the number of cells.

Notice that before the expression “Seema” there is a “<>” symbol which stands for “not equal to”. This is the same as a cell ‘not containing” a certain value.

Select a cell where I want my results and give the input for my formula.

How to Count Cells That Do Not Contain a Specific Text
Example Results of Count Cells That Do Not Contain a Specific Text

How to Count Cells with Partial Match Text in Google Sheets

In the two instances I have shown you so far, the search for texts are being done within those cells which contain just that text in it entirely. There are no other texts in those cells.

For instance, I have searched for cells with “Seema” and cells without “Seema” and, each cell in my database had just only one text in them. But what if, now my cells contain more than one text and “Seema” is a partial text?

Then, the syntax for this formula will be:

=COUNTIF(B2:B9,"*Seema*")

I have enclosed the relevant text in asterisk symbols.

The function of the asterisk is that, when you give this symbol before and after a character or a number in a formula; the function will consider every value with that character or number, even if there are multiple other characters or numbers present before and after it.

How to Count Cells with Partial Match Text in Google Sheets
Count Cells which contains Partial Match Text in Google Sheets
Count Cells which contains Partial Match Text - TeqTog Example Results

Disadvantage of the Count Cells with Partial Text in Google Sheets

As I have mentioned earlier, when using the asterisk symbol, Google Sheets will consider any value which has the relevant text in it.

I hope my article will help you in the future. Best of luck!

You May Like to Check More Google Sheets Tutorials on:

How to Delete Blank Rows in Google Sheets

Conditional Formatting Based on Another Cell in Google Sheets

Does Not Equal in Google Sheets (Symbol <>)

How to Search IF Cell Contains in Google Sheets

Was this article helpful?
YesNo


Related More Articles

Leave a Comment