Large IF Statement with multiple logical test

[ccamp88]

Hello,

I am having a hard time with continuing to add onto this formula with multiple logical test. In my screenshot attached you will see in Cell M5 I have the following IF statement:
IF(OR(C5="General Surgery Volume",C5="GYN Volume"),IF(AND(L5>=0,L5<3),"<3",IF(AND(L5>=3,L5<6),"3 - 5",IF(AND(L5>=6,L5<11),"6 - 10",IF(AND(L5>=11,L5<15),"11 - 14",IF(L5>=15,"15+",""))))))

Which works perfectly if anything in Column C is General Surgery Volume or GYN volume. My question is how to I add onto this formula where as if something in column C is Ortho Volume or Cardiology volume it will return a different value? Below I have posted the IF statement that would apply to Ortho or Cardiology volume:
IF(OR(C5="Ortho Volume",C5="Cardiology Volume"),IF(AND(L5>=0,L5<5),"<5",IF(AND(L5>=5,L5<11),"5 - 10",IF(AND(L5>=11,L5<16),"11 - 15",IF(AND(L5>=16,L5<21),"16 - 20",IF(L5>=21,"21+",""))))))

my question is how do I combine these two statement together without it producing an error? If these statements are run separately I have no problem but whenever I go to combine them so that the value is dictated based on what column C is show I get a formula error. Please help I am having such a hard time finding a solution for this

 

[snjpverma]

Eric, when I copy the data to my sheet, I get "3-5" captured in the date format as 3/5/2021. Using ' before "3-5" is the only option to stop it from getting converted to date?

 

[Eric W]

Yes, that is somewhat annoying. You can also format the cell as Text before entering the "3-5" value.