Forum Discussion

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 ce...

excel

Formulas and Functions

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
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))))))
- OliverScheurich
  Gold Contributor
  Sep 26, 2022
  You are welcome.