COUNTIF any text

Google Sheets – Count cells with text only – not numbers

If you’re using Google sheets and you want to count the number of cells in a range that have text in them, as in text and not a number value, there’s a relatively easy way to do it. But, there are some hiccups with it and so we’re going to go through the easy way and a way which is a little bit more complicated but is more accurate. This tutorial will show you why each one works and which to use. 

COUNTIF any text
COUNTIF any text



The COUNTIF may be all you need

Column B has, we’re not going to count the header, two cells with text in it and we got that by using the function COUNTIF. The syntax is =COUNTIF(B3:B8,"*") which counts any cells with characters in it. That’s what this wildcard character * means. You use the quotes to let Google Sheets know that it’s a character and the asterisks is a special character that means anything. So, this is counting cells if there’s anything. However, if you take a looked at the next column in the live spreadsheet or in the next image, you can see the value is 3 and not 2.

Watch the video



Dealing with empty spaces

COUNTIF unless empty space 
COUNTIF unless empty space


If you’re using the simple COUNTIF formula and getting a higher number than it should be, you may have some cells that have a space in them. They have a value, but you can’t see it. In the image above, C8 has one empty space in it. If you want to count that then you’re done here. The COUNTIF function with the asterisk is all you need. But, if you don’t want to count empty spaces, then you can use the function that we have in C10, =COUNTIFS(C3:C8,"*",C3:C8,"<> "). COUNTIFS means count if but plural so there are multiple criteria to consider. The first part is the same COUNTIF if there’s any character. But, we are saying also if it’s not just a blank space. When you put these two together in this compound COUNTIFS function, it doesn’t count the blank space that’s in cell C8.

So, column B is the easy way if you don’t have blank spaces. But, if you do have spaces, you want to use COUNTIFS. Keep in mind though, that the COUNTIFS above is just skipping cells with one space, you will have to extend the function if you have cells with multiple spaces.

Last minute reminders

A few things to remember are if the cell has a true/false value that’s not going to count. If the cell starts with a single quote, no matter what it has, that will count. Numbers are not counted by this function unless they’re entered as text. So another way to enter a number as text is to do the single quote and then type 333. That’s going to be counted because it’s not really a number, it’s the word if you will 333. I hope that’s helpful. Thanks!

 Live examples in Sheets

Go to this spreadsheet for examples of COUNT that you can study and use anywhere you would like.



Leave a Reply