Sum of Values appearing in one single cell

[Deepak Sharma]

Hi Everyone,

I am struggling with one formula to SUM of all the numbers given in a single cell, separated by few special characters. In B2 and B3, it's giving correct answers but not in rest of the cells.

'=SUM(IF(ISERR(VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1))),0,VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1))))

I have also tried to substitute the special characters with plus (+) sign but that converted to text, now not sure how to add them.

Please see attached file.

Regards,

 

[shrivallabha]

With your dataset you can try below formula. It is ARRAY formula so you need to use CTRL+SHIFT+ENTER:

=SUM(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&A2&REPT(",0",9),"""",""),",",REPT(" ",99)),":",REPT(" ",99)),99*ROW($A$1:$A$9),99))+0)

It will handle up to 9 delimited values. If you have more then red colored portion needs to be adjusted.

Green colored construct needs to be added if your dataset has more delimiter types.

 

[Deepak Sharma]

Thank you so much Shrivallabha, Got it.

Regards,
Deepak Sharma