Multiple Condition for Status

Hi All

I would like to ask for formula on how to get the status based on the multiple condition:


Low - Combination of medium/low findings (should not be > 3 medium findings, otherwise rating should be upgraded to Medium)
Medium - 1. Combination of at least 1 high finding and medium/low findings
2. 4 Medium finding and above

High - 2-3 high findings
Critical - >= 4 high findings


I have attached the sample and expected result.

Thank you in advance.

Hello WBD - the 2nd formula using Index is working but how do i update it since the maximum count number for medium status is 30 and for High is 22 while for Low is 4. I have attached another sample.

Thank you so much WBD, formula work perfectly. I will now tag this query as solved and reputation has been added also to you.