Results 1 to 9 of 9
Thread: Adding alternating fields

20010620, 17:27 #1
 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

20010620, 18:19 #2
 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 arrayformula: Hold ctrl/shift before pressing enter on this example:
=SUM((A5:Z5)*(MOD(COLUMN(A5:Z5),4)=1))
this would sum A5,E5,I5,...

20010620, 18:29 #3
 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

20010620, 18:36 #4
 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

20010620, 18:43 #5
 Join Date
 Jun 2001
 Posts
 91
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding alternating fields
Thanks

20010620, 20:05 #6
 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

20010620, 20:07 #7
 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

20010620, 20:54 #8
 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

20010620, 22:09 #9
 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.
Legare Coleman