EXCEL-Sum total surgerie duration of surgeries in the weekend and before or after working hours (with overlap)

Question

First I had to determine per specialty the total percentage of surgeries that started outside the regular working hours or in weekends.

The final line became:

=SUMPRODUCT(((Table1[Start surgery]>TIME(17;0;0))*(WEEKDAY(Table1[Surgery date];2)<=5))+((Table1[Start surgery]<TIME(9;0;0))*(WEEKDAY(Table1[Surgery date];2)<=5))+(WEEKDAY(Table1[Surgery date];2)>=6))

Now I want to sum the total surgery time of these surgerys (outside working hours and in the weekend) in the G column (surgery duration).

Help would be very appreciated!

Flag the items that matched your first criteria and then use that to calculate SUMIF on column G values. — Sam, Jun 06 '16 at 12:50

score 0 · Accepted Answer · answered Jun 06 '16 at 14:04

Since you already have the SumProduct returning 1's and 0's for the correct slots, you just need to add another criterion of the surgery duration:

=SUMPRODUCT((((Table1[Start surgery]>TIME(17;0;0))*(WEEKDAY(Table1[Surgery date];2)<=5))+((Table1[Start surgery]<TIME(9;0;0))*(WEEKDAY(Table1[Surgery date];2)<=5))+(WEEKDAY(Table1[Surgery date];2)>=6))*(Table1[Surgery duration]))

EXCEL-Sum total surgerie duration of surgeries in the weekend and before or after working hours (with overlap)

1 Answers1