multiple numbers , one cell, one total ex 1,3,7 = 11

**kai888** · 06-24-2012, 06:03 AM

Greetings everyone!

Long time viewer, first time poster.

I have A1 with multiple numbers, for example : 1,6,10
In B2 I would like to sum the numbers in A1, so it will total: 17

Thank you very much in advance.

Regards,
Kai

**oeldere** · 06-24-2012, 06:11 AM

What is the reason multiple numbers are in 1 cell?

Excel normaly has difficulties with that.

You can use text to collumn (Excel 2007 and higher) and then sum it.

**martindwilson** · 06-24-2012, 06:32 AM

select b1 this is importent
insert/name define
create a name say mysum and in the refers to box put
=EVALUATE(SUBSTITUTE(Sheet1!A1,",","+")) click ok
now in any cell put say 1,2,3 then in the cell immediately to its right put =mysum

**Marcol** · 06-24-2012, 06:33 AM

@ oeldere

You can use text to collumn (Excel 2007 and higher) and then sum it.

Text to Columns is available pre-2007, kai888 uses 2003 according to his/her profile.

@ kai888
How many numbers, seperated by commas, can you have?

**kai888** · 06-24-2012, 07:04 AM

Hey everyone,

I am really strongly impressed with the feedback..

I will give a brief scenario.

These cell references are connected to a Userform, Listbox with multiselect

The multiselection results are returned to a worksheet cell.

For example: A1, will have

Spinach 18, Lettuce 24, pumpkin 15.

It has to be in one cell because it is meant to show one persons order. Every row is for a different persons order.So if one persons order is in different cells, it will make each person calculation very difficult.

There could be up to 70 numbers in one cell.

Regards,
Kai

**martindwilson** · 06-24-2012, 07:18 AM

so that's nothing like you first post then? excel is designed to use separate cells you might as well us notepad and do the sums on an abacus!

**kai888** · 06-24-2012, 10:19 AM

Back to the drawing board on a few things.

My apologies , the initial information was useful so thank you for that.

As you can tell I am just starting in this area. I will try harder to redesign my questions to be more concise and specific.

Regards,
Kai

**oeldere** · 06-24-2012, 10:25 AM

No apology necessary.

Thanks for the reply.

If the question is solved, will you mark your question as solved?

If you have (other) questions, just ask.