[Excel Formula?] How to calculate a product's sales rank based on 3 criteria

=LET(arr,LAMBDA(x,CHOOSE(x,SEQUENCE(ROWS(A2:A9)),SEQUENCE(,COLUMNS(D1:F1)))),
res,MAP(arr({1}),arr({2}),LAMBDA(a,b,COUNTIFS(A2:A9,INDEX(A2:A9,a,),A2:A9,RIGHT(CHOOSECOLS(D1:F1,b),4),C2:C9,">="&INDEX(C2:C9,a,)))),
IF(res=0,"",res))

In Excel for the web and Excel 365 you can use this formula that spills the result.

It uses HansVogelaar​ 's COUNTIFS and spills the result. Thanks as well to m_tarler​ for his PIVOTBY with very instructive use of LAMBDA and XMATCH.

so i'm not sure what is expected really.  I see a few likely options:

a) regardless of the year for this row give what rank it would be if it was in that year

b) only return the actual rank that row placed in that year

c) create a new chart using only the Product name and show the 2021,2022,2023 results

for a) you could use:

=XMATCH($C2,SORT($C$2:$C$9*($A$2:$A$9=2021),,-1),-1)

for b) you could add the conditional/if around it:

=IF($A2=2021,XMATCH($C2,SORT($C$2:$C$9*($A$2:$A$9=2021),,-1),-1),"")

for c) a pivotchart can give you the % rankings for each year which is close:

but instead you could use PIVOTBY and get it:

=PIVOTBY(B2:B9,A2:A9,C2:C9,LAMBDA(a,b,XMATCH(SUM(a),SORT(b,,-1),-1)),0,0,,0)

and attached is the file with the different examples

EDIT: and here is another option if you want that row to show the result of that product in that year so for example row 1 has 2022 Red Hat and you want the 2021 column to show how Red Hat did in 2021 even though that is found in a different row:

=XMATCH($B2:$B9,TAKE(SORT(FILTER($B$2:$C$9,$A$2:$A$9=--RIGHT(J$1,4)),2,-1),,1),-1)

file updated with this option too

Is this what you want?

The formula in D2 is

=IF(--RIGHT(D$1, 4)=$A2, COUNTIFS($A$2:$A$9, $A2, $C$2:$C$9, ">="&$C2), "")