Ranking data based on criteria - Low to High

[JumboCactuar]

Hi,
similar to a question i asked here but now I'm trying to add rankings all the way down the data like below:
how can I add Column D using formula? i think with COUNTIFS but not too sure

Rank 1 = lowest price for item
data is always sorted item, then price low to high

thankyou

Item Code	Seller	Price	Rank
FR11	MapLtd	14.99	1
FR11	JustPro	15.11	2
FR11	KLG	15.19	3
FR11	ABCTech	16.49	4
GR44	JustPro	35.99	1
GR44	ABCTech	36.49	2
GR44	KLG	38.99	3
PG20	KLG	18.49	1
PG20	MapLtd	19.99	2
PG20	ABCTech	21.99	3
PG20	JustPro	22.79	4
ZD16	ABCTech	12.49	1
ZD16	JustPro	12.89	2
ZD16	KLG	12.99	3

 

[DRSteele]

What you're after is what we all want, a function called RANKIF. It does not exist. Unfortunately, function RANK.EQ does not allow filtering in the ref argument.

So here is a well-known work-around. For Item Code, it ranks the Price; the sort order won't matter.

=SUMPRODUCT(--($A$2:$A$15=$A2),--(C2>$C$2:$C$15))+1

 

[JumboCactuar]

What you're after is what we all want, a function called RANKIF. It does not exist. Unfortunately, function RANK.EQ does not allow filtering in the ref argument.

So here is a well-known work-around. For Item Code, it ranks the Price; the sort order won't matter.

=SUMPRODUCT(--($A$2:$A$15=$A2),--(C2>$C$2:$C$15))+1

thankyou
first time ive ever needed to use this

 

[JumboCactuar]

asking this here as its related to formula above

Im trying to set this down a column using VBA using the following:

    LR = Range("A65000").End(xlUp).Row
    Range("H2:H" & LR).Formula2R1C1 = "=SUMPRODUCT(--(R2C1:R100000C1=RC1),--(RC[-2]>R2C6:R100000C6))+1"

Formula
=SUMPRODUCT(--($A$2:$A$100000=$A2),--(F2>$F$2:$F$100000))+1

and its very slow calculating
does anyone know how to load the data into memory then perform the above, and output to sheet?