If you ever need to count cells with text in Google Sheets, there’s a simple way to do it. Just use the
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, ...])
=COUNTAis the formula
value1is the range of cells you want to be checked
and so on are optional additional cell ranges. We won’t use these most of the time.
[value2, value3, ...]
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:
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
Here’s what the COUNTIF syntax looks like –
Just like the other formula:
rangeis the range of cells
criterionis 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:
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:
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.
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:
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:
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")
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!