How to SUM a column with multiple entries in a single cell

[craigy111]

Good Morning,

I am new to this forum and a little bit new to Excel 2007.

I am struggling to come up with a formula to SUM a column with multiple entries in a single cell where other cells have single entries.
The values i require are costing therefore £ values in each column require to be added.

Is this possible? Do i need to extract the information to another sheet? If so what is the formula.

Any help on this would be much appreciated.

Thanks in advance
Craigy

 

[Special-K99]

Supply some example data and your expected output.
Make sure there is some variation in the example data somewhat.

 

[craigy111]

Hi Special-K99

Numbers to be added Column A1:A5 Where Cell A3 has multiple values in Sheet1
With TOTAL Column A6

Thanks

Column A
1 £4567
2
3 £4568
£4569
£5689
4

 

[Special-K99]

You could do it this way.
Bit fiddly, but gets the result.

Not sure I understood your data, here's what I did.

Blank Sheet.
in A1 enter £4567
in A3 enter £4568 Alt-Enter £4569 Alt-Enter £5689

If you have wrap text set on alignment tab you should see A3 consists of three rows of figures otherwise it will be three figures immediately after each other. But don't worry about this, it's purely appearances.

in B1
=SUBSTITUTE(A1,"£","+")
copy down the column

Now the tricky bit.
You need to use the EVALUATE formula but it works differently to other formulas.
Go to Formulas / Data Manager at top of screen and define a Named Range, e.g. AddTot, then in the "refers to" box enter =EVALUATE(Sheet1!$B$1:$B$3)

in C1
=AddTot
and copy down the column

Now column C should be the totals of each cell in column A so you can simply put a SUM(D1:D5) in D6 to add them up.

More info on EVALUATE function here:
https://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

 

[craigy111]

Thanks for that it works perfect.

A Very Happy Craigy