Using COUNTIF To Count!

Using COUNTIF To Count!

COUNTIF() family of functions is amongst the most frequency used family of functions in Excel. Amongst them, the ones most commonly referred are COUNTIF() and COUNTIFS() formulas.

The difference between these two functions is simple: the first one is use to count against single criteria, where the later can count based on multiple criteria.

Let’s begin with the syntax and we will see with example how it works:

 

The COUNTIF():

This function takes two arguments, first refer to the range where we want to validate a criteria and the second one is criteria itself.

=COUNTIF(range, criteria)

It should be noted that this function works only in columns.

 

The COUNTIFS():

This function can take up to 127 range and criteria and has the following syntax:

=COUNTIFS(criteria_range1, criteria1,…)

 

Simple counting with COUNTIF()

Referring to the example sheet, you may want to count the number of students in Class I the formula would be:

=COUNTIF($C$2:$C$20,L4)

Answer: 03

Similarly the number of students with absences 4 would be:

=COUNTIF($D$2:$D$20,L5)

Answer: 10

 

Example of COUNTIFS()

We can add little complexity in the examples now. What if we want to calculate the number of students in class I with 4 absences? The following formula will do the task:

=COUNTIFS(C2:C20,L6,D2:D20,L7)

Answer: 01 – there is only 01 student in class I with 04 absences.

Another example could be to count No. of students in class I and II and from year 2003. The formula would be:

=COUNTIFS(C2:C20,L10,G2:G20,L12)+COUNTIFS(C2:C20,L11,G2:G20,L12)

Answer: 02 – only two students meet this requirement.

Note the difference in construction of this formula. In COUNTIF formulas, we cannot execute the logical operator AND in array mode, so we have to add such criteria’s individually.

 

Example of using logical operators in COUNTIF formula:

There is no difference in the notation used for logical operators in both COUNTIF() and COUNTIFS(). The following examples demonstrate how to use them for both:

Counting Number of students with more then three absences: we will use the logical operator “>” for this requirement. Had we been interested in counting greater then or equal to, we would have used “>=” sign. For now it is:

=COUNTIF(D2:D20,">3")

Answer: 14

In the same way, counting number of students with marks in the bracket 50-60, would use:

=COUNTIFS(F2:F20,">50",F2:F20,"<60")

Answer: 04

And lastly, to count number of students that belong to year other then year 2002, will require use of following construction:

=COUNTIF(G2:G20,"<>2002")

Answer: 15

Thus with simple formula, we are able to perform a sought of exploratory data analysis to find the features of data.

 

 

Related Trainings

Practice: SUMIFS, COUNTIFS, AVERAGEIFS - III

Practice: SUMIFS, COUNTIFS, AVERAGEIFS - III

Making more practice by using SUMIFS(), COUNTIFS() and AVERAGEIFS() functions.


Practice: SUMIFS, COUNTIFS, AVERAGEIFS - IV

Practice: SUMIFS, COUNTIFS, AVERAGEIFS - IV

Making more practice by using SUMIFS(), COUNTIFS() and AVERAGEIFS() functions.


Practice: SUMIFS, COUNTIFS, AVERAGEIFS - V

Practice: SUMIFS, COUNTIFS, AVERAGEIFS - V

Making more practice by using SUMIFS(), COUNTIFS() and AVERAGEIFS() functions.