Sum of one cell

[madtai]

If I have the following in Cell A1 "30, 20, 10"

How do I add those numbers to put in line B1?

Thanks in advance

[Richard Schollar]

Well, a roundabout way would be:

1. Go Insert>Name>Define and enter a name like SumLeft and in the RefersTo box type:

=EVALUATE(SUBSTITUTE(OFFSET(INDIRECT("RC",FALSE),0,-1),",","+"))

(including = sign at beginning) and click OK

2. in B1 type

=SumLeft

and press return

Entering =SumLeft in a cell will always sum the contents of the cell to its immediate left where it contains a string of numbers separated by commas.

Hope this helps!

Richard

[MrShorty]

Do those number have to all stay in A1? If it were me, I would parse A1 into columns (Data->Text to columns work through text import wizard), then B1=sum(A1:C1)

[madtai]

Yes, all those numbers do have to stay in A1.

I tried to use the "SumLeft" approach and came up with this '#VALUE!'

Please help!!

Thanks

[VBA Noob]

This should help

VBA Noob

[Richard Schollar]

Thanks VBA Noob

Should have done that myself to begin with...


Richard

[VBA Noob]

No problem.

Nice formula by the way

VBA Noob