Google Sheets – How to Make SUMIF Default to Blank Instead of 0

google docsgoogle sheetsgoogle-sheets-query

My goal is to sum values in a row if they are present and leave the cell empty it there are no values. I used a formula: =SUMIF(A1:C1; "<>"; A1:C1) – but Google Sheets inserts a default value 0 – I don't want the default value.

Example:
A, B, C colums are number values. D is the sum column.

This is how my formula works:

This is how I want it to work:

Best Answer

You can omit the 0 by wrapping tour formula with the IF function:

=IF(SUMIF(A3:C3;"<>");SUMIF(A3:C3;"<>");"")

(Depending on your sheet's locale, ; could be replaced with ,)

Related Solutions

Google-sheets – Error using “sumif” together with “address” function

The problem is that address returns a string, so you need to turn that string into a reference.

Try this:

=sumif(indirect(address(F2,G2,2)&":"&address(ROW(B2),COLUMN(B2))),B2,indirect(address(F2,H2,2)&":"&address(ROW(C2),COLUMN(C2))))

Google Sheets – SUMIF Not Working Troubleshooting

Looks as though, as with Excel, the string functions (LEFT, MID, RIGHT) return strings regardless of whether these look like numbers. So a solution is to force conversion with =VALUE hence:

=VALUE(LEFT(A2,FIND("x",A2)-1))

in ColumnL should provide the input required for the existing =SUMIF formula to function as intended.

Best Answer

Related Solutions

Google-sheets – Error using “sumif” together with “address” function

Google Sheets – SUMIF Not Working Troubleshooting

Related Topic