Excel COUNTIF function
Excel COUNTIF function is the combination of COUNT and IF function.
Excel COUNTIF function – How to define
“Excel COUNTIF function helps us to find a result 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 must be met when to find a result.
When working with Excel COUNTIF function, always use one criteria with one range.
COUNTIF function, gives a result with a numeric value, like (0,1,2,3, . . .) so we can use more “COUNTIF” function with addition (+) or subtraction (-) sign.
We can apply two or more criteria in a single range but one criteria with one range and then add or subtract them. See the rule one (1).
Topics for Excel COUNTIF function
- The frequency of a “name” in a range
- Count before or after a particular date
- How to find a sales target met or not
- COUNTIF + COUNTIF how to use
- How to find duplicate entries
- How to find blank and no blank
- With a wildcard character (? *, ~)
- Sorting the data in ascending order
The frequency of a “name” in a range
Here in the above image, we can see that the frequency of “George” in “First Name” Column is more than one.
Suppose we want to know that how many “George” appear in column “A” or “First Name” column. Then apply the formula of Excel COUNTIF function as following way:- =COUNTIF(range, criteria).
Can also write it like =COUNTIF(range of all name in a column “($A$2:$A$11)”, criteria “(George)”. So our formula is =COUNTIF($A$2:$A$11,”George”).
As we already told in this blog that “COUNTIF” always produce a result in a 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.
Our data will remain the same, and our “range” area will be changed from “name range” to “date range” because we are looking for a date range. So our range will be “C2: C11” instead of “A2: A11”.
So 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 “>11-May-14” date.
The final formula is =COUNTIF ($C$2:$C$11, “>11-May-14”) and our result is “4”.
And 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”) where we have applied “< less than” sign instead of “>greater than”.
How to find a Sales target met or not.
Next topic is, how many employees have met their sales target which is assigned to them. Here “Sales” is our criteria range.
First of all, select the range where the sales figure is appearing, which is in column “D” of the data-set.
So for those who met their sales target, the formula will be “=COUNTIF(“D2: D11”, “>5000”). This formula tells that how many employees have met their sales target.
COUNTIF + COUNTIF how to use
Next one is combining “COUNTIF +COUNTIF” in one formula. We can apply two or more COUNTIF function in a single equation, as we know that it will produce a number value.
Here we will find the total number of “Sales” and “Purchasing” departments in the data-set. Apply the below formula to find out.
Our first formula will find the “how many people are in the “Sales” department whereas our second COUNTIF function will find out how many of them are in the “Purchase” department.
How to find duplicate entries
Now how to find duplicate entries in a column. In the below table we have “Names” in a column “C” and column “D” has the formula. Apply “COUNTIF” formula to find duplicate entries, which gives result in the form of TRUE/FALSE. “TRUE” are duplicate entries and “FALSE” is unique or not duplicate. See below:-
Here is the formula applied, “=COUNTIF($A$34:$A$43, A34)>1.”
Apply the formula in the 2nd column; it will give a result as TRUE/FALSE. TRUE is, duplicate existing whereas, FALSE is unique existing. Drag the formula end of the dataset and done.
How to 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 cells are empty or not showing any amount or text matter.
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 empty cell only). See below image:-
With wildcard character (?, *)
How to use wildcard characters, “Asterisk (*) and question mark (?)” with Excel COUNTIF function.
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 want to count the words which have its last characters as “e”. Apply Excel COUNTIF function as “=COUNTIF (A2: A11,”*e”)”. And see the image:-
The formula will find words, containing the last character as “e”. It does not matter how many characters it has before it.
In the below-given table, we are looking for a count of the words which has its three characters as “Joh” and rest characters we do not know.
So with the help of Excel COUNTIF function, we can find the total number of a name which has the first three number as “Joh”. See the formula in the below-given image:-
Similarly, we can solve the query for the name whose first three characters we do not know, but the last three characters are “rge”. The solution is in the below-given image:-
Sorting the Data in ascending order
Now, learn how to sort Excel data in ascending order with the help of Excel COUNTIF function.
Is it possible? Let’s try
Suppose that we have a name list and we want to sort the table in the ascending orders. Like (1,2,3,4… so on) or in alphabetic ascending orders.
We have names in column “A” and apply the formula in column “B”. Write a formula in cell one of column B and drag it to the last row of the name list. The sequence number of every name appears in the second column. See image below:-
These are the few examples of Excel COUNTIF function. There are many more another examples but could not explain in a single blog. Using these example is to make an overview of the Excel COUNTIF function. Indeed a most useful function.
Please feel free to make a comment or suggestion for this blog. I would feel grateful if someone shares something.