How to make an =SUM formula return zero when cells in the formula are blank?
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.
Best Answer

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

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 "".

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.

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 
This works great! Thank you.
Help Article Resources
Categories
Check out the Formula Handbook template!