How to make an =SUM formula return zero when cells in the formula are blank?

Options

I'm making a simple smartsheet and have a column adding several cells. Then have a status ball change colors based on certain numerical conditions. I need the SUM formula to remain blank and not 0 because blank is blue (not started) and 0 is green meeting goal. Thanks in advance for any assistance.


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Alishia Topper

    You can use an IF statement, like above, but have it so that IF the SUM = 0, return blank, otherwise calculate the SUM.

    Try this:

    =IF(SUM([Performance Achievement]183:[Performance Achievement]194) = 0, "", SUM([Performance Achievement]183:[Performance Achievement]194))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Jeff M.
    Jeff M. ✭✭✭
    Options

    Hi,

    You could try:

    IF(ISNUMBER([COLUMN WITH NUMBER]@row), [COLUMN WITH NUMBER]@row + [COLUMN 2 WITH NUMBER], "")

    This was it will only add if there's a number in the cell, otherwise it will remain blank "".

  • Alishia Topper
    Options

    Thank you for your help! I'm sorry if this is a silly question but I'm only adding 12 cells within one column. I'm not sure what I need to place in the [COLUMN 2 WITH NUMBER] portion of the formula. Here is the sum formula I'm using that I want to not return a zero (0) if blank. =SUM([Performance Achievement]183:[Performance Achievement]194)

    Thanks again.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Alishia Topper

    You can use an IF statement, like above, but have it so that IF the SUM = 0, return blank, otherwise calculate the SUM.

    Try this:

    =IF(SUM([Performance Achievement]183:[Performance Achievement]194) = 0, "", SUM([Performance Achievement]183:[Performance Achievement]194))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Alishia Topper
    Options

    This works great! Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!