RANK formula won't work when copied from PowerPivot

Let me set this up. I originally had this in a PowerPivot window and it works great, but my formula is crapping out now when I use it on a standard worksheet.
I have two columns. One is titled "A or B". Two is titled "Amount".
Example would be...

A or B Amount
A $100
A $1
B $250
A $0
B $25
B $50

I need a 3rd column to rank each within A or B now. For example row 3 and row 1 would both be ranked "1". Because they are the highest value for A and B.


A or B Amount Rank
A $100 1
A $1 2
B $250 1
A $0 3
B $25 3
B $50 2

My current formula for the "Rank" column is this...

"=COUNTROWS(FILTER('AllData','AllData'[AOrB]=EARLIER('AllData'[AOrB]) && 'AllData'[AMOUNT]>= EARLIER('AllData'[AMOUNT])))"

This is exactly what I needed in PowerPivot but now I need the same thing in a standard excel formula.
Any ideas?

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

Would that be something like "=SUMPRODUCT(--([Item]="A"),--(B2<[Value]))+1 " ?

This has to be for a table that will grow continuously. I'm not really sure what to substitute in for B2.

Perfect tony!

Thanks so much