summing comma separated values in a range

[bthoron]

I have a range of cells filled with comma separated values e.g.

8,2,0,0,1,0,0
3,0,7,1,0,0,2
5,0,3,0,2,0,2

I'd like to be able to total the values:

16,2,10,1,3,0,4

Right now I use a function that adds the 1st argument of each
individual cell (I have to identify each one) and the then a second
function for the next argument and so on... (too much typing of ab123
etc. I'd really like to simplify the work and be able to have one
function work for any range of cells.

The reason for the comma separated values was an attempt to make the
data easier to read (fewer columns).


thanks

 

[Patrick Molloy]

here's a UDF, worked on your sample...a string.."16,2,10,1,3,0,4"

Option Explicit
Public Function ColumnsSum(Source As Range) As String
Dim Result As String
Dim aResult() As Variant
Dim arr() As Long
Dim index As Long
Dim rowindex As Long
Dim ThisRow As Range
Dim splitter As Variant
Dim maxdepth As Long
Dim maxwidth As Long

maxdepth = Source.Rows.Count
For Each ThisRow In Source.Rows
splitter = Split(ThisRow.Value, ",")
rowindex = rowindex + 1
maxwidth = UBound(splitter, 1)
ReDim Preserve arr(1 To maxdepth, 0 To maxwidth)
For index = 0 To maxwidth
arr(rowindex, index) = splitter(index)

Next
Next
ReDim aResult(0 To maxwidth)
For rowindex = 1 To maxdepth
For index = 0 To maxwidth
aResult(index) = aResult(index) + arr(rowindex, index)
Next
Next
Result = Join(aResult, ",")
ColumnsSum = Result
End Function

NOTE if you wnat the output as an array,

remove
Result = Join(aResult, ",")
change
ColumnsSum = Result
to ColumnsSum = aResult

then change the function return type to Variant

 

[bthoron]

Thank you this worked great!

 

[Bob Phillips]

Here is a formula solution

=SUMPRODUCT(--MID($A$1:$A$3,FIND("~",SUBSTITUTE(","&$A$1:$A$3&",",",","~",RO
W(A1))),FIND("~",SUBSTITUTE(","&$A$1:$A$3&",",",","~",ROW(A2)))-FIND("~",SUB
STITUTE(","&$A$1:$A$3&",",",","~",ROW(A1)))-1))

This will get the first digits, copy down a row to get the second, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bthoron]

Bob- this didn't work out very well for me, I think I got rid of the
hyphens, but all I get is #value.
The other thing I note about the formula method is that it limits the
number of csv to the number in the formula. The code above could
handle any number of csv (provided the entire range had the same)

I'm curious about --MID and Substitute how are these used typically?

Thanks

 

[Bob Phillips]

You shouldn't get rid of the hyphens, they are intrinsic to the solution.

The number of CSV is dependant on the range size, it would be simple to
define a named range, and use that name in the formula.

I don't know about typically, but here SUBSTITUTE is used to replace an
instance of the comma with a ~ for uniqueness which FIND will locate,
thereby getting the comma's position. This is passed to the MID function to
extract the value between two commas. SUMPRODUCT Then sums across the range
that location.

The -- is used for coercing Booleans to a number. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for details.

I don't know why it is not working for you. I can assure you that it does
work. I created test data as presented by you, and got the results you gave,
albeit in separate cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bthoron]

I'm quite certain it's a personal problem on my part- I'll try again.
Thanks for the additional information.
B

 

[Bob Phillips]

Just try to explain what it does that you don't want or vice versa/

--

HTH

RP
(remove nothere from the email address if mailing direct)