I am trying to get the sum of a column of cells, which sometimes contains numbers and sometimes contains a string with numbers in it. I've found that the formula

=arrayformula(sum(0+iferror(regexextract(B2:B, "[\d\.]+"))))

works for parsing strings into numbers and SUMming them, but because of regex, it ignores anything that is not a string (ie, already a number). An example set of data might be:

B2: 1000.56
B3: $254.32
B4: ~125
B5: 321.43
B6: ~1030

The result of which should be

1000.56 + 254.32 + 125 + 321.43 + 1030 = 2731.31

Using the above formula though, the values of B2 and B5 are ignored, as they are not a string, and so regex will not parse them

Is this possible, or would it end up just being completely convoluted?

  • @user0 oops, updated with an expected result
    – GtwoK
    Sep 3, 2018 at 0:56

1 Answer 1


try this:

                                                         "."; ",")); ))

column B will be converted to TEXT values with keeping number format "#.##" and then extracted numbers are treated with SUBSTITUTE to replace . with , (otherwise result would be rounded to 2730) then it's fed to ARRAYFORMULA and to get rid of #N/A error from blank cells, all is wrapped in IFERROR and finally summed with SUMPRODUCT returning requested 2731.31

enter image description here

american syntax:

  • 1
    Hmm, I've entered your exact formula but can't replicate the result; only the strings are being added. Any idea what I did wrong? docs.google.com/spreadsheets/d/…
    – GtwoK
    Sep 3, 2018 at 1:53
  • 1
    Yeah, you are using US version of spreadsheets which calculates by different means than European spreadsheet. I've edited my answer - use 2nd formula.
    – user0
    Sep 3, 2018 at 2:09

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.