Search code examples
google-sheetssum

sum comma delimited string of integers

In Google Sheets - I need to sum a set of numbers, where the initial cell contains delimiters and non numerics:

3; 6; 1; 3; None; 1; 1

I first replace all spaces and non numerics:

=REGEXREPLACE(AG24,"\D+",",")

Which gives: 3,6,1,3,1,1

Since =SUM(3,6,1,3,1,1) correctly provides 15, I figured I'd try passing in the REGEXREPLACE result into SUM() and magically have it compute, but doing so yields 0:

=SUM(REGEXREPLACE(AG24,"\D+",",")) = 0

I kind of expected that...

I've also tried SUMPRODUCT, which also yields 0:

=SUMPRODUCT(ARRAYFORMULA(REGEXREPLACE(AG24,"\D+",","))) = 0

Question: so how can I sum the list of string integers?

Solution

  • Please try:

    =sum(split(REGEXREPLACE(AG24,"\D+",","),","))