Have you ever operated the function COUNTIF in Excel? COUNTIF is a statistical function which counts all the cells that match the criteria we describe in the function. This function always returns you a count number that has met the criteria specified in the function.
Let us discuss the syntax of the COUNTIF function:
COUNTIF(range, criteria)
range: Here, the range denotes the range of cells you are selecting for finding the match with the criteria.
criteria: It is the condition you describe in the formula which is matched with the range of cells specified in the formula.
Further, we will see how we can mention the criteria in the various method:
- Text
- Number
- Cell
- Logical Operator
- & Operator
- Wildcard Character
- Not Equal To
- COUNTIF Blank
- COUNTIF Not Blank
- COUNTIF with multiple criteria
For the explanation, I have taken data of 17 staff members (A2:A18) and their sale of this month (B2:B18). This picture below shows the data that we have used for explaining the COUNIF function with various criteria.
Steps to use the Function COUNTIF in Excel 2016:
COUNTIF in Excel with Text
In the given database I have to check how many times the text “Charles’ appears.
For this the formula will be:
=COUNTIF(A2:A18, “Charles”)
Result = 4
COUNTIF in Excel with Numbers
If I have to count cells that have the value 100.
For this, my formula will be
=COUNTIF(B2:B18, 100)
Result = 6
Remember for the numeric value, we do not require double-quotes.
COUNTIF in Excel with Cells
In case I have to count, how many time content in particular cell appear in the range of selected cell?
Then my formula will be:
=COUNTIF(A2:A18, A4)
Result = 4
In cell A4 I have the text content, ‘Charles’. So the above formula will give the count of the number of times Charles appear in the selected range.
COUNTIF with Logical Operator
COUNTIF greater than
For checking that in the numerical data ranged from B2:B18; how many cells contain the number that is greater than 160?
So the formula for this is:
=COUNTIF(B2:B18, “>160”)
Result = 8
COUNTIF with greater than equal to
If we have to find the cells that have the value greater than and equal to 160 then;
The formula will be:
=COUNTIF(B2:B18, “>=160”)
Result = 10
Similarly, we can check for smaller than, smaller than equal to.
COUNTIF with & operator
The “&” operator merges two criteria into a single criterion for validating the result. For explaining this we have an example in the picture below:
In the following example, we have counted only those cells which are “>” and also are equal to 160.
The formula is:
=COUNTIF(B2:B18, “>”&160)
Result is 8
Wildcard Character
? Character
The “?” character is a wildcard character which is used to match the individual character. For understanding this look at the example in the picture below.
The COUNTIF formula will only count the cells which will have 7 characters and must be ending with the sequence of character “les”. We have four cells in the selected range which have seven characters word ending with “les” i.e. “Charles”.
Formula is
COUNTIF(A2:A18, “????les”)
Result is 4
It’s not that you can only count the cells who have words ending with the particular character sequence. You can even find the cells that have words starting with particular character.
Like for the formula
=COUNTIF(A2:A18, “Emi???”)
Result is 2
* Character
Like “?” operator, “*” is also a wildcard character. But the difference is that “*” character matches any character. We can understand it with an example.
If we have to count the cells that have word with any character in any sequence then the formula is:
=COUNTIF(A2:A18, “*”)
As we have 17 cells in the selected range and all cells have a staff name. Therefore the result is 17.
To count the cells who have the ending character “es” then the formula would be:
=COUNTIF(A2:A18,”*es” )
This formula will count all the cells who have the word ending with “es”.
COUNTIF For Not Equal To
For “not equal to” the sign used is “<>”. If I have to find how many cells have the number that is not equal to 160. Then my formula would be:
=COUNTIF(B2:B18, “<>160”)
Result = 15
COUNTIF if Blank
Suppose we have to count the number of cells that are blank and doesn’t have any content in it. For this the formula would be:
=COUNTIF(A2:A18, “<>”&“*”)
The formula says that count the cells that have content not equal to any character.
Result = 2
COUNTIF not Blank
To count the number of cells that are not blank has some value either text, number, date, etc. then the formula would be:
=COUNTIF(A2:A18, “<>”&“”)
Result 15
COUNTIF with Multiple Criteria
In the COUNTIF function, only one criterion can be specified. So, if we have to use COUNTIF with multiple criteria then we can use multiple COUNTIF; mentioning one criterion per COUNTIF function.
In the picture below we have the example where we want the count of cells which have content either Charles or Emiliy
Formula is
=COUNTIF(A2:A18, “Charles”)+COUNTIF(A2:A18, “Emiliy”)
Result is 6
So this is all about COUNIF function. Hope you find it knowledgeable. Keep reading.
Leave a Reply