Calculating SUM pending on dropdown selection

Hey all,

I'm trying to calculate a sum pending two different drop down selections but having challenges with making it work. Any help would be greatly appreciated!

=IF(OR(Coverage@row = "Travel", Coverage@row = "Local", Coverage@row = "2 Locations"), (SUM([# of Shifts]@row * 12))), (OR(Coverage@row = "Planning Only"), (SUM([# of Shifts]@row * 6)))





  • ^ It works until I add the second layer of the "Planning only."

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Matt Fruitman

    You don't need the second OR since you only have one Coverage option for the second part of your formula. Instead you'll want to have another IF statement as your second statement.

    You also don't need to say SUM if you're using the * function to multiply your values.

    Try this:

    =IF(OR(Coverage@row = "Travel", Coverage@row = "Local", Coverage@row = "2 Locations"), [# of Shifts]@row * 12, IF(Coverage@row = "Planning Only", [# of Shifts]@row * 6))

    This will multiply [# of Shifts] in this row by 12 if Coverage is Travel, Local, or 2 Locations.

    Otherwise, if Coverage is "Planning Only", it multiplies [# of Shifts] by 6.

    Is this what you were looking to do?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!