Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to enter
=sum in each individual cell?

I hope I'm being clear. Please help!

Is it an option to split the data into 4 columns? Data/Text To
Columns/Delimited, delimiter = ","

ensure the columns to the right of your data are empty (when the data gets
split, it will overwrite whatever is in the adjacent columns), select your
data, separate the data using text to columns, then just use the sum function
and copy it as far down or across as needed. keep a backup in case of
mishaps.


"SUPER EA" wrote:

Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to enter
=sum in each individual cell?

I hope I'm being clear. Please help!

SUPER EA <SUPER wrote...
Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one
cell. . . . I need for each cell to be summed up individually. So
if I have 100, 200, 300 in a cell, I need it to add up and read 600.
....

Define the name seq referring to the formula

=ROW(INDEX(Sheet2!$1:$65536,1,1):INDEX(Sheet2!$1:$ 65536,64,1))

then try the array formula

=SUM(IF(MID(","&A1,seq,1)=",",--MID(A1,seq,FIND(",",A1&",",seq)-seq)))

For example, if A1 contained 47, 94, 4, 65, 471, 487, this formula
returns 1168.

SUPER EA

Select the cells and run this macro.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 17:20:02 -0700, JMB wrote:

Is it an option to split the data into 4 columns? Data/Text To
Columns/Delimited, delimiter = ","

ensure the columns to the right of your data are empty (when the data gets
split, it will overwrite whatever is in the adjacent columns), select your
data, separate the data using text to columns, then just use the sum function
and copy it as far down or across as needed. keep a backup in case of
mishaps.


"SUPER EA" wrote:

Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to enter
=sum in each individual cell?

I hope I'm being clear. Please help!

Seems like it would work, except it's not adding the numbers correctly.

When using this on my example of 100, 200, 300 in cell A1, after running the
macro, the formula displayed above reads: =sum(0, 200, 300) ......the cell
then reads the total of 500




"Gord Dibben" wrote:

SUPER EA

Select the cells and run this macro.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 17:20:02 -0700, JMB wrote:

Is it an option to split the data into 4 columns? Data/Text To
Columns/Delimited, delimiter = ","

ensure the columns to the right of your data are empty (when the data gets
split, it will overwrite whatever is in the adjacent columns), select your
data, separate the data using text to columns, then just use the sum function
and copy it as far down or across as needed. keep a backup in case of
mishaps.


"SUPER EA" wrote:

Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to enter
=sum in each individual cell?

I hope I'm being clear. Please help!

Here's another way.

Assume:

A1 = 100, 200, 300

Enter this formula in B1:

="=SUM("&SUBSTITUTE(A1," ","")&")"

This will return a *text string* that looks like a formula:

=SUM(100,100,100)

To convert it to a real formula and get a numeric result:

With B1 still selected goto EditCopy then EditPaste SpecialValuesOK then
EditReplace

Find what: =
Replace with: =
Replace (or Replace all if you do a group of cells at once)
Close

--
Biff
Microsoft Excel MVP


"SUPER EA" <SUPER wrote in message
...
Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I
have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell
individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to
enter
=sum in each individual cell?

I hope I'm being clear. Please help!

Apologies

SUM_Add was originally another routine.

I forgot to remove the "-1" that the other needed. Try this revision.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula))
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord

On Wed, 25 Jul 2007 20:40:01 -0700, SUPER EA
wrote:

Seems like it would work, except it's not adding the numbers correctly.

When using this on my example of 100, 200, 300 in cell A1, after running the
macro, the formula displayed above reads: =sum(0, 200, 300) ......the cell
then reads the total of 500




"Gord Dibben" wrote:

SUPER EA

Select the cells and run this macro.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 17:20:02 -0700, JMB wrote:

Is it an option to split the data into 4 columns? Data/Text To
Columns/Delimited, delimiter = ","

ensure the columns to the right of your data are empty (when the data gets
split, it will overwrite whatever is in the adjacent columns), select your
data, separate the data using text to columns, then just use the sum function
and copy it as far down or across as needed. keep a backup in case of
mishaps.


"SUPER EA" wrote:

Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to enter
=sum in each individual cell?

I hope I'm being clear. Please help!

