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. ## 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. ## 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>

4. ## Re: Adding alternating fields

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

Thanks

6. ## 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

7. ## 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. ## 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

9. ## 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.

#### Posting Permissions

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