2

I have a Google spreadsheet with cells having the following format:

A1: 1 Y
A2: 0.5 N
A3: 2 N
A4: (empty)
A5: (empty)
A6: 1.3 N

I would like to sum the numbers in the cells, i.e. (1+0.5+2+1.3) while ignoring empty cells.

This is the formula I'm using right now, but it does not work with empty cells:

ARRAYFORMULA(SUM(VALUE(LEFT(A1:A6,FIND(" ",A1:A6)-1))))

This is the error I get in Google Sheets:

Function FIND parameter 2 value should be non-empty.

How I can modify this formula to ignore empty cells?

1

I would use a helper column but if you can't suggest:

=arrayformula(sum(value(if(A1:A="","",left(A1:A,find(" ",A1:A))))))  

I assume only six cells was for example purposes.

1

Use IFERROR

Example

=ARRAYFORMULA(SUM(IFERROR(VALUE(LEFT(A1:A6,FIND(" ",A1:A6)-1)),)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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