Sum in column that counts blanks as zero

[WinonaC]

I have a column that adds up to a total (X). In this column, some fields are blank, but need to be counted as zero, as I am getting an error that says the formula refers to empty cells. I do not want the zero to appear in the field, just have the blanks counted as zero. I tried a formula to ignore blanks, but it still gives the error. Whether it counts them as zero or ignores them, whichever provides the correct sum at the bottom.

 

[Coyotex3]

Take a look at this to see if this fixes the issue.

Redirecting

 

[Fluff]

Why not just use the SUM function, it will only sum numbers & ignore everything else.

 

[jdellasala]

@Fluff is correct. SUM will sum a range regardless of whether or not the range contains numbers or texts. Values that start with # like #N/A will typically cause an error as an answer, and TRUE will add 1.
If you have 0's displaying and don't want them to appear as blank cells, you can use the custom format #;-#;;@ which will hide 0 values.

 

[Fluff]

and TRUE will add 1.

Not with sum it won't.

 

[jdellasala]

Not with sum it won't.

Oops!

 

[WinonaC]

Take a look at this to see if this fixes the issue.

Redirecting

Thank you! That fixed it

 

[Coyotex3]

Thank you! That fixed it

Glad that worked. I recommend using the Sum function instead like @Fluff suggested. Much more efficient IMO.