Hi

Need to split cell values and sum.
Cell A1: 0,20,300,20,300
Cell A2: 280,34,200,129,0

Result for A1 = 640
Result for A2 = 643

Note that the length of cell and values are not consistent.

Any help much appreciate.

Hi,

Use text to columns to split the cell values then you can use the @sum
formula.

Hope this helps.

"Francois Taljaard" wrote:

Hi

Need to split cell values and sum.
Cell A1: 0,20,300,20,300
Cell A2: 280,34,200,129,0

Result for A1 = 640
Result for A2 = 643

Note that the length of cell and values are not consistent.

Any help much appreciate.

Did try it the cell is actually a vlookup and the formula much run in
a If formula.

The data is not consistent it might look something like
20,400,20,30 = 470.

Use the if statement to check for "=" if true I do a Mid to get the value
(470),
if false I need to sum (20,400,20,30).


Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This Row],[PRODUCT]],PRODUCTS!A:G,7)

Try this one

U have data in A1 in B1 put this formula =REPLACE(",",
1,1,"+")&SUBSTITUTE(A3,",","+")

and then copy | go to Edit | paste special | values | ok |



On Sep 8, 3:16*am, Francois Taljaard <Francois
wrote:
Hi

Need to split cell values and sum.
Cell A1: 0,20,300,20,300 *
Cell A2: 280,34,200,129,0

Result for A1 = 640
Result for A2 = 643

Note that the length of cell and values are not consistent.

Any help much appreciate.

Are you allowed to use VBA code? If so, this User Defined Function (UDF) can
do what you want...

Function CommaSum(R As Range) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(R.Value, ",")
For X = 0 To UBound(Parts)
CommaSum = CommaSum + Parts(X)
Next
End Function

To install it, press Alt+F11 to get into the VBA editor; once there, click
Insert/Module from its menu bar; then copy/paste the above code into the
code window that opened when you did that. You are done. Go back to your
worksheet and, assuming your values are in A1, A2, etc., enter this formula
in, say, B1...

=CommaSum(A1)

and copy it down. You can use the CommaSum function inside other functions
just like you can do with the built-in worksheet functions.

--
Rick (MVP - Excel)


"Francois Taljaard" wrote in
message ...
Did try it the cell is actually a vlookup and the formula much run in
a If formula.

The data is not consistent it might look something like
20,400,20,30 = 470.

Use the if statement to check for "=" if true I do a Mid to get the value
(470),
if false I need to sum (20,400,20,30).


Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This Row],[PRODUCT]],PRODUCTS!A:G,7)

Hi muddan

try it before not sure if I'm doing something wrong.

A1: 20,301,21,302,0


I copy B1 (+20+301+21+302+0), then paste special, with Values checked and
then OK.

the result is +20+301+21+302+0.

Hi Rick,

Thanks for the code I may also need it someday. Can you also help me with
the problem I post regarding Data Validation?

Thanks!

"Rick Rothstein" wrote:

Are you allowed to use VBA code? If so, this User Defined Function (UDF) can
do what you want...

Function CommaSum(R As Range) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(R.Value, ",")
For X = 0 To UBound(Parts)
CommaSum = CommaSum + Parts(X)
Next
End Function

To install it, press Alt+F11 to get into the VBA editor; once there, click
Insert/Module from its menu bar; then copy/paste the above code into the
code window that opened when you did that. You are done. Go back to your
worksheet and, assuming your values are in A1, A2, etc., enter this formula
in, say, B1...

=CommaSum(A1)

and copy it down. You can use the CommaSum function inside other functions
just like you can do with the built-in worksheet functions.

--
Rick (MVP - Excel)


"Francois Taljaard" wrote in
message ...
Did try it the cell is actually a vlookup and the formula much run in
a If formula.

The data is not consistent it might look something like
20,400,20,30 = 470.

Use the if statement to check for "=" if true I do a Mid to get the value
(470),
if false I need to sum (20,400,20,30).


Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This Row],[PRODUCT]],PRODUCTS!A:G,7)

On Sep 8, 4:06*am, Francois Taljaard
wrote:
Hi muddan

try it before not sure if I'm doing something wrong.

A1: 20,301,21,302,0

I copy B1 (+20+301+21+302+0), then paste special, with Values checked and
then OK.

the result is +20+301+21+302+0.


