Hide 0 value in SUM calculations

Question

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?

Answer 1

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.

Answer 2

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().

Answer 3

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;