I WANT TO THANK EVERYONE FOR THEIR VERY HELPFUL SUGGESTIONS. I TRIED THEM
ALL SO THANK YOU. I HAD THE BEST RESULTS USING GORD'S BELOW.
THANK YOU SO MUCH GORD!!! IT WORKED LIKE A CHARM.

"Gord Dibben" wrote:

Apologies

SUM_Add was originally another routine.

I forgot to remove the "-1" that the other needed. Try this revision.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula))
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord

On Wed, 25 Jul 2007 20:40:01 -0700, SUPER EA
wrote:

Seems like it would work, except it's not adding the numbers correctly.

When using this on my example of 100, 200, 300 in cell A1, after running the
macro, the formula displayed above reads: =sum(0, 200, 300) ......the cell
then reads the total of 500




"Gord Dibben" wrote:

SUPER EA

Select the cells and run this macro.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 17:20:02 -0700, JMB wrote:

Is it an option to split the data into 4 columns? Data/Text To
Columns/Delimited, delimiter = ","

ensure the columns to the right of your data are empty (when the data gets
split, it will overwrite whatever is in the adjacent columns), select your
data, separate the data using text to columns, then just use the sum function
and copy it as far down or across as needed. keep a backup in case of
mishaps.


"SUPER EA" wrote:

Hello. I have a spreas sheeit in which each cell has various number
seperated with commas. For e.g. 100, 200, 300, 400 are in one cell. I have
an entire worksheet in which each cell has multiple numbers and I need for
each cell to be summed up individually. So if I have 100, 200, 300 in a
cell, I need it to add up and read 600. I can go into each cell individually
and enter the =sum but that would take me forever with an entire spread
sheet. Is there a way I can highlight the spreadsheet and enter a formula
that will automatically add up each individual cell so I don't have to enter
=sum in each individual cell?

I hope I'm being clear. Please help!

Hi

For a formula method, as opposed VBA code you could use the following method

With your cursor in cell B1
InsertNameDefine Name Addcell Refers to
=EVALUATE(SUBSTITUTE(Sheet1!$A1,", ","+"))

In cell B1 enter
=Addcell and copy down


--
Regards
Roger Govier



"SUPER EA" wrote in message
...
I WANT TO THANK EVERYONE FOR THEIR VERY HELPFUL SUGGESTIONS. I TRIED THEM
ALL SO THANK YOU. I HAD THE BEST RESULTS USING GORD'S BELOW.
THANK YOU SO MUCH GORD!!! IT WORKED LIKE A CHARM.

"Gord Dibben" wrote:

Apologies

SUM_Add was originally another routine.

I forgot to remove the "-1" that the other needed. Try this revision.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula))
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord

On Wed, 25 Jul 2007 20:40:01 -0700, SUPER EA

wrote:

Seems like it would work, except it's not adding the numbers correctly.

When using this on my example of 100, 200, 300 in cell A1, after running
the
macro, the formula displayed above reads: =sum(0, 200, 300) ......the
cell
then reads the total of 500




"Gord Dibben" wrote:

SUPER EA

Select the cells and run this macro.

Sub SUM_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If Not cel.Formula Like "=SUM(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=SUM(" & myStr & ")"
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 17:20:02 -0700, JMB
wrote:

Is it an option to split the data into 4 columns? Data/Text To
Columns/Delimited, delimiter = ","

ensure the columns to the right of your data are empty (when the data
gets
split, it will overwrite whatever is in the adjacent columns), select
your
data, separate the data using text to columns, then just use the sum
function
and copy it as far down or across as needed. keep a backup in case
of
mishaps.


"SUPER EA" wrote:

Hello. I have a spreas sheeit in which each cell has various
number
seperated with commas. For e.g. 100, 200, 300, 400 are in one
cell. I have
an entire worksheet in which each cell has multiple numbers and I
need for
each cell to be summed up individually. So if I have 100, 200, 300
in a
cell, I need it to add up and read 600. I can go into each cell
individually
and enter the =sum but that would take me forever with an entire
spread
sheet. Is there a way I can highlight the spreadsheet and enter a
formula
that will automatically add up each individual cell so I don't have
to enter
=sum in each individual cell?

I hope I'm being clear. Please help!