Google Sheets – Sum Numbers in Range with Text and Numbers

google sheets

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))))))

Best Answer

Try this:

 =ARRAYFORMULA(SUM(--REGEXEXTRACT(A1:A6,"(\d+)")))
Related Topic