Vlookup & SumIF - Staff Rota summary

[TheOnlyJme]

Hi I'm trying to produce a summary sheet for a staff rota and need to know how best to achieve two things:

1. How many shift types each person is working (i.e. Day, Night, Early, Late)
2. How many shifts of each day (i.e. Saturday and Sunday or Day Off) each person has.

For Q.1 - I think I need to do a vlookup to find the member of staff and then a CountIF per shift type - But have failed so far
For Q.2 - I'm unsure how to combine the WEEKDAY function to count across multiple columns (i.e If date in column is Monday, count/sum if staff members name is Paul)

The rota is laid out as below:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Staff Name[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Snr. Nurse[/TD]
[TD]Paul[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]DO[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]AL[/TD]
[TD]AL[/TD]
[/TR]
[TR]
[TD]Nurse[/TD]
[TD]Jane[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]N[/TD]
[TD]DO[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]






The summary table of number of shift types is laid out as below:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Staff Name[/TD]
[TD]E[/TD]
[TD]L[/TD]
[TD]Day[/TD]
[TD]Night[/TD]
[TD]Leave[/TD]
[/TR]
[TR]
[TD]Snr. Nurse[/TD]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nurse[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






The summary table of shifts per day over the month is laid out as below:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Staff Name[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Snr. Nurse[/TD]
[TD]Paul[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Nurse[/TD]
[TD]Jane[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]






Thanks in advance

 

[bnorton4]

You will want to match the headers in your summary table to the entries in the rota table to simplify it. Here's a formula assuming your rota table is in sheet 1 with "Role" in cell A1 of your workbook. You can drag this across and down. =COUNTIF(INDIRECT("Sheet1!"&MATCH($B2,Sheet1!$B:$B,0)&":"&MATCH($B2,Sheet1!$B:$B,0)),C$1)

For the shifts per day you could use something like this. You can add more of these by simply changing the characters in the quotation marks. This is only counting the occurrences of "L" and "E".
=COUNTIFS(Sheet1!$B:$B,$B7,Sheet1!C:C,"L")+COUNTIFS(Sheet1!$B:$B,$B7,Sheet1!C:C,"E")