Google Sheets – SUMIF Not Working Troubleshooting

google sheets

I have values in column L and I'd like to SUM what is in column B IF the value in L is greater than 1600

Here is my formula:

=SUMIF(L2:L,">1600",B2:B)

I'm getting a result of 0 in the cell currently (expecting some numbers as there are values in L larger than 1600)

Best Answer

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.

Related Solutions

Google-sheets – How to zero out cell if filtered sum is less than 0

For your first question, I'd expect the MAX() function to give you what you need.

=MAX(SUM(A1:A5),0)

MAX() returns the maximum value in a list of arguments. In this case, if the SUM() returns a value less than zero (i.e., negative) it'll return the zero instead.

Google Sheets – Combining SUMIF and FIND Functions

Untested but please try (if only to satisfy my curiosity!):

=arrayformula(SUMIF(VALUE(LEFT(A2:A,FIND("x",A2:A)-1)),N3,$B$2:B))

Best Answer

Related Solutions

Google-sheets – How to zero out cell if filtered sum is less than 0

Google Sheets – Combining SUMIF and FIND Functions

Related Topic