Sum of comma seperated values in cell(Reposting)

[xcelion]

Hi All,
Can anyone help me in writting a formula to find the sum of comma
separated numbers in a cell
Thanks in advance

 

[Harlan Grove]

Can anyone help me in writting a formula to find the sum of comma
separated numbers in a cell

....

Comma-separated numbers in a single cell? Not easy. You'd need to parse the
cell's text into an array and sum the array. Possible but ugly. If the these
numbers were in cell A1, the *array* formula would look like

=SUM(--MID(A1,SMALL(IF(MID(","&A1,seq,1)=",",seq),
ROW(INDIRECT("1:"&COUNT(1,1/(MID(A1,seq,1)=","))))),
SMALL(IF(MID(A1&",",seq,1)=",",seq),ROW(INDIRECT("1:"&
COUNT(1,1/(MID(A1,seq,1)=",")))))-SMALL(IF(MID(","&A1,
seq,1)=",",seq),ROW(INDIRECT("1:"&COUNT(1,1/(MID(A1,
seq,1)=",")))))))

Note that this needs a defined name like seq in the formula above referring
to an array of serial numbers beginning with one, like

seq referring to =ROW(INDIRECT("1:1024"))

 

[Bob Phillips]

Here is an alternative that doesn't need the named range and is *not* an
array formula

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT("1:"&L
EN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1)))),
FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE
(A1,B1,""))+2))))-
FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE
(A1,B1,""))+1))))-1))

THis version assumes the list in A1, and the separator character in cell B1
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[xcelion]

Thanks Bob and Harlanb foy your formula.
It's really wonderful.I tried this formula for some time but i
couldn't reach no where near this approach .Really MasterPiece

But Bob could you explain logic behid this formula.I tried stepping
through your formula but couldn't find any clue about your logic
..Iam a newbie .Could you help me in understanding the approach you have
taken in this formula so that next time i cloud write awesome formulas
like this

Thanks
Xcelion