Hi,
I have a table detailing staff duties in 10 minute blocks. I use one
sheet for each day of the week, and have included a summary worksheet
which uses a countif function to total the amount of duties performing
a certain task in a given 10 minute block.

this (extract) is the 10 minute data for Monday.

No. 06:00 06:10 06:20 06:30 06:40 06:50
1 PA BM BM BM BM BM
2 BM BM BM BM BM BM
3 MHE MHE MHE MHE MHE MHE
4 MHE MHE MHE MHE MHE MHE
5 PM PM PM PM PM PM
6 PM PM PM PM PM PM
7 MHE MHE MHE MHE MHE MHE
8 MHE MHE MHE MHE MHE MHE


This (extract) is the summary count for MONDAY
06:00 06:10 06:20 06:30
PA 1 0 0 0
PM 42 42 42 42
MHE 10 10 10 10
XD 0 0 0 0
BM 1 2 2 2
GH 2 2 2 2
AT 1 1 1 1


My question is - I want to reduce the seven daily worksheets to one
sheet and add seven columns to determine if a duty occurs on a given
day. See below. How would I then produce a summary worksheet to
total the tasks in 10 minute blocks providing the task occurs on a
Monday, Tuesday etc? I thought of sumproduct or a countif with two
arguments? It works so well with seven seperate worksheets, but for
maintainence it would be better to use one worksheet. I think a macro
which loops through every cell in the range (30,000 plus cells), would
take too long as it would be updated regularly. Any thoughts?

Duty M Tu W 06:00 06:10
1 Y Y Y PA BM
2 Y Y BM BM
3 Y Y Y MHE MHE
4 Y Y Y MHE MHE
5 Y Y Y PM PM
6 Y PM PM
7 Y Y Y MHE MHE
8 Y Y Y MHE MHE