Countifs issue!

[mrsdeapsleap]

I have attached my spreadsheet, so you will know what I am on about! This spreadsheet has been a very trying project for me; I think this is the 3rd time I have had to come to the forum!!! My boss has a lot to answer for LOL!

I am trying to get a total of people supported in a set period. In cell E67 I want the number of people supported in that week (dates are shown in D67, but the actual dates come from Sheet 2, A17 to B17). I need to take into account that service users have start and end dates (D13:D21 and E13:E21). So if someone doesn't start support till week 2 (shown in cell D13:D21), I don't want them included in the count for week 1; but also, if they end in week 1 (shown in cell E13:E21), I don't want them counted in week 2, etc., etc.

Hope this makes sense, if not please let me know!

Many thanks in advance!

[pjwhitfield]

None of your example data fits with the example though? ie none of the Start/End Dates fall within the ranges youre looking at

[loginjmor]

Hi -

I think I get what you mean.

I used SUMPRODUCT to check if the start date is less than or equal to the date range you are searching and if the end date is greater than or equal to the date range. The formula ignores anyone who doesn't have an end date (Cells E13 through E21). So I put some fake data into some of those cells to verify they work. The formula in Cell C67 looks like:

=SUMPRODUCT(--(($D$13:$D$21)<=Sheet2!A17)*--(('4 weekly service user schedule'!$E$13:$E$21)>=Sheet2!B17))

I have attached a copy of your spreadsheet with this formula in place. See if this works for you.

Please note I changed the format on your dates to US for my convenience. You should be able to copy the formulas into your spreadsheet and they should work the same.