Trying to Create a Dynamic Formula and need some guidance

Hi,

Working on an advanced Dynamic Formula where selections on the sheet drive the formula results where we have several different criteria driving different selections.

[Type] a selection would be either "A" or "B" AND [Milestone] selection is driving a Reference column to indicate the YEAR (ex. Milestone 1 = FY23, Milestone 2 = FY24)

If [TYPE] = A and [Milestone] = "Milestone 1" Then enter [FY23Rate]#

If [TYPE] = A and [Milestone] = "Milestone 2" Then enter [FY24Rate]#

If [TYPE] = B and [Milestone] = "Milestone 1" Then enter [FY23RateB]#

[TYPE] = B and [Milestone] = "Milestone 1" Then enter [FY24RateB]#

So essentially my question is how do i write the formula so that it will use both of the two selections to factor into the result?

Best Answer

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hi @GILLL023 - You'll need nested IFs with the AND function. Give this a try:

    =IF(AND([TYPE], A, [Milestone], "Milestone 1"), [FY23Rate]#, IF(AND([TYPE], A, [Milestone], "Milestone 2"), [FY24Rate]#, IF(AND([TYPE], B, [Milestone], "Milestone 1"), [FY23RateB]#, IF(AND([TYPE], B, [Milestone], "Milestone 2"), [FY24RateB]#))))

    Let me know if that works!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!