# Formula- need to Sum OR Average based on another cell

Options

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.

Tags:

• ✭✭✭
Options

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)))

• ✭✭✭
Options

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)))

• Options

Perfect! Thank you SO much!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!