Add sum of part of array

[ricklou]

Goodday
I have an array in Column C -I would like to know if there might be a way to calculate the total value of the array before the separator >> ;1, 11, 13, 42, 47, 49 >> 2, 15, 3, 7, 4, 4. So basically 1+ 11+ 13+ 42+ 47+ 49 =163. is there a Formula that can do it with an array......normally those values need to be in rows to addsum. Thanx for any help.

Regards

Ricklou

[Haseeb Avarakkan]

Hello Ricklou,

If the cell contains only NUMBERS with separator, try this.

=SUMPRODUCT((0&TRIM(MID(SUBSTITUTE(C1,",",REPT(" ",250)),ROW(INDEX(C:C,1):INDEX(C:C,LEN(C1)+1))*250-249,250)))+0)

[ricklou]

Hi there Haseeb, thanx for your reply. Unfortunately that gives a #Value error. Just to be clear in C1 I have 1, 11, 13, 42, 47, 49 >> 2, 15, 3, 7, 4, 4 . In d1 I put your formula hoping to achieve the sum of 1, 11, 13, 42, 47, 49 =163. Dont know if its possible though so please inform me if this is doable or not.

Many thanks

Ricklou

[Haseeb Avarakkan]

Use LET function to take only part before >>

=SUMPRODUCT((0&TRIM(MID(SUBSTITUTE(LEFT(C1,FIND(">>",C1)-1),",",REPT(" ",250)),ROW(INDEX(C:C,1):INDEX(C:C,LEN(C1)+1))*250-249,250)))+0)

But, if a cell doesn't have >> or contains any text other than comma, before >> will give error.

If you have these, please post various samples of your data.

[ricklou]

Use LET function to take only part before >>

=SUMPRODUCT((0&TRIM(MID(SUBSTITUTE(LEFT(C1,FIND(">>",C1)-1),",",REPT(" ",250)),ROW(INDEX(C:C,1):INDEX(C:C,LEN(C1)+1))*250-249,250)))+0)

But, if a cell doesn't have >> or contains any text other than comma, before >> will give error.

If you have these, please post various samples of your data.

Your example works flawlessly thank you Haseeb