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

enter image description here

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:


How can I do it?


3 Answers 3


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,

Return A String

=BYROW(C2:M20, LAMBDA(row,

Return String but Skip Blank Rows

=BYROW(C2:M1000, LAMBDA(row,
  • 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:

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

  3. 0;-0;;@


  4. 0;-0;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.