Showing highest selling product based on criteria

[creative999]

Hi

Trying to find:

Highest selling product (Y:Y) based on total sales (K:K)
And criteria (L:L and S:S).

The formula below works if theres a product that matches the L:L and S:S criteria. If theres no matching product from Y:Y, I was expecting it to return a ‘--’ value, instead it still returns a product name.

Any suggestions?

=IFERROR(LET(u,UNIQUE(sheet1!$Y2:$Y20000), INDEX(SORT(CHOOSE({1,2},u,SUMIFS(sheet1!$K2:$K20000, sheet1!$Y2:$Y20000,u ,sheet1!$L2:$L20000,"RESALE", sheet1!$S2:$S20000,"YES")),2,-1),SEQUENCE(1),{1})),"--")

 

[Fluff]

How about

=LET(u,UNIQUE(Sheet1!$Y2:$Y20000), s,SORT(HSTACK(u,SUMIFS(Sheet1!$K2:$K20000, Sheet1!$Y2:$Y20000,u,Sheet1!$L2:$L20000,"RESALE", Sheet1!$S2:$S20000,"YES")),2,-1),TAKE(FILTER(s,INDEX(s,,2)>0,"--"),1,1))

 

[creative999]

PERFECT !!! Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.