RANK formula with yes/no conditions

[Newbie75]

Hi,
I have an evaluation sheet which currently only ranks on the price entered in row 33, the current formula is:
=IFERROR(RANK(F33,$F$33:$H$33,1),"")

But I would like to exclude from the ranking any supplier who has a 'No' in one of the mandatory criteria cells in their column but am really struggling with how to do this. Ideally I'd like a rank in row 34 for those who have all Yes's or blanks in and 'Does not qualify' in any that have a 'No' in any of the mandatory fields. So in the example below Supplier 3 would not be ranked and will show 'Does not quality' in cell H34 as it has a No in mandatory criteria 3. I am using Office 365. Hope this makes sense!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IFNA(XMATCH(F33:H33,SORT(BYCOL(F28:H33,LAMBDA(bc,IF(COUNTIFS(bc,"no"),"",SUM(bc)))),,,1)),"Does not qualify")

 

[Newbie75]

Hi & welcome to MrExcel.
How about

=IFNA(XMATCH(F33:H33,SORT(BYCOL(F28:H33,LAMBDA(bc,IF(COUNTIFS(bc,"no"),"",SUM(bc)))),,,1)),"Does not qualify")

Thank you for your reply, this is coming up with the Spill error below ...

 

[SunnyAlv]

Thank you for your reply, this is coming up with the Spill error below ...

View attachment 113621

i think because on cell G34 & H34 Have value, and u must delete it

 

[Newbie75]

i think because on cell G34 & H34 Have value, and u must delete it

That works perfectly, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.