To get the overall ranking is easy with the RANK function.  The syntax of RANK

=RANK(Number,Ref,[order])

• Number is the number we want to rank;
• Ref is the list of numbers of comparison (could be an array, a reference or a list of numbers);
• Order, is the optional argument, when omitted or 0 it meanings descending order (i.e. larger number has higher rank); 1 = ascending order (i.e. smaller number has higher rank)

So to get the overall ranking, the following formula does the job effectively:

=RANK(C2,$C$2:$C$7)

How about to get the rank in class?  You may want to look for a function like RANKIF.  Unfortunately, there is no such straightforward function in Excel so far…

So is there a way to solve the problem above?

Array formula is on top of your mind?  If that’s the case, I believe you have quite good knowledge in Excel.  In my observation, majority of Excel users is not familiar with array formula.

Therefore, instead of showing a “complicating” array formula, I will show you how to use COUNTIFS to solve the problem.  Yes, you heard that – COUNTIFS.

Before we start, let’s think about what do we do when we try to rank a number from a list of numbers…

Suppose we have the following list of numbers:

We can tell very quick that 97 ranks first; 92 ranks ranks third (because there is a tie of 97, resulting in two firsts);  90 ranks forth; and so on and so forth.

But now, a simple question for you:  Why 97 ranks the first?

ummmmm……

errrrrrrrrrr……

well……

because……

See remarks in the screenshot below:

To make a generic statement for it:

The rank of a value in a list is equal to the number of values that are higher than the value itself plus 1

In the example, 97 earns the 1st place because there is no value higher than 97 itself. Make sense?

In Excel’s words, it is

=COUNTIF(Range, Criteria)+1

Substitute this into our example:

=COUNTIF($C$2:$C$7,">"&C2)+1  'note the use of & to construct the criteria

The following screenshot shows you the two constructions of the formula using RANK, and COUNTIF as described.  They yield the same result.

Wait… how about with additional criteria? i.e. Rank in class in the example?

Using RANK may not give you an easy solution.  Nevertheless, we may twist the COUNTIF just a little bit to solve the problem.  We will use COUNTIFS, which is very similar to COUNTIF but accepting multiple criteria.

=COUNTIFS(Range1,Criteria1,Range2,Criteria2...)

Substituting the range and criteria in our example,

=COUNTIFS($A$2:$A$7,A2,$C$2:$C$7,">"&C2)+1

• The first pair of Range & Criteria specifies the condition, i.e. Class;
• The second pair of Range & Criteria specifies what to look at, i.e. Score
• The +1 is critical to give you the correct rank.

Using the cell F3 for illustration.  The above formula means:

Count scores that are larger than “70” in Class “A”, then add 1 to it.

The following screenshot summarizes the result using different functions.

With COUNTIFS, we can do the ranking with multiple criteria easily. 🙂

Try to do the exercise by modifying the formula to do the ranking in ascending order.