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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=397670

"xcelion" wrote...
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"))

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(IND IRECT("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" wrote in
message ...

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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=397670

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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=397670