Forum Discussion

Jerry-1951's avatar
Jerry-1951
Copper Contributor
Jun 06, 2023

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 value = 1000 followed by IF F7 = 1 and B7 =6 then the cell value +1500.

I don't know how to format that, would appreciate any help,

 

Thanks,

 

Jerry

 

Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 06, 2023
    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")

Resources