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.
Table of Contents
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.
[adthrive-in-post-video-player video-id=”xm7TqRaq” upload-date=”2021-11-22T14:24:56.000Z” name=”Easy Guide to COUNTIF and COUNTIFS in Google Sheets” description=”Learn how to use the COUNTIF function in Google Sheets. This video also shows how to use the COUNTIFS function, which uses multiple conditionals.” player-type=”default” override-embed=”default”]
Syntax Of The COUNTIF Function
If you look up the COUNTIF Googleย function in a spreadsheet, youโll find the COUNTIF syntaxย below:
=COUNTIF(range, criterion)
- 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.
[adthrive-in-post-video-player video-id=”xm7TqRaq” upload-date=”2021-11-22T14:24:56.000Z” name=”Easy Guide to COUNTIF and COUNTIFS in Google Sheets” description=”Learn how to use the COUNTIF function in Google Sheets. This video also shows how to use the COUNTIFS function, which uses multiple conditionals.” player-type=”default” override-embed=”default”]
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, 2024. To do this, you can use the formula below:
=COUNTIF(E1:E8, โFebruary 2, 2024โ)
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, 2024, you can use this formula:
=COUNTIF(E1:E810,โ>January 15, 2024โ)
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:
=COUNTIF(F1:F10, โ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, 2024. Your formula would end up looking similar to the one below:
=COUNTIFS(C1:C10, "Senior High," D1:D10,">70", E1:E10,">January 15, 2024")
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.
Wrapping Up
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.
Related:
- 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)