Hide 0 value in SUM calculations

I have a simple sheet with additions, it looks like this:

B column is for the sum of the values on the right, and if there is no value on a row, it shows 0, but let's say I want it to say 'None' instead, or just be empty if there is no value to sum. B row functions are currently like this:

=SUM(C7:M7)

How can I do it?

I would use a similar approach for replacing both blanks and text.

Similar to @doubleunary's second approach, I would trigger an error, but I would use that same aporoach in both cases and simply change the result based on preferred error handing (blank vs. string).

Multi-Row Formula

The following formula in `B2` will calculate the SUM for all rows in the range C2:M20

Return Empty Values

``````=BYROW(C2:M20, LAMBDA(row,
IFERROR(1/(1/SUM(row)))))
``````

Return A String

``````=BYROW(C2:M20, LAMBDA(row,
IFERROR(1/(1/SUM(row)),"text")))
``````

Return String but Skip Blank Rows

``````=BYROW(C2:M1000, LAMBDA(row,
IF(LEN(JOIN(,row)),
IFERROR(1/(1/SUM(row)),"Text"),)))
``````
• If returning blanks instead of text, the additional approach has no benefit.

To display a text string when the sum is zero, use this pattern:

``````=if(sum(C7:M7), sum(C7:M7), "(none)")
``````

To display a blank when the sum is zero, use `iferror()`:

``````=iferror(1 / sum(C7:M7) ^ -1)
``````

See iferror().

If your goal is simply to hide the `0` value, rather than replace it, you can apply a custom number format to the cells.

1. Navigate to Custom number format using any of:

1. `Format menu > Number > Custom number format`
2. Alt+Shift+O N B
3. Ctrl+1 N B * If Compatible Spreadsheet KB Shortcuts are enabled.

2. Custom number formats follow the following pattern:
`positive;negative;zero;text`

Therefore, any of the following formats would hide zero values:

3. `0;-0;;@`

4. `0;-0;`