Rank in subgroup… RANKIF?

Excel tip - Rankif.PNG

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

  • 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:


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:

Excel tip - Rankif1.PNG

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?





See remarks in the screenshot below:Excel tip - Rankif4

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.Excel tip - Rankif2.PNG

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.


Substituting the range and criteria in our example,

  • 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.Excel tip - Rankif3

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.

This entry was posted in Formula and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s