Need smarter Excel rather than too many IFs

Hi,

I need a quicker and smarter formula for my incentive calculation, but all I can think of is IF IF IF, and I realized that moving forward, there are going to have more IFs if the incentive tier is increasing.

I have paste the attachmentTier_Sample.xlsx

Here is my explanation:

Under the cell IDS, LP , SP ,

If all of them are Tier 2, Result shows Tier 2
If 1 of the cell has Tier 1, Result shows Tier 1
If 1 of the cell has NO, Result shows NO (Regardless there are Tier 1 or Tier 2 exist)

Currently I'm using IF IF IF

Hope Anyone can suggest me any better Formulas that this

Thank you

Regards,
Richard

Copy it in E2 &
With two if
Use Ctrl+Shift+Enter to activate the array function

=IF(COUNTIF(A2:C2,"Tier 2")>1,"Tire 2",IF(OR(A2:C2="Tier 1",A2:C2="No"),"No"))
Punnam

Hi Puunam,

The Formula works if is it all the 3 cells are Tier 2, or 1 of them is not Tier 2. But the Tier 1 Is not working.

I hope that the formula is = the same with the Result column

Please advice

Thank You

HI Ford,

That is actually a great idea! using only Numbers. I have learn much

Thank You so much For, Puunam, I have learn a lot!

Hi , try this in E2

Use Ctrl+Shift + Enter
=IF(COUNTIF(A2:C2,"NO")>=1,"NO",IF(COUNTIF(A2:C2,"Tier 2")>=2,"Tier 2","Tire 1"))
Punnam