How to use COUNTIF Function | Microsoft Excel
COUNTIF Function is a statical function. COUNTIF Function will count the values from the given criteria.
COUNTIF Function is a counter which counts your desired value or data.
If you have lots of data and you have to need how much time a specific object is used than COUNTIF Function is suitable to your need.
Syntax of COUNTIF Function
=COUNTIF(range,criteria)
Arguments for COUNTIF Function
range: the criteria in which you want to count the object
criteria: the value or object you want to search from your data
Behavior Of COUNTIF Function
Here we have taken an example of the student names and subjects with their percentages.
Right side we have used the formula for COUNTIF Function.
The Formula is
=COUNTIF($B$3:$D$22,F3)
$B$3:$D$22 is your table or data, which has all the information
F3 Here F3 is a reference value which is taken from the information or object from the cell F3
For Electronics
Here F3=”Electronics”
The result is 3 because in the table “Electronics” word is used 3 times.
For Computer
Here F4=”Computer”
The result is 5 because in the table “Computer” word is used 5 times
For Civil
Here F5=”Civil”
The result is 2 because in the table “Civil” word is used 2 times
Here F6=”Financial”
The result is 2 because in the table “Financial” word is used 2 times.
Return of COUNT Function
Usage of COUNT Function
Error Values
Error Commands | Description | |
0 |
| |
Wrong Value | If you entered more than 255 character | |
#VALUE | If you take data from another worksheet but worksheet not open |
More Examples
Example 1 COUNTIF Function for numbers
This example will show you how much time a number is repeated in the selected range from the data
D4:D13: It is a range from where the all students percentages are listed
F4: It is the value that searches from the list of the percentages of the students
Here we have used F4 to take reference from the list
As, same you can use manual formula also for the same
Will give you the same result as the above formula.
Example 2 COUNTIF Function for conditional
Here we are using condition to know how much students get more than 50 percent from the list
Here I am using condition for how much student get more than 80 and how much student gets less than 50
We are using the formula
=COUNTIF(D4:D13,”>80″)
Here
D4:D13: it is the range of the percentages(data)
“>80”: it is the condition that defines in the range have any value more than 80 will count
You can see here is 6 values that is more than 80.
=COUNTIF(D4:D13,”<50″)
This function defines if the range has values less than 50 will count
here you can see there are two values are less than 50 so, ans is 2
Example 3 To use Wild Card Character(*)
Here we are using a wildcard character (*) to find a name that contains the specific word or find the name by any character of the name.
Here we find the numbers of student whose surname is patel
=COUNTIF(B4:B13,”*patel*”)
B4: B13; It is the range of the name of students
“*patel*”: searches for the word “patel” from the range
This function will count how much patel surnames students are there.
There are 3 patel surnamed students
=COUNTIF(B4:B13,”*r*”)
This formula will count the how much time letter “R” is used in the name of the students in the list.
________________________________________________________________________
How would you like this content to write us in the comment box!