summing comma separated values in a range

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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

Thank you this worked great!

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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)


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>