I need your support to find the rank considering below
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
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.
=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.
There has been some interest in Google Sheets in finding a faster way to assign sequence numbers to groups here. For interest using one of the suggested approaches (which is more or less linear in N, the number of rows of data) would look like this translated into Excel:
=LET(startrow,4,
rows,COUNTA(A:A)-1,
endrow,startrow+rows-1,
Data,INDEX(A:A,startrow):INDEX(C:C,endrow),
sData,SORTBY(Data,INDEX(Data,,1),1,INDEX(Data,,2),1,INDEX(Data,,3),1),
sRows,SORTBY(ROW(Data),INDEX(Data,,1),1,INDEX(Data,,2),1,INDEX(Data,,3),1),
ranks,SCAN(1,SEQUENCE(rows),LAMBDA(a,c,IF(c=1,1,IF(OR(INDEX(INDEX(sData,,1),c-1)<>INDEX(INDEX(sData,,1),c),INDEX(INDEX(sData,,2),c-1)<>INDEX(INDEX(sData,,2),c)),1,a+1)))),
SORTBY(ranks,sRows))
but clearly the extra complexity would only be worthwhile if you had a lot of data.
If you just wanted ranks based on the first and third columns and they were pre-sorted, the formula would be the much simpler:
=LET(startrow,4,
rows,COUNTA(A:A)-1,
endrow,startrow+rows-1,
Data,INDEX(A:A,startrow):INDEX(A:A,endrow),
SCAN(1,SEQUENCE(rows),LAMBDA(a,c,IF(c=1,1,IF(INDEX(Data,c-1)<>INDEX(Data,c),1,a+1)))))
