COUNT is a basic function that tallies the number of items in a range of cells. But what if you want to count items that meet certain criteria? In that case, you’re better off using the COUNTIF Google Sheets function. Essentially, it combines both the COUNT and IF functions, allowing for more complex enumerations with a simpler formula.
In this guide, we’ll walk you through all there is to know about the COUNTIF function, along with some common situations where it can be used and a few examples to make it easier to wrap your head around.
What Exactly Does The COUNTIF Google Sheets Function Accomplish?
COUNTIF in Google Sheets enumerates the number of items in a selected range of cells that fall under a particular condition.
It’s a great option to use if you’re looking to combine the situational conditioning of IF and the straightforward operation of the Google Sheet COUNT function without having to resort to the more complex scenario of adding each function individually.
Keep in mind, however, that COUNTIF only returns a conditional count that meets one designated criterion, meaning the cells you want to be counted have to meet your condition precisely.
But that’s not to say that the condition can’t be complex. A few examples of COUNTIF usage include counting all the cells that include a particular text or those that are more than, less, than, or equal/not equal to a specified number.
Syntax Of The COUNTIF Function
If you look up the COUNTIF Google function in a spreadsheet, you’ll find the COUNTIF syntax below:
- Range: This is the span of cells that you need to designate that includes the data you would like to potentially count.
- Criterion: This is the scenario that the cells need to fall under in order to be included in the count.
The range you specify can include text, numbers, or a mixture of both. The criterion, on the other hand, can be the value of another cell or a specified text or number. If your chosen range includes numbers, you can even throw in logical operators to add to the complexity of your formula, such as:
- Equal (=)
- Less than (<)
- Less than or equal to (<=)
- Greater than (>)
- Greater than or equal to (>=)
- Does not equal to (<>)
For example, if you have a list of test scores and want to count the number of students who scored 90 points or above, you can include the logical operator >= in the criterion part of your formula. Just be sure to put the logical operator and the number within quotation marks for the formula to recognize it.
You can also include Wildcard symbols in your criterion. For those who are unaware of Wildcards, here are some of the most common ones in Google Sheets:
- Asterisk: This is denoted by the “*” symbol and can be used as a proxy for any letter, number, a string of characters, or symbol. E.g., If you have a list of full names that include multiple entries with the first name “John,” you can use “John*” in your formula to search for all the cells that include that name in them.
- Question Mark: This is denoted by the “?” symbol and acts as a stand-in for a particular character in a string. E.g., Your range includes the Samsung mobile phone models S10, S11, and S12. By adding “S1?” to your formula, you can search for all three models since the Wildcard takes the place of the third digit in the model name.
- Tilde: This is denoted by the “~” symbol and is commonly used before either a question mark or asterisk. Basically, it negates the use of either symbol as a Wildcard and forces the formula to recognize it as simply a symbol. E.g., If you add the “Bob~*” to your formula, you can search for the string “Bob*” without the asterisk symbol acting as a Wildcard.
Considerations When Using The COUNTIF Google Sheets Function
Creating complex formulae in Google Sheets is often a trial-and-error process, even for the pros, but here are a few things to keep in mind when using the COUNTIF function to string together a successful formula:
1. The COUNTIF Function Is Not Case-Sensitive
It doesn’t matter if you’re using upper or lower-case characters for your formula. It will run the same way, regardless.
2. How To Count Cells That Are Blank or Not Blank
By nature, COUNTIF disregards blank cells when counting. If you don’t want that to be the case, you can use two quotation marks (“”) in your criterion to ensure that empty cells in your selected range are counted. Conversely, you can use the (<>) – also known as the not-equal logical operator – in your criterion, if you want to count cells in your specified range that are not blank
3. You Don’t Need To Add Quotation Marks For Numerical Values
Unlike alphabetic or alphanumeric strings, if your criterion includes numbers with precise matches, then you don’t have to put them in between quotation marks.
4. You Can Contain Your Criterion In a Different Cell
If you want your COUNTIF formula to look sleeker and a bit cleaner, you can house the contents of your criterion – which is the longest part of your formula – in a separate cell and simply reference that cell in the criterion of your formula.
Using COUNTIF In Google Sheets
Now, let’s get into how to use COUNTIF in Google Sheets. As sample data, we’ve created a Google spreadsheet that contains various information revolving around school tests. There are columns for Name, Nationality, Education Level, Test Score, and Test Date. We’ll be using the following spreadsheet in our examples to come:
Counting Cells That Contain a Particular String Of Text
One of the most common uses of the COUNTIF function is when you want to know how many cells within a designated range contain a particular string of text or number. Using our sample data as an example, let’s say we’d like to count the number of students that are American. For this to be accomplished, we’ll need to count the number of times the text “American” appears in column B, which has the “Nationality” header.
We can use COUNTIF to count cells with a specific text in Google Sheets which, in this case, is “American.” To do this, simply type in the following formula:
=COUNTIF(B1:B8, “American”) – where B1:B8 is the range of data you’re looking into and “American” is the text that you’d like to count. It should look something like this:
Counting Cells That Belong To a Specific Date
Another great use of the COUNTIF function is to count cells that contain a specific date within a given range. We’ve amended the info so that a couple of students have test dates that fall under the same day. So let’s assume that you want to count how many students took the test on February 1, 2023. To do this, you can use the formula below:
=COUNTIF(E1:E8, “February 2, 2023”)
Keep in mind that the date should be in between quotation marks. Here’s what you should see:
Alternatively, you can have your criterion be “02/02/2023” since the values of the cells under the E column are proper dates.
Counting Blank Cells Or Non-Blank Ones
We’ve mentioned before that COUNTIF in Google spreadsheets generally overlooks blank cells unless you instructed it to do otherwise. In fact, you can set it up so that it counts only blank cells specifically. You can do that with this formula:
=COUNTIF(A1:A10,””) – where the (“”) in the criterion denotes a blank cell. This is what it should look like:
If you want to count non-blank cells, simply edit the criterion to (“<>”), which will make it look for cells that are not blank in value.
Using COUNTIF Combined With a Logical Expression To Count Cells Under a Specific Condition
In the case that you’d like to count all cells in a particular range that are greater than, less than, equal to, or not equal to a specific value, you can use COUNTIF combined with a logical expression to achieve the result. For example, if you’d like to count all test dates that occurred after January 15, 2023, you can use this formula:
=COUNTIF(E1:E810,”>January 15, 2023”)
Using the same logic, you can use logical expressions in your criterion with numbers.
Counting All Cells That Include a Specific Text
You may encounter scenarios where you have multiple cells that include particular keywords along with other text. Take column C, for example. The keyword “Junior” is repeated in various entries but not all of them. To count cells that only include the word ”Junior”, this is the formula you would use:
=COUNTIF(C1:C10, “Junior*”) – where “Junior*” utilizes the asterisk Wildcard to look up any cells that include the text “Junior”.
Counting Cells Checkbox Values In Cells
Checkboxes have values as well, which are either TRUE or FALSE. TRUE means the checkbox is ticked, while FALSE means the checkbox is unticked. With this in mind, you can use COUNTIF to count all the cells in a given range that are either ticked or not.
For the sake of the next example, we manipulated some data and added another column with checkboxes that tell whether a student passed or failed. You can use this formula to look up the number of students who passed by counting checkboxes in Google Sheets with a value of TRUE:
Google Spreadsheet COUNTIFS Function
On its own, COUNTIF is only capable of counting within a range of cells using a single criterion. If you’d like a count that utilizes multiple criteria, then you should look into the COUNTIFS Google Sheets function instead.
An example of this would be to count the number of students who scored above 70, are in Senior High, and took the test after January 15, 2023. Your formula would end up looking similar to the one below:
=COUNTIFS(C1:C10, "Senior High," D1:D10,">70", E1:E10,">January 15, 2023")
There’s also another variation of the COUNT function called COUNTUNIQUE which allows you to count unique values in Google Sheets within a chosen range.
The COUNTIF Google Sheets function is an incredibly useful tool that’s relatively easy to master if you can wrap your head around its logic and rules.
It’s a simple way to count the number of times a specific value (or lack thereof) appears in a selected range under a particular condition.
Its purpose may be relatively basic, but it allows for many complex formulas if you can approach it properly.
- How to Count Unique Values in Google Sheets [Easy Examples]
- Easy Guide to the COUNTIFS Google Sheets Function
- Count Checkboxes in Google Sheets with 2 Simple Formulas
- Count Cells based on the Cell Color in Google Sheets
- Count the Number of Characters in a Cell in Google Sheets (LEN Function)
- Count if NOT Blank in Google Sheets: 4 Simple Methods
- Count Cells With Text in Google Sheets [2 Easy Functions]
- How to Get the Word Count in Google Sheets (Easy Formula)