Google Sheets – SUM Range with Mixed Strings and Numbers

google sheetsgoogle-sheets-arrayformula

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?

Best Answer

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+\.]+")), ))
Related Topic