Building Complex Formula


I want to capture our planned cost based on the below criteria:

  • IF Cost Rate Override Status is marked NO, AND FY Status Column says FY22 then multiply 2022 Cost Rate by Planned Hours
    IF FY Status Column says FY23, multiply 2023 Cost Rate by Planned Hours

  • IF Cost Rate Override Status is marked YES, then multiple the cost rate override by Planned Hours

Current formula im building and getting errors on is:

IF(AND({Cost Override?}=“No”,{FY}=“FY22”),{2022 Cost Rate}*
{Planned/Sold}),IF(AND({Cost Override?}=“No”,{FY}=“FY23”),{2023 Cost Rate}*
{Planned/Sold}), IF({Cost Override?}=“Yes”,{Cost Rate Override}*{Planned/Sold})

Where am i messing this up?

Hi @Aschne43,

You need to nest your if statements. You can’t add them one after the other.
You’re doing:


whereas you should do:



If(AND({Cost Override?}="No",{FY}="FY22"),{2022 Cost Rate}*{Planned/Sold},IF(AND({Cost Override?}="No",{FY}="FY23"),{2023 Cost Rate}*{Planned/Sold},IF({Cost Override?}="Yes",{Cost Rate Override}*{Planned/Sold})))