Sum values within a single cell

macropod · #1 05-09-2014, 06:20 PM

You could use a UDF for this:

Code:

Public Function SUMCELLNUMS(Source As Range, StrSplit As String)
Dim StrIn As String, StrTmp As String, ValOut, i As Long
StrIn = Trim(Replace(Source.Text, "'", ""))
For i = 0 To UBound(Split(StrIn, StrSplit))
  StrTmp = Trim(Split(StrIn, StrSplit)(i))
  If IsNumeric(StrTmp) Then
    ValOut = Evaluate(ValOut + StrTmp)
  End If
Next
SUMCELLNUMS = ValOut
End Function

Simply add the above function to a normal code module in the workbook, then use a formula like:
=SUMCELLNUMS(A1, ",")
where A1 is the cell reference and "," is the delimiter.

shabbaranks · #2 05-11-2014, 11:53 PM

Quote:

Originally Posted by macropod

You could use a UDF for this:

Code:

Public Function SUMCELLNUMS(Source As Range, StrSplit As String)
Dim StrIn As String, StrTmp As String, ValOut, i As Long
StrIn = Trim(Replace(Source.Text, "'", ""))
For i = 0 To UBound(Split(StrIn, StrSplit))
  StrTmp = Trim(Split(StrIn, StrSplit)(i))
  If IsNumeric(StrTmp) Then
    ValOut = Evaluate(ValOut + StrTmp)
  End If
Next
SUMCELLNUMS = ValOut
End Function

Simply add the above function to a normal code module in the workbook, then use a formula like:
=SUMCELLNUMS(A1, ",")
where A1 is the cell reference and "," is the delimiter.

Perfecto!! Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Summing up values in a Single Merge Field	Beowolf	Mail Merge	1	03-07-2014 03:26 PM
Finding Earned Values for Different Baselines in a single project	pratik6312	Project	1	08-22-2013 11:18 AM
How to count multiple values in a single cell, except zero?	iuliandonici	Excel	1	04-13-2011 09:45 PM
Sum & difference between multiple values in a single cell	iuliandonici	Excel	4	04-13-2011 09:27 PM
How can I write the following (whole) formula in a single cell?	Learner7	Excel	1	07-19-2010 10:06 AM