Count number of times a date is inbewteen 2 date ranges

[AllyGee]

Hi can anybody help me out with me excel problem. I have a list of training courses and need to count how many courses I have on each day and the number of students to make sure that I (a) dont have more courses than trainers and (b) dont have too many people out of the office at any one time. my spreadsheet is set up like this--
Column A -course name
column B- start date of course
Column C - end date of course
Column D - number of students on course

I dont know if this makes a difference but the courses are listed as they are booked and are not always in date order.

Example

Course A, 01/09/11, 03/09/11, 10
Course B, 02/09/11, 04/09/11, 5
Course C, 10/11/11, 10/11/11, 6
Course D, 01/09/11, 02/09/11, 7

Thanks

 

[nancymk]

=SUMIFS(G7:G10,E7:E10,"<="&F4,F7:F10,">="&F4)

using your date - g7:g10 is the number of students on the course, e7:e10 is the start date, f7:f10 is the end date and f4 is the date you are testing for

This will only work in excel 2007, because sumifs isn't in 2003. If you have 2003 you will have to use this formula, but you need to hit control shift and enter after you type in the formula, not just enter.

=SUM(IF(E7:E10<=F4,IF(F7:F10>=F4,G7:G10)))

 

[AllyGee]

Thanks I have 2007 at home and it works great (2003 at work so will give that a go tomorrow). If I change the formula to a countif in a separate column would it tell me how many courses are on that day so I can have the number of courses running as well as the number of people out of the office?

 

[nancymk]

you're welcome!

excel 2007

=countIFS(E7:E10,"<="&F4,F7:F10,">="&F4)

excel 2003

=count(IF(E7:E10<=F4,IF(F7:F10>=F4,1)))

for both formulas you do not need to reference the student count col.

 

[AllyGee]

Thanks again, that's just the job ~ unless my boss changes her mind and wants something different!