LinkBack URL
About LinkBacks
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
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
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
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
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
Last edited by john dalton; 09-17-2013 at 05:16 AM. Reason: Spell Check
I have taken into account just one criteria.
Does it mean that those two categories will not be counted at all?
yeah it will be failed to attend, unsuitable and awaiting inductio
cheer
jd
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))
cant get it work for some reason
jd
Dates%20as%20per%20client(1).xlsx
What's the reason?
That works great thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks