Adding multiple numbers in one cell

  • Thread starter Thread starter pandd15
  • Start date Start date
P

pandd15

My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,
 
One way...

The formula *must* be entered in the cell to the immediate right of the cell
that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu Insert>Name>Define
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum
 
pandd15 said:
My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,
Click to expand...


=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)),
(99*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1))+1,99)))
 
I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing
something wrong. I am new to Excel so I must be missing something. In this
example what would the formula look like? The amount of numbers in C will
vary in each row.

a b c
d
1 700-2, 88-00,
900-4, 33-00,

2


3

Thanks so much.
 
700-2, 88-00,
900-4, 33-00,
Click to expand...

The "numbers" you just posted don't look anything like the the numbers you
posted in your original sample!

What are the dashes for?

If these are the numbers:
700-2, 88-00,
900-4, 33-00,
Click to expand...

Then what results do you expect?

What about that last comma? If there is not another number why is it there?
 
My appologies for not doing a good job of explaining; these numbers are
product #'s, and vary in format, some with dashes some without, they also
vary in the amount of numerals and some have letters. I am interested only
in the total of numbers, as in 4 for the example below. They will always
have a coma and space between the numbers.

Thanks for your quick response.
 
I am interested only in the total of numbers,
as in 4 for the example below.
Click to expand...

Ok, so this leaves me even more confused!

If the result you expect is 4 then you must want to COUNT the "numbers", not
SUM them.

If that's the case then counting the number of commas will work as long as
every cell is in the same format:

number, number,

If you also have cells like this:

number
number, number

Then you'll have to let us know.
 
The counting comma's works great but unfortunately there are many numbers
that don't have comma's after the last #.
 
Let's assume that there is at least one (and maybe more) spaces between each
"number". Count them and add one.

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1
 
Maybe this to sum all numbers in A1 (with any non-numeric delimiter):

=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)-
LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1))
 
Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1,
838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely
as an example.

Thanks,
 
To count numbers contained in A1 (with comma or any other delimiter) try:

=SUM(--(FREQUENCY(COLUMN(A:IV),COLUMN(A:IV)*ISERR(-MID("."&A1&".",COLUMN(A:IV),1)))>1))
 
Should have read more carefully, if it's only comma separated, perhaps

=LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,",","
"))," ",""))+1
 
Thanks Lori and everyone else for all the help. I have another question
about this same spreadsheet.

I have dates in several columns that track how long a processes take. These
dates are manually added however the process may still be incomplete thus
leaving a blank in that column. These blank columns are causing my
calculations to have incorrect #'s. Is there a way I can leave a cell blank
without having this happen?
 
pandd15 said:
Thanks Lori and everyone else for all the help. I have another question
about this same spreadsheet.

I have dates in several columns that track how long a processes take. These
dates are manually added however the process may still be incomplete thus
leaving a blank in that column. These blank columns are causing my
calculations to have incorrect #'s. Is there a way I can leave a cell blank
without having this happen?
Click to expand...


Probably, but without more information about your data layout and existing
formulas, a specific answer is not likely.
 
Back
Top