I need your support to find the rank considering below

1. PO Number
2. Group
3. Start date

In this need to calculate different rank for all PO separately and also Need to pick the rank based on n the date but need to consider PO and Group separately

Result need to come as showing number in Rank (Column D)

Best regard Indika

CodePudding user response：

Not sure why your expected result for row 13 is 2, not 3, but try:

=COUNTIFS(A4:A18,A4:A18,B4:B18,B4:B18,C4:C18,"<="&C4:C18)

which will produce a spilled range containing your desired result.

CodePudding user response：

=LET(d,   A4:C18,
     a,   INDEX(d,,1),
     b,   INDEX(d,,2),
     c,   INDEX(d,,3),
MAP(      SEQUENCE(ROWS(d)),
LAMBDA(   r,
SUM((a=INDEX(a,r))*(b=INDEX(b,r))*(c<=INDEX(c,r))))))

This may be easier to maintain if your range changes. All you need to do is expand the range d.

Jos' solution calculates faster on large ranges though.