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

Alishia Topper

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.

Genevieve P.

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

Jeff M.

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

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.

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

Alishia Topper

This works great! Thank you.