CODEWITHSUNDEEP
google-sheetssum
user3871
user3871

Reputation: 12718

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?

Upvotes: 1

Views: 405

Answers (4)

Anders Maarup Ellgaard
Anders Maarup Ellgaard

Reputation: 1

Depending on where you are in the world, the parameters of google sheets functions are separated by different characters.

US version using comma seperation: =Sum(SPLIT(AG24,";"))

EU version using semicolon: =Sum(SPLIT(AG24;";"))

I'm located in EU but only found US documentation, so I was confused for a long time

Upvotes: 0

player0
player0

Reputation: 1

  • =SUMPRODUCT(SPLIT(AG24, ";"))

  • =SUMPRODUCT(SPLIT(REGEXREPLACE(AG24,"\D+"," ")," "))

will work as well...

Upvotes: 0

shabnam bharmal
shabnam bharmal

Reputation: 584

You can try the below formula which will directly convert the string to array ad then make a sum of it.

=Sum(SPLIT(AG24,";"))

Hope it helps!

Upvotes: 1

pnuts
pnuts

Reputation: 59475

Please try:

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

Upvotes: 0

Related Questions