Excel COUNTIF Function is a member of the COUNT Family and is a combination of two functions, COUNT and IF function.
In this blog post, you will learn about many examples or uses of the Excel COUNTIF function, such as find any particular number within a given range or date or text and so on. We can also use logical operators, such as (>, <, <>, =, <=) and wildcard characters like (?) and (*).
Define Excel COUNTIF function
“This function helps us to find a result or value in a range of cells or dataset if there is any condition or criteria is provided.”
Syntax of Excel COUNTIF function
=COUNTIF (range, criteria)
range – dataset or range where our result is located.
criteria – this is a condition (criteria) that must meet when to find a result.
Important note
- When working with the COUNTIF function, always use one criteria with one range.
- COUNTIF function, always produce a result with a numeric value, like (0,1,2,3, . . .) so that we can also apply more “COUNTIF” function with addition (+) or subtraction (-) sign as per our requirement.
- We can apply two or more criteria in a single range but one criteria with one range and then add or subtract them. See rule one (1).
Frequency of a “name” in a range
In the below image, we can see that the frequency of “George” in the “First Name” Column is more than one.
Suppose we want the frequency of “George” in column “A” or “Name” column, so we will apply the formula according to the following way:-
=COUNTIF(range, criteria).
Can also write it like – =COUNTIF (range of all name in a column “($A$2:$A$11)”, criteria “(George)” and finally our formula is =COUNTIF ($A$2:$A$11, ”George”).
As we already told in this blog that “COUNTIF” always produces a result in numeric form. So here we will get the answer in the number form and our result is “2”.
There are “2” numbers of “George” in the Name column in the above-given table.
Count Before or After a Particular Date
Now come to the second point where we have to find out how many employees are there who joined this company before or after a particular date.
So our data will remain the same and our “range” area will be changing from “name range” to “date range” because we looking within a date range. So our range will be “C2: C11” instead of “A2: A11”.
Our formula would be in this case “=COUNTIF (“range of date area”,(“$C$2:$C$11”), criteria (“>11-May-14”). Here we choose our criteria as “>11-May-14”, it will show us all the number of employees who joined the organization after the “>11-May-14” date.
The final formula is =COUNTIF ($C$2:$C$11, “>11-May-14”) and our result is “4”.
And the same way we can find the joining date before a particular date by changing the formula as =COUNTIF ($C$2:$C$11, “<08-Aug-14”), here we have applied “< less than” sign instead of “>greater than”.
How To Find A Sales Target Met
The next topic is, how many employees have met their sales targets, where the sales target is assigned to the employee. Here this target is our criteria in this case.
First of all, select the range where the sales figure is appearing. So here in this range, the sales figure is given in column “D”.
For those who met their sales target, the formula will be “=COUNTIF (“D2: D11”, “>5000”). This formula tells how many employees are there who have met their sales target “>5000”.
Applying Multiple COUNTIF In One Range
The next topic is applying multiple COUNTIF in a single range. We know that Excel COUNTIF Function gives results in the form of a numeric value. Apply COUNTIF function in one range first and then use addition (+) or subtraction (-) sign and then apply the second COUNTIF function.
So this way we can easily apply addition or subtraction in our COUNTIF Function.
Here we will find the total number of “Sales” and “Purchasing” departments in the table.
Our first formula will give us the result as a total number of “Sales” department and second formula give us the result as a total number of “Purchase” department.
Excel COUNTIF Function-How To Find Duplicate Entries
In the below image, we have “Names” in column “A” and COUNTIF formula in column “B” to find duplicate entries. Our result is appearing in column “B” in the form of TRUE/FALSE format. Where TRUE represents as duplicate entries and FALSE for unique.
In this table, TRUE represents as duplicate entries and FALSE for unique.
Excel COUNTIF function – Find Blank and No Blank
Now the next topic is how to find a cell is blank or not blank. As we can see in the below-given image our first column has some text values (names). Some cell is blank or not showing any value or text matter.
Here we will apply the formula to find a blank cell. See below formula: – “=COUNTIF (D2,””)”.
This formula will give us the result in the form of “0” and “1”. Earlier in this blog, we learned that the “COUNTIF” function gives a result in number value, like (0,1,2,3,4……).
After applying the formula “=COUNTIF (D2,” “)”, if any cell has a value it will count as “1” or if there is no value it will count as “0”. Now apply the “IF” formula to exchange “0” with “Not Blank” and “1” with “Blank”.
How this formula works – it will count a cell as “1” if it has a blank space or counts “0” if there is no blank space. (As we are counting blank cell only).
Excel COUNTIF Function Using Wildcard Character (?, *)
Apply COUNTIF function with wildcard characters such as Asterisk (*) and question mark (?).
Wildcard Characters:-
Asterisk (*) – How to use
(*e) = any numbers of character before character “e”
(e*) = any numbers of character after character “e”
Question mark (?) – How to use
(??) =one (?) sign for one character and two sign (?) for two and so on…
In a below-given image, we will count the words which have its last characters as “e”.
Apply COUNTIF formula as “=COUNTIF (A2: A11,”*e”)” and result would be
The formula will find the number of words, containing the last character as “e”. It does not matter how many characters it has before it.
Similarly, we can find the result as “how many words contain its first character as (J)”.
In this case, we will use formula as =COUNTIF(A2: A11, “J*”).
The formula will find out the words containing “J” as the initial letter, it may contain any number of characters after it.
First of all, we will type the character which we are looking for then type (*) asterisk character.
Now, we are looking for a count of the words which contain its first three characters as “Joh” and rest characters we do not know.
So with the help of COUNTIF function, we can easily find the total number of the name which has the first three numbers as “Joh”.
Similarly, we can solve the query for the name whose first three characters we do not know but the last three characters are “rage”. The solution is in the below-given image.
Sorting Data In Ascending Order
How to make a dataset according to ascending sorting order with the help of COUNTIF function. Is it possible?
Suppose that we have a name list and we want to sort this list according to ascender order in terms of a numeric value. Like (1,2,3,4… so on).
If we have a name list in the first column then put a formula in the second column. Once you write a formula in one cell and drag it to the last row of the name list.
These are the few examples of Excel COUNTIF function, it has many more examples but could not explain in a single blog. Using these examples to make an overview of the Excel COUNTIF function. Really most useful function.
Furthermore, details for the Excel COUNTIF Function are provided on the Microsoft Office website.
Please feel free to make a comment or suggestion for this blog. I would feel great if found a suggestion or share something.
Related Post