How to use COUNTIF Function | Microsoft Excel

How to use COUNTIF Function | Microsoft Excel

how to use countif function in 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

How to use COUNTIF Function in Excel


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

The COUINTIF Function will return how much time an object appears in the given array as per given criteria

Usage of COUNT Function

The COUNTIF Function is used to get a number which you want from the range given.

Error Values

Error Commands

Description

0
If the required value is not in the range
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

We have a data of 9 students with their subject and percentages
Here we are going to count how much students get the percentages(as specified in column F4)
 COUNTIF Function for numbersH
Here we are using the formula =COUNTIF(D4:D13,F4)
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

=COUNTIF(D4: D13,49)

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

COUNTIF FUNCTION FOR CONDITION


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.

wild card character in countif

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!

If you face any difficulty in the content feel free to ask!
Thank You.

Leave a Reply

Your email address will not be published. Required fields are marked *