Countif and Rank Formula [SOLVED]

[Ecel Dumbo]

Hello Dear Friends,


Please refer attached spreadsheet. This is using count if and rank formula to rank values.

If a values repeats, the repeated values should share the rank. Please see attached sheet for better understanding. I use Excel 2003.


Thanks in advance


http://speedy.sh/ez6sa/Count-if-and-Rank-Formula-Query.xls

 

[Hui]

Ecel Dumbo


You have made a mistake in your Rank Column

C10 should be 9 as it is the same as A7 is the same as A10

the numbers after that are hence wrong


I would put a high value 99 or 999 in A14:A15 instead of blanks

Then in B3 use:

=SUMPRODUCT((A3 >A$3:A$17)/COUNTIF(A$3:A$17,A$3:A$17))+1

Copy down


If you don't want to see the 99 values use CF and make the font white

 

[Ecel Dumbo]

HI Hui, Many Thanks . It worked. Yes are correct, I made a mistake in the rank.


Thanks heaps