Alan,
I had a similar problem when analysing call out stats, I 'm still on Excel 3 and
used the following technique. I would hope that it is easier in Excel 5, perhaps
using its pivot tables. I don't have a COUNTIF function so you must be on a
later version of Excel.
Here's your data somewhat expanded: Select and Define Name all the cells from
Duty Shift to the last Mike as Shifts of some other meaningful name [even
Database :-) ]
Duty Shift GP
weekday eve. Linda
weekday eve. Carol
weekday night Linda
saturday day Mary
saturday night Mike
weekday eve. Bob
saturday day Mary
saturday day Mike
weekday eve. Bob
saturday day Mike
weekday night Mike
Now set up the following pairs of lines.
Duty Shift GP
weekday eve. Mike 0
The cell with the count has the following formula: =DCOUNT(Shifts,,A14:B15)
where A14:B15 is the range from Duty Shift to Mike. Don't use the Define Name
convention.
Repeat for each shift type. By copying the formula it will adjust automatically
if cell references are used.
Duty Shift GP
weekday night Mike 1
Duty Shift GP
saturday day Mike 2
Duty Shift GP
saturday night Mike 1
If you like you can total all the counts:
Total Mike 4
This of course has to be repeated for each person.
To get a compact printout just copy down each cell value to a block of cells:
Summary for Mike Carol Linda
weekday eve. 0
weekday night 1
saturday day 2
saturday night 1
Total 4
I hope this helps. It would be nice to see a more compact solution for Excel 3.
Regards
Alan
Alan Cooper, Managing Change
E-mail: [log in to unmask], Tel & Fax: +44 (0)1264 737609
S-mail: Change House, Shepherds Rise, Vernham Dean, Andover, Hampshire, SP11
0HD, England
Managing Change means "A proactive approach to change"
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|