Sum Multiple Values In A Single Cell

RUCEE

New Member
Joined
May 6, 2010
Messages
2
I am trying to sum multiple values contained in single cell.
I have the following data in Column A:
9.5/4/6
3/4/6.5/7.8

My list goes on for over 700 entries.
The number of values separated by a slash changes as does the number of decimal points in each value.

I need to get the sum of each row to show up in Column B.

I prefer a non VBA solution if possible.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Welcome to the Board...

This would be tricky to do via formula.

Have you considered Text to Columns with a "/" Delimiter?

Matty
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Can you parse it out with Data-->Text to Columns-->Delimited-->"/"?

Then just sum from the last new column back. That will be much easier.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Or a UDF that goes in a regular module

Code:
Function SplitSum(r As Range) As Double
Dim i As Integer, X
X = Split(r.Value, "/")
For i = LBound(X) To UBound(X)
    SplitSum = SplitSum + X(i)
Next i
End Function


To use

=splitsum(A1)
 

RUCEE

New Member
Joined
May 6, 2010
Messages
2

ADVERTISEMENT

I could separate the data into columns but it would make the sheets messy and not very dynamic. Sometimes there is only 1 value and others there are 6.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Rucee
Welcome to the board

Try:

=SUM(0+(0&MID(A1,FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})),FIND("#",SUBSTITUTE(A1&"//////","/","#",{1,2,3,4,5,6}))-FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})))))
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Hi Rucee
Welcome to the board

Try:

=SUM(0+(0&MID(A1,FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})),FIND("#",SUBSTITUTE(A1&"//////","/","#",{1,2,3,4,5,6}))-FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})))))

Good grief! It'll take me at least a week to figure out how that works (y)
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
I had a play around with a few options, but failed miserably...

Respect, pgc01 (y)

Now to figure out how it works!

Matty
 

RUCEE

New Member
Joined
May 6, 2010
Messages
2
VoG, your solution works the best so far. Only one problem: after I copy the formula down for all of my entries, I cannot sum that column. I get a #Value! error.


pgc01, your solution works also, but only up to 6 values. Anyway to make it not care how many values there are? I do like that it is a formula however.


Thanks all for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top