Sum of every 6th column and recurring
 

Hi, I have a spreadsheet divided into 52 weeks across the columns. Each week
has 5 individual columns. Rows go to about 100, one for each employee.
Starting with week 1, Employee 1, I insert values (timesheet hours) into each
of the 5 individual columns. As the weeks go by I would like to have a
running total of the values in column 1 for each week, column 2 for each week
and so on. Can I set up a formula to get the SUM of every 6th column
recurring 52 times without me having to do it manually i.e. click every 6th
column 52 times in my formula? Thanks in advance! Pierian Spring

Sum of every 6th column and recurring
 

=SUMPRODUCT(--(MOD(COLUMN(A1:AZ1),6)=2),A1:AZ1)

This sums B, H, etc. Modify =2 if you want different columns

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Pierian Spring" <Pierian wrote in message
...
Hi, I have a spreadsheet divided into 52 weeks across the columns. Each
week
has 5 individual columns. Rows go to about 100, one for each employee.
Starting with week 1, Employee 1, I insert values (timesheet hours) into
each
of the 5 individual columns. As the weeks go by I would like to have a
running total of the values in column 1 for each week, column 2 for each
week
and so on. Can I set up a formula to get the SUM of every 6th column
recurring 52 times without me having to do it manually i.e. click every
6th
column 52 times in my formula? Thanks in advance! Pierian Spring

Sum of every 6th column and recurring
 

Bob, many thanks - works like dream. I spent many hours yesterday trying to
crack without success. You've made life a little easier for a guy in Co.
Clare in Ireland. Cheers!

"Bob Phillips" wrote:

=SUMPRODUCT(--(MOD(COLUMN(A1:AZ1),6)=2),A1:AZ1)

This sums B, H, etc. Modify =2 if you want different columns

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Pierian Spring" <Pierian wrote in message
...
Hi, I have a spreadsheet divided into 52 weeks across the columns. Each
week
has 5 individual columns. Rows go to about 100, one for each employee.
Starting with week 1, Employee 1, I insert values (timesheet hours) into
each
of the 5 individual columns. As the weeks go by I would like to have a
running total of the values in column 1 for each week, column 2 for each
week
and so on. Can I set up a formula to get the SUM of every 6th column
recurring 52 times without me having to do it manually i.e. click every
6th
column 52 times in my formula? Thanks in advance! Pierian Spring

Sum of every 6th column and recurring
 

You might also want to experiment with a worksheet structured with three
columns:
employee,
date,
hours

and then entering all of the hours down the sheet

Using that structure you can then make use of a pivot table to generate
reports by week, month, or other period.


"Pierian Spring" wrote:

Bob, many thanks - works like dream. I spent many hours yesterday trying to
crack without success. You've made life a little easier for a guy in Co.
Clare in Ireland. Cheers!

"Bob Phillips" wrote:

=SUMPRODUCT(--(MOD(COLUMN(A1:AZ1),6)=2),A1:AZ1)

This sums B, H, etc. Modify =2 if you want different columns

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Pierian Spring" <Pierian wrote in message
...
Hi, I have a spreadsheet divided into 52 weeks across the columns. Each
week
has 5 individual columns. Rows go to about 100, one for each employee.
Starting with week 1, Employee 1, I insert values (timesheet hours) into
each
of the 5 individual columns. As the weeks go by I would like to have a
running total of the values in column 1 for each week, column 2 for each
week
and so on. Can I set up a formula to get the SUM of every 6th column
recurring 52 times without me having to do it manually i.e. click every
6th
column 52 times in my formula? Thanks in advance! Pierian Spring