Contents
What is COUNTIF Google Sheets Function?
COUNTIF is a function that offers a conditional count that is counting under defined conditions, for selected values. Applications like Microsoft Excel and Google Sheets use the COUNTIF function to find out and count cells that fit certain criteria, in a certain specified range of cells containing different values.
We will discuss and go through the basics of COUNTIF Google Sheets function to help beginners. This article will act as the foundation for understanding how to go about the COUNTIF function in Google Sheets. With additional practice, more complicated criteria can also be employed.
COUNTIF Syntax in Google Sheets:
In google sheets, the COUNTIF function is to be written in the formula bar indicated by “fx” or in any selected blank cell, according to the following syntax (or basic formula).
=COUNTIF(range, criterion)
Here,
- COUNTIF is the function.
- Range refers to the range of cells that need to be checked for the specified criteria. It is written with the two cell addresses (represented by a combination of numbers and letters) and a colon (:) in between, indicating the to and from. This can be both row or column range values.
- Criterion refers to the criterion argument that the cells, if following, will be counted for. It could be a number (10, 12 15 etc.), a text (Orange juice, Mango juice etc.) or a cell reference (A2 or B5).
Once the correct COUNTIF formula is entered, google sheets analyzes values in the cell range, calculates which values fulfill the criterion, and displays the result in selected cell.
How to Use the COUNTIF Function in Google Sheets:
Since the syntax is so simple, the usage of the function is also pretty simple.
To use this function, all you have to do is to go to an empty single cell, enter the formula with your desired cell range and conditions and press enter to get your result in the empty cell.
The COUNTIF formula can be used in a lot of ways, for a lot of different types of criteria. You could use it to find an exact word(/s) or number(/s) from a list of words and/or number or to find all numbers greater than, (or less than, or not equal to) a specific number.
A few of these examples have been discussed in detail below:
Using COUNTIF to Find Exact Matches:
This refers to exact matches of text or digits. This is the simplest way of using COUNTIF formula or function in Google sheets.
Following is a sample usage of the COUNTIF function.
Let’s suppose a data set of different restaurant franchises that are selling different products for varying prices and quantities on any give day.
If we want to count the number of times “Mango Juice” is sold across all franchises, we simply need to go to an empty cell and enter the syntax along with the required data.
=COUNTIF(D4:D18,”Mango Juice”)
And, this will be the result.
It is necessary to use double quotation marks to enclose the text-based criteria but not the numerical value criteria.
Similarly, we can follow the same method for products other than mango juice, and/or for numerical digits as well.
For example, to count the number of products that had a total of exactly twelve quantities, we employ the same syntax along with the required data.
=COUNTIF(F4:F18, 12)
If you noticed, since our criterion contained only the number therefore we didn’t have to enclose it in double quotes.
After pressing enter, we get our desired result in cell F20.
Using COUNTIF to Find Values Greater than, Smaller than, or Equal to :
In order to search in a list of values, ones that fit your ‘greater than’ or ‘smaller than’ criteria all you need to do is to mention your cell references and then add the suitable comparison operator to your formula i.e., “>” and “<” for greater than and smaller than respectively.
Greater than:
In this example, we will calculate the number of per day total sales that are greater than 10,000 in monetary value.
=COUNTIF(G4:G18, “>10000”)
Google sheets searches the columns and displays the results.
Lesser than:
Similarly, the syntax below can be used for lesser than criteria:
=COUNTIF(G4:G18, “<10000”)
And just like this, we can produce an extensive summary table that breakdowns our data effectively.
Equal to:
For equal to, the “=” sign will be used as indicated below:
To illustrate this via example, let’s apply the below mentioned formula on our existing data set in order to find the number of days when total sales stood exactly 10000.
=COUNTIF(G4:G18, “=10000”)
Since, none of the sales were exactly 10000 therefore COUNTIF Google Sheets function gives us ZERO as our final result.
Other Related Symbols and Operators
According to the task requirement at hand, the necessary suitable symbol or comparison operator will be used. For example, greater than or equal to, and smaller than or equal will use ‘<=’ and ‘>=’, respectively.
The below mentioned formula will help us calculate all the number of days when total sales were greater than or equal to 10000.
=COUNTIF(G4:G18, “>=10000”)
Similarly, we can also find the number of days when total sales were less than or equal to 10000 by using the below mentioned formula.
=COUNTIF(G4:G18, “<=10000”)
Since, these criteria are not entirely numeric values, double quotation marks are essential here as well.
COUNTIF and Counting for Blank or Non-Blank Cells:
To count blank and non-blank cells the process is made super simple by COUNTIF in google sheets. Like the others, you just need to know the symbols denoting blank or filled cells and put them up in the same COUNTIF or COUNTIFS function syntax.
Blank cells are denoted by nothing; simply use quotation marks without anything in between – “”. The formula is:
=COUNTIF(RANGE:””) e.g. =COUNTIF(F7:F15,””)
If there is a blank cell in your data, sheets will count it and display the total number.
Non-blank cells are represented by <>, and the formula is,
=COUNTIF(RANGE:”<>”) e.g. =COUNTIF(F7:F15,”<>”)
Other Considerations for the COUNTIF Function:
In order to dive deeper into the more complicated syntaxes, thereby, establishing a specialized hand on google sheets, some other considerations regarding the COUNTIF function are discussed below:
COUNTIF for Multiple Cell Ranges:
If you want to find google sheets to analyze two or more ranges, the formula will follow the following structure:
=COUNTIF(range 1, criterion) + COUNTIF(range 2, criterion)
For example,
=COUNTIF(C27:C41, “Mango Juice”) + COUNTIF(F27:F41,”Mango Juice”)
Entering a formula like
=COUNTIF(C27:C41, “Mango Juice”, F27:F41,”Mango Juice”)
will be incorrect and won’t be perceived by google sheets.
COUNTIF and Wildcard Characters:
COUNTIF Google Sheets function is not just limited to searching for exact matches of the entered criteria but it can also search for parts of the total cell content. For searching for such parts of spreadsheet cells, we use such wildcard characters, which are simple signs.
In google sheets, there are three wild characters. These are:
- Question Mark “?”
- Asterisk “*”
- Tilde “~”
1. Question Mark “?”
Usage of the question mark sign replaces one single character. If you enter “4?” Google sheets is going to search for all characters replacing the ?, such as 4B or 4M, and if you enter “B?”, google sheets will search for cells with characters like B2, B8, etc.
For example,
=COUNTIF (K36:K44, “?B”)
=COUNTIF (K36:K44, “B?”)
2. Asterisk “*”
Using the asterisk sign with COUNTIF function will look for any number of contiguous characters before and/or after the asterisk.
For example,
=COUNTIF (K36:K44, “*B”)
=COUNTIF (K36:K44, “B*”)
*B will look for data that ends with the letter B, such as 2HB, and BAB, up to any length in terms of characters
B* will look for data that starts with the letter B, such as BAC, and B23, up to any length in terms of characters.
3. Tilde “~”
The tilde sign is used when you want to look for cells that themselves contain the * and ? characters.
If you want to search for cells ending with “?”, you will use “~?”.
For example, to search for questions in a list of statements you will use the formula
=COUNTIF(C50:C52, “*~?”)
This will make sure the COUNTIF Google sheets considers the “?” here as a normal character (an actual question mark), and not a wildcard character.
Just like the comparison operators, the wildcard characters are also supposed to be written within double quotation marks. These wildcard characters are not just limited to the COUNTIF function only, and can be applied for other google sheets functions as well.
COUNTIF and Searching for Specific Keywords:
The wildcard character asterisk can also be used to search for the number of cells containing specific keywords within string contents of cells, as in a partial match.
For this purpose, you need to search for it like this
*keyword*
The keyword will be in between two asterisks and double quotation marks, right after the row or column range or ranges.
The function formula would look something like this
=COUNTIF(D4:D8, “*keyword*”)
Doing so will give us a total number of cells using the word “mango” irrespective of the other data inside the particular cell.
COUNTIF and Multiple Criteria:
For application of COUNTIF function with multiple criteria, google sheets has a second function called “COUNTIFS”
The syntax of this COUNTIFS formula is,
COUNTIFS Syntax =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …])
These criteria may either be two-dimensional or three-dimensional. Some formula examples include:
=COUNTIFS(C4:C18, “ISB”, D4:D18, “Mango Juice”)
=COUNTIFS(C4:C18, “ISB”, D4:D18, “Orange Juice”, B4:B18, DATE(2021,1,1))
The first row indicates the fulfillment of two criteria, i.e. franchise located in ISB and product being mango juice.
The second row involves the fulfilment of three criteria i.e. franchise located in ISB, product being Orange juice and 01/01/2021 as the date, thus making the syntax three-dimensional.
How to use COUNTIF with a date condition
COUNTIF Google Sheets function with a date condition will allow the user to find the required criterion/criteria on a particular date as required.
Date condition does not required double quotations marks and date is entered in yy:mm:dd format.
The Function Syntax would look something like this:
=COUNTIF(Range, Date)
=COUNTIF(B4:B18,DATE(2021,1,13))
Using the above formula, we were be able to find how many sale entries were recorded on January 13, 2021.
Frequently Asked Questions (FAQs):
Q. Are text-based criteria case-sensitive?
A. The criteria for COUNTIF Google Sheets function are not case sensitive and you will get the same answer whether you search for “Mango Juice” or “MANGo JUIce”. Just remember to always use double quotation marks and the opening and closing bracket for a string criterion argument, in google sheets.
Q. Will =COUNTIF(range, “?1”) search for numerical values like 11, 21?
A. No, it will not. =COUNTIF (range, “1?”) will also not search for numeric values like 10-19. The same applies to searching for these with asterisks.
Q. How do I look for all cells containing texts in a range of cells?
A. Non-blank text containing cells are represented by an Asterisk. Use the formula =COUNTIF(F7:F15,”*”) after adjusting for your specific cell references.