# SUM a range of cells (sometimes string, sometimes numbers), parsing the cell if it is a string

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 '18 at 0:56

try this:

=SUMPRODUCT(IFERROR(ARRAYFORMULA(SUBSTITUTE(REGEXEXTRACT(TEXT(B2:B; "#.##");
"[\d+\.]+");
"."; ",")); ))

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

american syntax:

=SUMPRODUCT(IFERROR(ARRAYFORMULA(REGEXEXTRACT(TEXT(B2:B, "#.##"), "[\d+\.]+")), ))
• 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 '18 at 1:53
• 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 '18 at 2:09