Sum of comma seperated values in cell(Reposting)

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.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>

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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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"))

"xcelion" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>

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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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"))

"xcelion" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>

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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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"))

"xcelion" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>

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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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"))

"xcelion" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>

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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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"))

"xcelion" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>

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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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"))

"xcelion" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> 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(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" <xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com> wrote in
message news:xcelion.1u5m2e_1124694331.4581@excelforum-nospam.com...
>
> 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
>