Forum Discussion

BHOOMIKAB's avatar
BHOOMIKAB
Copper Contributor
Sep 26, 2022

Help with nested ifs formula fiving error N/A

@

Hello, I'm trying to write a formula for below.

If the building is "restaurant", and type of systems is C33=YES,  C34=YES and C35=YES, then refer savings cell A, else refer to "others" savings cell B. Somehow I'm getting N/A, can anyone help me why is it so. Refer to my formula below:

 

Type of building

Restaurant

Others

 

Type of systems

C33- Yes/No

C34- Yes/No

C35- Yes/No

 

=IFS(Inputs!F28="Restaurant", IF(AND($C$33="Yes",$C$34="Yes",$C$35="Yes"),Savings!O43,IF(AND($C$33="Yes",$C$34="No",$C$35="Yes"),(Savings!O43+Savings!P43),IF(AND($C$33="Yes",$C$34="Yes",$C$35="No"),(Savings!O43),IF(AND($C$33="Yes",$C$34="No",$C$35="No"),(Savings!O43+Savings!P43+Savings!Q43),0)))),IF(Inputs!F28="others", IF(AND($C$33="Yes",$C$34="Yes",$C$35="Yes"),Savings!O44,IF(AND($C$33="Yes",$C$34="No",$C$35="Yes"),(Savings!O44+Savings!P44),IF(AND($C$33="Yes",$C$34="Yes",$C$35="No"),(Savings!O44),IF(AND($C$33="Yes",$C$34="No",$C$35="No"),(Savings!O44+Savings!P44+Savings!Q44),0))))),0)

 

Thanks in advance!

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 26, 2022

    BHOOMIKAB 

    =IFS(F28="Restaurant",IF(AND($C$33="Yes",$C$34="Yes",$C$35="Yes"),Savings!O43,IF(AND($C$33="Yes",$C$34="No",$C$35="Yes"),(Savings!O43+Savings!P43),IF(AND($C$33="Yes",$C$34="Yes",$C$35="No"),(Savings!O43),IF(AND($C$33="Yes",$C$34="No",$C$35="No"),(Savings!O43+Savings!P43+Savings!Q43),0)))),F28="Others",IF(AND($C$33="Yes",$C$34="Yes",$C$35="Yes"),Savings!O44,IF(AND($C$33="Yes",$C$34="No",$C$35="Yes"),(Savings!O44+Savings!P44),IF(AND($C$33="Yes",$C$34="Yes",$C$35="No"),(Savings!O44),IF(AND($C$33="Yes",$C$34="No",$C$35="No"),(Savings!O44+Savings!P44+Savings!Q44),0)))))

     

    This formula works in my sheet.

     

    Savings

    • BHOOMIKAB's avatar
      BHOOMIKAB
      Copper Contributor
      Sep 26, 2022

      OliverScheurich Thanks for your reply. This is why it was not working, of all the extra spaces and if. 

       

      =IFS(Inputs!F28="Restaurant", IF(AND($C$33="Yes",$C$34="Yes",$C$35="Yes"),Savings!O43,IF(AND($C$33="Yes",$C$34="No",$C$35="Yes"),(Savings!O43+Savings!P43),IF(AND($C$33="Yes",$C$34="Yes",$C$35="No"),(Savings!O43),IF(AND($C$33="Yes",$C$34="No",$C$35="No"),(Savings!O43+Savings!P43+Savings!Q43),0)))),IF(Inputs!F28="others", IF(AND($C$33="Yes",$C$34="Yes",$C$35="Yes"),Savings!O44,IF(AND($C$33="Yes",$C$34="No",$C$35="Yes"),(Savings!O44+Savings!P44),IF(AND($C$33="Yes",$C$34="Yes",$C$35="No"),(Savings!O44),IF(AND($C$33="Yes",$C$34="No",$C$35="No"),(Savings!O44+Savings!P44+Savings!Q44),0))))))

       

       

       

       

       

Resources