2

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
0

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

enter image description here


american syntax:

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

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.