Forum Discussion
[Excel Formula?] How to calculate a product's sales rank based on 3 criteria
Hi, is there a formula that can calculate a Product's Sales Rank in year 20xx (starting with 1 as the 'highest' rank in the year 20xx) that corresponds with the Product Name AND the Sales Year. The e...
=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.