just click F2 and enter

I'm glad you picked my message up... I see I accidentally posted it under
someone else's reply.

I'll try to help you with your Data Validation question... see the question
I posed back to you in that original thread.

--
Rick (MVP - Excel)


"RaulDR" wrote in message
...
Hi Rick,

Thanks for the code I may also need it someday. Can you also help me with
the problem I post regarding Data Validation?

Thanks!

"Rick Rothstein" wrote:

Are you allowed to use VBA code? If so, this User Defined Function (UDF)
can
do what you want...

Function CommaSum(R As Range) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(R.Value, ",")
For X = 0 To UBound(Parts)
CommaSum = CommaSum + Parts(X)
Next
End Function

To install it, press Alt+F11 to get into the VBA editor; once there,
click
Insert/Module from its menu bar; then copy/paste the above code into the
code window that opened when you did that. You are done. Go back to your
worksheet and, assuming your values are in A1, A2, etc., enter this
formula
in, say, B1...

=CommaSum(A1)

and copy it down. You can use the CommaSum function inside other
functions
just like you can do with the built-in worksheet functions.

--
Rick (MVP - Excel)


"Francois Taljaard" wrote in
message ...
Did try it the cell is actually a vlookup and the formula much run in
a If formula.

The data is not consistent it might look something like
20,400,20,30 = 470.

Use the if statement to check for "=" if true I do a Mid to get the
value
(470),
if false I need to sum (20,400,20,30).


Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This Row],[PRODUCT]],PRODUCTS!A:G,7)

Thanks Rick.

Works 100%.


"Rick Rothstein" wrote:

Are you allowed to use VBA code? If so, this User Defined Function (UDF) can
do what you want...

Function CommaSum(R As Range) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(R.Value, ",")
For X = 0 To UBound(Parts)
CommaSum = CommaSum + Parts(X)
Next
End Function

To install it, press Alt+F11 to get into the VBA editor; once there, click
Insert/Module from its menu bar; then copy/paste the above code into the
code window that opened when you did that. You are done. Go back to your
worksheet and, assuming your values are in A1, A2, etc., enter this formula
in, say, B1...

=CommaSum(A1)

and copy it down. You can use the CommaSum function inside other functions
just like you can do with the built-in worksheet functions.

--
Rick (MVP - Excel)


"Francois Taljaard" wrote in
message ...
Did try it the cell is actually a vlookup and the formula much run in
a If formula.

The data is not consistent it might look something like
20,400,20,30 = 470.

Use the if statement to check for "=" if true I do a Mid to get the value
(470),
if false I need to sum (20,400,20,30).


Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This Row],[PRODUCT]],PRODUCTS!A:G,7)

Now that I look again... I see I did post it to the correct reply. (It's
late here, I'm getting tired, time for bed soon.<g)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I'm glad you picked my message up... I see I accidentally posted it under
someone else's reply.

I'll try to help you with your Data Validation question... see the
question I posed back to you in that original thread.

--
Rick (MVP - Excel)


"RaulDR" wrote in message
...
Hi Rick,

Thanks for the code I may also need it someday. Can you also help me with
the problem I post regarding Data Validation?

Thanks!

"Rick Rothstein" wrote:

Are you allowed to use VBA code? If so, this User Defined Function (UDF)
can
do what you want...

Function CommaSum(R As Range) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(R.Value, ",")
For X = 0 To UBound(Parts)
CommaSum = CommaSum + Parts(X)
Next
End Function

To install it, press Alt+F11 to get into the VBA editor; once there,
click
Insert/Module from its menu bar; then copy/paste the above code into the
code window that opened when you did that. You are done. Go back to your
worksheet and, assuming your values are in A1, A2, etc., enter this
formula
in, say, B1...

=CommaSum(A1)

and copy it down. You can use the CommaSum function inside other
functions
just like you can do with the built-in worksheet functions.

--
Rick (MVP - Excel)


"Francois Taljaard" wrote
in
message ...
Did try it the cell is actually a vlookup and the formula much run in
a If formula.

The data is not consistent it might look something like
20,400,20,30 = 470.

Use the if statement to check for "=" if true I do a Mid to get the
value
(470),
if false I need to sum (20,400,20,30).


Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This
Row],[PRODUCT]],PRODUCTS!A:G,7)