+ Reply to Thread
Results 1 to 8 of 8

summing comma separated values in a range

  1. #1
    bthoron
    Guest

    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


  2. #2
    Patrick Molloy
    Guest

    Re: summing comma separated values in a range

    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" <bthoron@gmail.com> wrote in message
    news:1113848479.834966.87910@f14g2000cwb.googlegroups.com...
    >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
    >




  3. #3
    bthoron
    Guest

    Re: summing comma separated values in a range

    Thank you this worked great!


  4. #4
    Bob Phillips
    Guest

    Re: summing comma separated values in a range

    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" <bthoron@gmail.com> wrote in message
    news:1113848479.834966.87910@f14g2000cwb.googlegroups.com...
    > 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
    >




  5. #5
    bthoron
    Guest

    Re: summing comma separated values in a range

    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


  6. #6
    Bob Phillips
    Guest

    Re: summing comma separated values in a range

    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" <bthoron@gmail.com> wrote in message
    news:1113969592.620538.184480@l41g2000cwc.googlegroups.com...
    > 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
    >




  7. #7
    bthoron
    Guest

    Re: summing comma separated values in a range

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


  8. #8
    Bob Phillips
    Guest

    Re: summing comma separated values in a range

    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" <bob.phillips@notheretiscali.co.uk> wrote in message
    news:ed1CeqXRFHA.648@TK2MSFTNGP14.phx.gbl...
    > 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" <bthoron@gmail.com> wrote in message
    > news:1113969592.620538.184480@l41g2000cwc.googlegroups.com...
    > > 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
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1