Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding alternating fields

    I maintain a very long list in excel 2000 by name and within each name I need to track monthly hours worked, weighted hours and points. Than at end of each month I need a total of all hours worked, weighted hours and points. I figure there must be away (probably pretty basic that I am just not seeing) of telling EXCEL to add every fourth field for this value, etc. or whatever?
    Thanks for any help!
    Ken

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    You can add every 4th field from a given range by an array-formula: Hold ctrl/shift before pressing enter on this example:
    =SUM((A5:Z5)*(MOD(COLUMN(A5:Z5),4)=1))
    this would sum A5,E5,I5,...

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    Since I can't see your spreadsheet, and you didn't really describe how it is layed out, it is kind of tough to visualize what you are trying to do. There is no way that I know of to automatically add every fourth cell. You could use the SUM() function, but you will have to specify each cell to be added in the parameter list like this:

    <pre>=SUM(B1,F1,J1,N1)
    </pre>


    In addition, Excel functions have a limit (15 if I remember correctly) on the maximum number of parameters that can be passed. Therefore, you will probably have to do something like this:

    <pre>=SUM(B1,F1,J1,N1)+SUM(R1,V1,Z1)
    </pre>


    You could also write your own VBA function to sum every fourth cell. The following function will sum every fourth cell in the range passed to it starting at the first cell in the range:

    <pre>Public Function SumEveryFourth(oCellsToSum As Range) As Double
    Dim lCellNum As Long
    Dim dSum As Double
    Dim oCurCell As Range
    lCellNum = 0
    dSum = 0
    For Each oCurCell In oCellsToSum
    If lCellNum Mod 4 = 0 Then
    dSum = dSum + oCurCell.Value
    End If
    lCellNum = lCellNum + 1
    Next oCurCell
    SumEveryFourth = dSum
    End Function
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    I stand corrected. There is a way to do it. Nice solution.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    Thanks

  6. #6
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    Bob:
    Thanks for your response - I got a feeling I am not explaining my situation very well. What I need is a monthly total of c6+c10+c14 and so on; c7+c11+c15 and so on; c8+c12+c16 and so on. I will try to attach a small version of my work sheet.
    Thank you
    Ken
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    Legare:
    Thanks for your response. I have responded to Bob with some additional data as I don't think I was explaining my situation very well.
    Thank You
    Ken

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    Ken, The SUMIF function is worth a look, and I attach a copy of your workbook using it. For example to sum the amounts for TOT WGT use =SUMIF($B6:$B44,"TOT WGT",D644). To use this formula for the Name I had to insert a new column, to distinguish those rows.

    Hope it works for you

    Andrew C
    Attached Files Attached Files

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding alternating fields

    That is exactly what the Function I sent and the formula that Bob Umlas do. I am attaching your workbook modified to use my function to calculate the totals for Column C and using Bob's array formula to calculate the totals for column D.
    Attached Files Attached Files
    Legare Coleman

Posting Permissions

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