List clients in a chart by date on course

[john dalton]

I am trying to log which clients are active on a course each week by the dates of when they started and ended the course.

Please see attached Sheet for details.

Thanks in advance

JD

[RobertMika]

D2 down:
=COUNTIFS(Data!$C$2:$C$152,"<="&B2,Data!$D$2:$D$152,">="&B2,Data!$D$2:$D$152,"<>"&E1)
where E1 =
Failed to Attend

[john dalton]

The formula works to a certain degree, however where does to lookup value for the "Failed to Attend","Unsuitable for Course" come into the forumla as I have tried a dummy run and it doesn't seem to take the criteria into consideration.

Cheers

JD

[RobertMika]

I have taken into account just one criteria.
Does it mean that those two categories will not be counted at all?

[john dalton]

yeah it will be failed to attend, unsuitable and awaiting inductio

cheer

jd

[RobertMika]

Try:
=SUMPRODUCT(--((Data!$B$2:$B$152<>"failed to attend")+(Data!$B$2:$B$152<>"awaiting induction")+(Data!$B$2:$B$152<>"unsuitable for course")=3),--(Data!$C$2:$C$152<=B2),--(Data!$D$2:$D$152>=B2))

[john dalton]

cant get it work for some reason

jd

[RobertMika]

Dates%20as%20per%20client(1).xlsx
What's the reason?

[john dalton]

That works great thank you