Best way to count?

Hello all,

I'm posting this here for some direction on the best way to do something. I'm trying to figure out for 2 years, the number of people we had working at a station at a given time. I will have data exported as:

Name, Shift Start Date, Shift Start Time, Shift Stop Date, Shift Stop Time, Station, Total Hours.

We have usually 4 people at a station, and they usually work 0600-0600 shifts, or 0600-1700 and 1700-0600 if some part timers are working. Sometimes, there might be trades, or overtime, or classes, etc. that would cause one person to be on maybe 0600 - 1200, then a different guy 1200-1800, and someone else 1800-0600.

The information I ultimately need out is a count of how many day shifts (0600-1700) and night shifts (1700-0600) had 3, 4, or 5 people working.

Any ideas on the best way to have this work? I'm stuck even on the route to go - COUNTIF statements, or Pivot tables or something totally different? I was asked once for this information for 1 station, and it wasn't that hard to just count each day, but now I've been asked to do it for all 3 stations, and for 2 years, and it sounds like it will be a reoccurring request, so it would be much easier if I can have Excel just give me the information. I have attached a sample of the data I will get out so that it might help. In the sample, Oct 4 and Oct 6 are "normal" days - 4 guys working four 24 hour shifts. Oct 5th is one of the odd days though. I'd be happy enough with saying how many people are working at 6am and how many people are working at 5pm each day, if that is an easier way to do it.

If someone could just give me at least an idea of where to head?

Thanks!

Does this help?

I manually changed the orange filled cells for testing purposes...

=SUMPRODUCT(--(M3+N3>=$D$3:$D$18+E$3:E$18),--(M3+N3<$F$3:$F$18+G$3:G$18))

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.