Rank with a array that is not in cells next to each other.

[XNemo]

Hi Forum.

I'm trying to use the rank function to rank some numbers from different years.

This is an example of my start data.
Year Amount
A 2014 1000$
2013 2000$
2012 3000$
B 2014 3000$
2013 2000$
2012 1000$
C 2014 1200$
2013 1300$
2012 1400$

Now i want to rank the different years of each (A,B,C) compaired to the others.

Like this:

Year Amount Rank
A 2014 1000$ 3
2013 2000$ 1
2012 3000$ 1
B 2014 3000$ 1
2013 2000$ 1
2012 1000$ 3
C 2014 1200$ 2
2013 1300$ 3
2012 1400$ 2

The problem is now that the array that I need to enter in my =RANK function is not an array with cells next to each other.

Hope you can help me.

- Xnemo

[samba_ravi]

=SUMPRODUCT(($B$1:$B$9=$B1)*($C$1:$C$9>C1))+1
Assuming that you have data in ColumnA to Column C
or
=COUNTIFS($B$1:$B$9,$B1,$C$1:$C$9,">"&C1)+1

[XNemo]

Thank you very much.