Forum Discussion
Excel IF/And formula
Hello I have the following IF/AND formula in a spreadsheet. =IF(AND(F7=1,B7=4),500,"0") It works but I need to expand it to =IF(AND(F7=1,B7=4),500,"0") plus IF F7 = 1 and B7 = 5 then the cell ...
I think you want each condition if the prior was FALSE. With simple calculation and logic it might be cleanest to use IFS() (assuming you have excel 365):
=IFS(AND(F7=1,B7=4),500, AND(F7 = 1 , B7 = 5) ,1000, AND( F7 = 1 , B7 =6), 1500, TRUE, "0")
alternatively cascading IF()
=IF(AND(F7=1,B7=4),500, IF(AND(F7 = 1 , B7 = 5) ,1000, IF(AND( F7 = 1 , B7 =6), 1500, "0")))
or a combo first checking for F7 being 1 then using SWITCH on B7
=IF(F7=1, SWITCH(B7, 4,500, 5 ,1000, 6, 1500, "0"), "0")
=IFS(AND(F7=1,B7=4),500, AND(F7 = 1 , B7 = 5) ,1000, AND( F7 = 1 , B7 =6), 1500, TRUE, "0")
alternatively cascading IF()
=IF(AND(F7=1,B7=4),500, IF(AND(F7 = 1 , B7 = 5) ,1000, IF(AND( F7 = 1 , B7 =6), 1500, "0")))
or a combo first checking for F7 being 1 then using SWITCH on B7
=IF(F7=1, SWITCH(B7, 4,500, 5 ,1000, 6, 1500, "0"), "0")