Tag: countif

COUNTIF any text

Google Sheets – Count cells with text only – not numbers

Posted on | by Prolific Oaktree | Leave a Comment on 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

 

Types of counts

 

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.

 

 

Google Sheets – Count cells containing specific text

Posted on | by Prolific Oaktree | 34 Comments on Google Sheets – Count cells containing specific text

If you’re using Google Sheets and you want to count the occurrences of a certain letter or word, there’s a pretty easy function that you can use.

Looking for a word and only that word

Using the COUNTIF function
Using the COUNTIF function

Types of counts

In this first set of data in the image above and also in this linked spreadsheet, we will be counting any cells that contain the word “Yellow” and only that word. To count the occurrences of the word yellow in the range B2 to B9 you can use the count COUNTIF function as such: =COUNTIF(B2:B9,"yellow"). It performs a conditional count. In this case, only if the cell or ranges of cells is equal to Yellow. Yellow is not case-sensitive so this is going to pick up three instances even though B7 is not capitalized. If the COUNTIF technique is doing everything you need, then you’re done and there is no need to try anything more involved.

Looking for a word occurring anywhere a cell

COUNTIF with wildcard
COUNTIF with wildcard

Looking at this second set of data, things will get a little bit more complicated. We are looking for a certain word that occurs anywhere in any of these cells. First, you want to use COUNTIF again and give it a range =COUNTIF(C2:C9, "*Yellow*"). For this example, the range will be C2 to C9. If it has the word yellow and anything before which is what the asterisk means, and anything after it which is the second asterisk, then it should be counted. It just has to have yellow in some part of it. Anything can be nothing so it can start or end with yellow too. This function is also counting 3 because of the yellow plane, the yellow car, and the little yellow boat.

Case-Sensitivity

The COUNTIF function is not case-sensitive. To count cells with specific capitalization, follow the second example in this tutorial.

Using a plugin

As an alternative to the options above, you may want a plugin to do the heavy lifting for you. I like to use a plugin called Power Tools. This will give you a menu option with, among other things, advanced Find and Replace features. This will give you a list of all the occurrences of a word in your spreadsheet, but it won’t give you a count of them. Depending on the size of the spreadsheet, this may be the preferable option.

The Find and Replace Menu
Find and Replace Menu

I hope that was helpful and now you know the formula for counting any occurrences of any word that you’re looking for.

 Live examples in Sheets

Go to this spreadsheet for examples counting specific text that you can study and use anywhere you would like.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

Column of numbers

Google Sheets – Count cells greater or less than an amount

Posted on | by Prolific Oaktree | Leave a Comment on Google Sheets – Count cells greater or less than an amount

If you’re using Google Sheets and you have a column of data in which you want to count the number of items that are greater or less than a certain amount, there are a couple of small nuances to it, but generally, it’s pretty easy.

Types of counts

Column of numbers
Column of numbers

Column of numbers with COUNT function
Numbers with a count

To perform this count using the data in the images above, we’ll be using the COUNTIF function in cell C11. This is a function that’s going to count a range if a certain criteria is met.

  • Place your cursor in cell C11 and type =COUNTIF(C3:C9,">30000"). The C3 is the start of the range and C9 is the end.
  • After you type the range into the function, enter a comma. That lets Google Sheets know that you are done with the range.
  • Then, enter the criteria surround by quotes. We’re going to do anything greater than 30,000. Don’t put a comma in your 30000 or it’s going to think it’s text instead of number.

After you entered the formula, you can see there are four items over 30,000 and this counted them. Easy as pie.

Video explanation


 Live examples in Sheets

Go tothis spreadsheet for examples of that you can study and use anywhere you would like.