How to split and sum in google sheets-CodePudding

I am trying to figure out how to split the range in google sheets by "-" delimiter and add the result. Basically from the image below, I am trying to split on "-" and add up the ones (i.e 1 1 1 1 = 4). However, using the formula below it adds up all of the numbers (i.e 1 5 1 1 1 0 1 3 = 13) which is not what I want

CodePudding user response：

You are correctly splitting the values into to two columns of data, but then summing the entire dataset. You need to specify your summation to just the column you want (which appears to be the first column). Index function will do this probably the best as the second parameter can specify which column in a data set.

Summing the first column:

=sum(index(split(B11:B14,"-"),,1))

Summing the second column:

=sum(index(split(B11:B14,"-"),,2))

Showing all (same as your your arraysformula split)

=index(split(B11:B14,"-"))

See sample sheet here.