Google Sheets Counting Cells With Text (Quick Guide)

Posted on by Luke

If you ever need to count cells with text in Google Sheets, there’s a simple way to do it. Just use the COUNTA function!

In this post, I’ll walk you through how to count cells with text in google sheets and count the number of cells with specific text or values along with a couple of FAQs.

How to Count Number of Cells with Text in Google Sheets

The only way to determine how many times a word appears in Google Sheets is by utilizing a formula; it cannot be done through the toolbar or keyboard shortcuts.

The Google Sheets formula to count cells with text (or numbers) is COUNTA. Here’s its syntax and a quick breakdown:

=COUNTA(value1, [value2, value3, ...])

Where:

  • =COUNTA is the formula
  • value1 is the range of cells you want to be checked
  • [value2, value3, ...] and so on are optional additional cell ranges. We won’t use these most of the time.

For example, if you want Google Sheets to count how many times a word appears in A and B Columns, the formula is going to look like this:

=COUNTA(A1:B)

The screenshot shows two things. First arrow point to the place where we can provide our COUNTA function. Second arrow points on the cell where the output of counting items in cells is visible

So, all you have to do to count cells with text is to select a cell you want the results in and type this formula with your cell range.

How to Count Cells With Specific Text in Google Sheets

If you only want to count the number of cells that contain specific text, you have to use the COUNTIF function.

Here’s what the COUNTIF syntax looks like –

=COUNTIF(range, criterion)

Just like the other formula:

  • the range is the range of cells
  • criterion is text or pattern you want to search for within the selected range.

For example, if you want to count the number of cells that contain the text “pizza” in A and B columns you would type this into the cell where you want the results to appear:

=COUNTIF(A1:B,"pizza")

The screenshot shows two things. First arrow points to the place where the COUNTIF function is used to count all 'pizza' word occuriencies. Second arrow points to the cell where we have the sum of all 'pizza' words in our excercise doc (its 5)

Keep in mind that you have to always use double quotation marks for the criterion. And if you have more than a single value to check, you’d have to use the COUNTIFS function instead.

How to Count Cells With Specific Text Inside Words

You can even use this formula to count cells that have specific text inside a word in a cell using wildcards.

For example, if you want to count cells that have the letters “pi” in them (that would cover pizza, pies, pistachios, pickles, and pita bread), you’ll have to arrange the formula to look like this: 

=COUNTIF(A1:B,"*pi*")

The asterisks make Google Sheets substitute them for any text and check if the letters “pi” appear anywhere inside the words in cells and count the times they appear. 

The screenshot shows two things. The first arrow points to the place where we use the COUNTIF function to use a wildcard to search all words that have a 'pi' string in them. The second arrow points to the cell where we can see the output of the COUNTIF function which is 8 words

How To Count Number Of Cells With Specific Value In Google Sheets 

You can also use the COUNTIF formula to count cells that have specific values, like if there are cells that have a value greater than 100, or a number of cells that has a number lower than 20.

It’s really simple to do this and all you need to do is to use Google Sheets comparison operators instead of specific text in the criterion.

These operators are “<“, “<=”, “>”, “>=”, “=” “<>”.

For example, if you want to count the cells that have a value larger than 50 in A and B columns, the formula is going to be:

=COUNTIF(A1:B,">50")

The screenshot shows two things. First arrow points to the COUNTIF function which is going to count all numbers that are higher than 50. Second arrow points on the cell where the output is visible. The value of the E1 cell is 7

Some Questions You May Still Have

Can You Use Countif With Text?

Yes, you can use the COUNTIF function with text. You can even use COUNTIF with text wildcards! Just remember to enclose the text in double-quotes.

How Do I Count Cells Based On Text In Another Cell?

You can count cells based on text in another cell using the COUNTIF function as well, however, if there are 2 or more values to check, you’ll have to use the COUNTIFS function.

Let’s take a look at how to do it based on just one value:

In this example, you want to count the cells in the A and B columns that match the text in cell E1. Here’s what the formula would look like:

=COUNTIF(A1:B,E1)

Gif shows how we can count cells based on the text in another cell. The value in E1 cell is changing from 'pizza' to 'lemon' to 'blueberry'. The E2 cell shows the count of the words in A and B columns

If you want to count the number of cells based on multiple criteria, here’s how the COUNTIFS formula would like:

=COUNTIFS(A1:A10, ">20", B1:B10, "<30")

The screenshot shows two things. First arrow point to the COUNTIFS function that's going to count all numbers that are higher than 20 in A column and numbers that are lower than 30 in B column. Second arrow points to the D1 cell where we can see 3 as the output of the function

This will count all numbers that are higher than 20 in the A column and all numbers that are smaller than 30 in the B column

The formula then outputs the sum of both the counts of the two ranges.

My Final Thoughts

Counting cells with text or value in Google Sheets is an easy process. All you have to do is use either the COUNTIF or the COUNTIFS function to get a result.

You’ll just need to specify the range of cells and enter the text/value you want to count. And if you’re counting cells with specific text, you can use wildcards to match the letters inside words.

The COUNTIF formula is also great for counting cells based on text in other cells as well. You’ll just need to specify the range and enter the cell reference from where you want it to get its value.

And if you’re counting cells with two different values, you can use the COUNTIFS function to get the total count of both.

So start counting and make sure to check out other Google Sheets tutorials to learn more powerful formulas and functions!

Leave a Reply

Your email address will not be published. Required fields are marked *