Using Sumproduct for group ranking

Question

Group	Product	Value	Ranking
Group A	Salmon	2000	1
Group A	Trout	900	2
Group B	Pencil	500	2
Group B	Rubber	700	1
Group C	Smirnoff Vodka	1200	1
Group C	Heineken	900	2

I'm trying to achieve "Ranking by use of Sumproduct formula" in each group, resulting in the "Value" in each group to have an ascending order.

I can do it in excel as a formula:

=SUMPRODUCT(($A$2:$A$10000=A2)*($C$2:$C$10000>C2))+1

However, I want to achieve that in VBA; I tried to do it but it still shows a bug in the code below (For this one I need a for loop version until the lastrow):

Sub Ranking()

lastrowA = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'Find a lastrow of column A

Dim i As Integer

Dim groupRange As Range
Dim productRange As Range

Set groupRange = Sheet1.Range("A:A" & lasrowA)  'Cost Center Name Range
Set productRange = Sheet1.Range("B:B" & lasrowA)  'Store Name Range

For i = 2 To lastrowA

Cells(i, 4).Value = Evaluate("Sumproduct((A2:A10000=Cells(i,1))*(A2:A10000>Cells(i,2)))") + 1

Next i

End Sub

Answer 1

SUMPRODUCT returns a single value, therefore you use VBA to repeat the calculation for each row. You could also use one of the following formulas to get a SPILL in one go using Office 365:

=COUNTIF(A2:A7,A2:A7)-COUNTIFS(A2:A7,A2:A7,C2:C7,"<"&C2:C7)

=1+MMULT((TOROW(A2:A7)=A2:A7)*(TOROW(C2:C7)>C2:C7),SEQUENCE(ROWS(A2:A7),,,0))

=LET(a,A2:A7,c,C2:C7,MAP(a,c,LAMBDA(x,y,1+SUM((a=x)*(c>y)))))

Change the range to your needs.

Answer 2

Option Explicit

Sub Ranking()

    Dim lastrowA As Long, f As String

    f = "=Sumproduct(($A$2:$A$10000=A2)*($C$2:$C$10000>C2)) + 1"
    With Sheet1
       'Find a lastrow of column A
       lastrowA = .Cells(.Rows.Count, "A").End(xlUp).Row
       .Range("D2:D" & lastrowA).Formula = f
    End With

End Sub