I've got a range of cells in Google Sheets that contain a mixture of text and numbers, e.g. Yes 46, Yes 35, No 23, Yes 21, Yes 14, etc.

Can anyone help me with a formula that would add up the numbers in the above range of cells? So the formula should ignore any text, and just add up the sum of the numbers within the cells.

So in the above examples, I'd want the sum of 46 + 35 + 23 + 21 + 14, which would be 139.

Update: I was trying this previously, but it only works on a single cell, not on a range of cells. It was giving me a #VALUE! error:

=arrayformula(sum(value(if(N4:O4="","",left(N4:O4,find(" ",N4:O4))))))  

1 Answer 1


Try this:


Your Answer

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

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