# Excel 2003

I would like to have formula in column C where it should show "1" for the largest amount for each ID and then 2 for the second largest amount for each ID and then 3 for the 3rd largest amount for each ID. For exameple for ID 110 it should 1 for 25 amount and 2 for 23 amount. Please look at the attach sample. thanks
Sample.xls
###### Who is Participating?

Commented:
Try this formula in cell C2, copy down

=SUMPRODUCT(--(A\$2:A\$10=A2),--(B2< B\$2:B\$10))+1

Thomas
0

Commented:
Put this in C2 and copy down:

=SUMPRODUCT(--(B2>\$B\$2:\$B\$1000)*(\$A\$2:\$A\$1000=A2))+1

Increase the 1000 to the max your range will be

see attached:

Dave
ranking-sample-r1.xls
0

Commented:
you beat me, Thomas...

Dave
0

Commented:
and I ranked backwards.

My formula should be

=SUMPRODUCT(--(B2<\$B\$2:\$B\$1000)*(\$A\$2:\$A\$1000=A2))+1

lol

Dave
0

Author Commented:
Good job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.