What is CountIF?

 

Do you need to learn the use of COUNTIF? For intermediate Excel users, COUNTIF is a great tool. The COUNTIF() family of functions is among the most frequently used in Excel. Amongst them, the ones most commonly referred are COUNTIF() and COUNTIFS() formulas. These can be used to count the number of cells holding certain values based on what’s held in other cells.

The difference between these two functions is simple: the first one is use to count against single criteria (e.g. ‘how many students got a grade A’), where the later can count based on multiple criteria (e.g. ‘how many students got a grade A and were from a certain class’).

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

COUNTIF

This function takes two arguments or elements. First, refer to the range where we want to look for (validate) a certain value (criteria). Second, enter the value being looked for (the criteria itself).

 

Format (syntax):

 

=COUNTIF(range, criteria)

 

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

 

COUNTIFS

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

 

=COUNTIFS(criteria_range1, criteria1,…)

 

Using this format, you can build up a long list of ranges to be checked and what they should be checked for, all separated by commas.

 

Simple counting with COUNTIF

Referring to the example sheet, you may want to count the number of students in Class I. the students’ classes are listed in column C and Class I is specified in cell L4. The formula would be:

 

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

 

Answer: 3

 

Similarly, the number of students with 4 absences would be:

 

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

 

Answer: 10

 

Example of COUNTIFS

We can add a little complexity to the examples now. What if we want to calculate the number of students in class I with 4 absences? We need to check both column C for the class and column D for the number of absences. The following formula will do the task:

 

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

 

Answer: there is only 1 student in class I with 4 absences.

 

Another example might be to count the 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 (in this case ‘+’) in array mode, so we have to add such criteria 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 than three absences: we will use the logical operator “>” for this requirement. Had we been interested in counting greater than or equal to, we would have used “>=” sign. For now it is:

 

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

 

Answer: 14

 

In the same way, counting the number of students with marks between 50 and 60, we would use:

 

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

 

Answer: 04

 

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

 

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

 

Answer: 15

 

Thus, with simple formulas, we are able to perform exploratory data analysis to extrapolate conclusions from the data.