Formula- need to Sum OR Average based on another cell

I need some help with a formula. I have:

=IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row,IF([Goal Calculation]@row = "average", avg([Quarter 1]@row:[Quarter 4]@row))))

The problem is it is only updating to reflect SUM. If I change it to AVERAGE, the cell goes blank. So I switched it and put AVERAGE first and that worked, but then SUM went blank. So it's only reading the first part of the formula.

Any advice?

Tags:

Best Answer

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓

    Try your formula with the modification. The IF statements needed to be separated with more the the comma, I believe its because the SUM function needed to be closed before starting the next.


    =IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row), IF([Goal Calculation]@row = "average", AVG([Quarter 1]@row:[Quarter 4]@row)))

Answers

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓

    Try your formula with the modification. The IF statements needed to be separated with more the the comma, I believe its because the SUM function needed to be closed before starting the next.


    =IF([Goal Calculation]@row = "sum", SUM([Quarter 1]@row:[Quarter 4]@row), IF([Goal Calculation]@row = "average", AVG([Quarter 1]@row:[Quarter 4]@row)))

  • Perfect! Thank you SO much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!