jawilliams29
New Member
- Joined
- Jul 27, 2011
- Messages
- 2
I have a small spreadsheet which records staff hours in two different categories (Tch and RD) over the weeks in a year. Each staff member has a category e.g. TIC3, TIC6, HTB1 or HTB2. I want to be able to add up the totals for each staff cateogry for each work category. E.g. The total hours for TIC3 staff who did Tch hours. The spreadsheet looks like this:
A B C D E F G H I J ....
1 Tch RD Tch RD Tch RD Tch RD
2 John TIC3 2 1 3 1 4 1 2 2
3 Mary HTB1 1 3 1 4 1 2 2 5
4 Joe TIC6 4 1 2 2 5 6 2 1
5 Phil TIC3 2 2 5 6 2 1 4 3
6 Julie HTB1 6 2 1 4 3 5 1 2
....
Tch Hours
TIC3 = (2 + 3 + 4 + 2) + (2 + 5 + 2 + 4)
TIC6 = 4 + 2 + 5 + 2
HTB1 = (1 + 1 + 1 + 2) + (6 + 1 + 3 + 1)
HTB2 = 0
Then the RD hours would be the opposite alternate columns.
I have looked at the SUMPRODUCT with the MOD(ROW) and MOD(COLUMN) options but I cannot seem to find a way for it to look at more than one row or column at a time.
I have always found this site really helpful when I am stuck, but it seems no one else has ever had this problem.
Thank you
A B C D E F G H I J ....
1 Tch RD Tch RD Tch RD Tch RD
2 John TIC3 2 1 3 1 4 1 2 2
3 Mary HTB1 1 3 1 4 1 2 2 5
4 Joe TIC6 4 1 2 2 5 6 2 1
5 Phil TIC3 2 2 5 6 2 1 4 3
6 Julie HTB1 6 2 1 4 3 5 1 2
....
Tch Hours
TIC3 = (2 + 3 + 4 + 2) + (2 + 5 + 2 + 4)
TIC6 = 4 + 2 + 5 + 2
HTB1 = (1 + 1 + 1 + 2) + (6 + 1 + 3 + 1)
HTB2 = 0
Then the RD hours would be the opposite alternate columns.
I have looked at the SUMPRODUCT with the MOD(ROW) and MOD(COLUMN) options but I cannot seem to find a way for it to look at more than one row or column at a time.
I have always found this site really helpful when I am stuck, but it seems no one else has ever had this problem.
Thank you