Sometimes we have to know the rankings of amounts in an array or range, in order to select some of them, let’s say top 10. Microsoft Excel comes to the rescue by offering a formula to count elements that have certain attributes or meet some conditions. This function is COUNTIF and it’s result is the number of elements in a range that qualify to the condition given.

Using COUNTIF to determine top rankings of amounts in a range

The solution to this problem is simple, if you can find the right condition to use in the COUNTIF formula.

So, we need to find the largest amount to give it position 1 in our rankins, and then find the second largest amount and give it ranking 2 and so on. This translates, for the first, largest amount in that there is no other element/amount larger than it. That is, the COUNTIF formula to return zer0 when counting how many amounts are larger than the current one. The condition for this kind of attribute is composed from 2 elements: “>” that is the sign for greater than and &B4 (position of the current amount).

Take a look in the picture bellow:

Don’t forget to add 1 to the result, in order for the first amount to have the first rank and not zero (the number of amounts that are larger than it is).

Good luck excelling!