Using SUMPRODUCT and MAX together

[JakeMann]

Hiya,

I'm working on a specific problem with one of our documents.

Attached is an example of the work.

By day there 2 shifts with various jobs running and a maximum number of staff per shift. My problem is that the End Date of some of the jobs fall into a future date.

As an example, on the attached,

Job B starts on the afternoon shift of 21st and finishes on 22nd on the morning shift. Job C starts on 22nd in the morning and finishes in the afternoon, so the maximum number of staff i need for the morning shift is 1.

Job D starts on 22nd in the afternoon, so the maximum number of staff I need in the afternoon is 2.

On the test I am using the below in N2

=SUMPRODUCT(MAX(IF(B$2:B$5=J4,)*(E$2:E$5=J4)*(C$2:C$5=N$1)*(F$2:F$5=N$1),H$2:H$5))

But I don't think it's right.

Previously I have used =MAX(IF(E9:E9="dddddd",G9="Morning",IF(H9=1,I9))) in N2 but this doesn't take into account if a job continues to run on another shift on another day.

A bit complicated, but can anyone help or advise?

Thanks