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

plus1

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 COUNTIF**S, **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.

Hi, suppose in your example there are 2 who scored 97. How do we rank it that one is 1st while the other is 2nd even if both are 97?

LikeLike

Need to inset a ranking with below criterias. I have this data:

Name Accuracy

A 100%

B 99.60%

C 99.80%

And below criteria

Score 1, if accuracy is <99.70 %

Score 2. if accuracy is 99.70 % to 99.90 %

Score 3. If accuracy is 100.00 %

Please help

LikeLike

What you try to achieve should be a lookup problem… read the blogpost below and see if it helps

https://wmfexcel.com/2015/11/14/nested-if-vs-vlookup-which-one-to-use/

LikeLike

Is it possible to use and modify these formulas in a filtered context. By that I mean is it possible to incorporate these rankings in multiple classes with SUBTOTAL for visible only cells (filters on)? – thanks

LikeLike

Hi Geoffrey,

Try adding a helper column with this formula:

=SUBTOTAL(103,C2)*C2

‘where C2 is the original score

Then apply the formula using COUNTIFS described in this post to the range of the helper column instead.

Hope it helps.

Cheers 🙂

LikeLike

Hi, how to get 1, 3, 2 instead of 1, 3, 1 hence how to NOT skip a rank like is shown in your example where both Angela and Carman got 1st due to both having 97.

LikeLike

Mmmmh got it with

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

LikeLike

But it does not work if more than 2 students would have the same score…

LikeLike

Hi cyribrd,

try this:

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

LikeLike