Nesting SUMPRODUCT in to an IF statement

Hi All

I would really appreciate your help with what I believe will be a nested SUMPRODUCT within an IF statement (or it may involve VLOOKUP, HLOOKUP, any other formula!).

Working with the NHS, I am trying to devise a simple Demand and Capacity tool where a service can monitor the number of activities (appointments) available each day across multiple sites. This is standardised each day but will be affected by people taking leave, being off sick etc.

Using SUMPRODUCT I have been able to calculate the number of a given activity lost should a person be off (i.e. Person A is off on Monday so 4 episodes of activity 1 will be unavailable), but now I need to add additional criteria by identifying the Site that the activities will be lost from.

I have attached a sample of the spreadsheet I am using. The first sheet represents the activities each person is scheduled to undertake each day and the location the activities take place at (these do not change), and the second sheet identifies who is off on any given day.

The third sheet includes the table I am unable to complete. This table needs to identify the total number of activities (split by activity no.) against each site for the date identified at the top of the sheet. To do this it needs to look at the Master Shift Grid and identify the number of activities per site for that day (which is the easy part) and then remove any activities for people who are on leave.

For example, looking at the Master Shift Grid of the attached, on a Monday Person A does 4 episodes of Activity 1 at Site 1, and Person B also does 4 episodes of Activity 1 at Site 1 (highlighted in red). I can get the table on the third sheet (Lost slots per site) to calculate that on a Monday (01/07/13 in this example) there are 8 episodes of Activity 1 at Site 1 available, but what I can’t get it to calculate is if Person A is on leave on 01/07/13 there needs to be only 4 episodes of Activity 1 at Site 1 available for that day.

Is anyone able to help me with this please?

Apologies for the long post but its not a simple thing to explain!

Thanks in advance
00skins

In the Master Shift Grid sheet, add a helper column.. so in T7 (you could hide the formula in the table border by setting font and background to blue) enter formula:

=IF($B7="","",IF(INDEX('MASTER Leave Grid'!$F$11:$I$31,MATCH($B$3,'MASTER Leave Grid'!$B$11:$B$31,0),MATCH($B7,'MASTER Leave Grid'!$F$10:$I$10,0))<>"","X",""))

copied down. This identifies the person(s) who are not available on the current date.

Then formula in C7 of Lost Slots sheet is:

=SUMPRODUCT(('MASTER Shift Grid'!$E$7:$E$28=$B7)*('MASTER Shift Grid'!$F$6:$S$6=C$6)*('MASTER Shift Grid'!$T$7:$T$28<>"X"),'MASTER Shift Grid'!$F$7:$S$28)

copied down and across the table.

Hi NBVC

Thank you - this is really helpful and works much better than aything else I have come up with. The only problem is it is picking up all activity across the week rather than just the activity for that one day (relating to the date at the top of the sheet). I presume the additional calculation would need to be added to the formula on the Lost Slots sheet. I have tried a few things but cant quite get it to work...?

Thanks again

e.g.

=SUMPRODUCT(('MASTER Shift Grid'!$E$7:$E$28=$B7)*('MASTER Shift Grid'!$F$6:$S$6=C$6)*('MASTER Shift Grid'!$D$7:$D$28=$B$4)*('MASTER Shift Grid'!$T$7:$T$28<>"X"),'MASTER Shift Grid'!$F$7:$S$28)

Absolutely perfect! Thank you NBVC, you have been incredibly helpful.