Count If Between Certain Dates and Activities

I have attached my spread sheet for you. What I need to do is count the amount of people who have been on support (per a week) in a 4 weekly period, but only if they have a status of placed or drop-in. The formula I have in there at the moment was taken from an earlier version of the spread sheet, where there was no status, just hours received. The info goes into Row 66 and onwards, titled for "Supporting People Use Only". So, if they have the status of placed or drop-in, and the start date is in week two, I would need it to count that, but obviously not in week 1; the same goes for if they have an end date; if they received service in week 1 and 2, but had an end date in week 2, I would only want them counted for these two weeks. I'm hoping this makes sense, but if not please ask me questions!

Many thanks for your help; this worked perfectly! And thanks for re-arranging my period dates.

Arrays and VBA are two things I really need to look into, as not had much need to use them so far, but now I have more knowledge of Excel, think I am ready to take that step!

Thanks again,
Emma.

You're quite welcome, glad I could be of service! Thank you for the rep. -Lee

Hi again,

I thought your formula had worked, but on closer inspection, it seems to be slightly out I have since added a count of people on support (i.e. not necessarily having received hours of support during the week, but no end date, so still current), and this has shown up the issue. I have attached the amended spread sheet for you, so you can see what I mean. Range C86:G86 shows the amount of people who have received support in that week, but the range above (C86:G86) is using your formula, and is saying the number of people still current, but not necessarily receiving support is one less than those on support, which can't be right; in fact in week 4 there is a difference of 4!.

Any ideas? This is driving me mad!!!!!!!!!!!!!!

Thanks, Emma.

Hi Emma- Sorry, I'm pretty busy, but I'll TRY to look it over this evening. -Lee

Ok, the test for {"placed","drop-in"} wasn't working correctly. Paste this ARRAY FORMULA in D85 and copy across:

=SUMPRODUCT((($B$15:$B$66="placed")+($B$15:$B$66="drop-in"))*($H$15:$H$66<=(Sheet2!A$18+6))*(IF($I$15:$I$66,--($I$15:$I$66>=Sheet2!A$18),1)))

My apologies, I'm sure that was frustrating. I hope this solves your problem. -Lee