countif with dates but more complicated

[odarestephen]

Hi

I am beaten. Time to ask the experts...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employe Name (A1)
[/TD]
[TD]Location (B1)
[/TD]
[TD]Start Date (C1)
[/TD]
[TD]End Date (D1)
[/TD]
[TD]Blank (E1)
[/TD]
[TD]Location (F1)
[/TD]
[TD]Sep'13 (G1)
[/TD]
[TD]Oct'13 (H1)
[/TD]
[TD]Nov'13 (I1)
[/TD]
[TD]Dec'13 (J1)
[/TD]
[TD]Jan'14 (K1)
[/TD]
[TD]Feb'14 (L1)
[/TD]
[/TR]
[TR]
[TD]Mike
[/TD]
[TD]Australia
[/TD]
[TD]5/10/13
[/TD]
[TD]14/12/13
[/TD]
[TD][/TD]
[TD]Australia
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]Australia
[/TD]
[TD]1/8/13
[/TD]
[TD]14/1/14
[/TD]
[TD][/TD]
[TD]Madrid
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]Madrid
[/TD]
[TD]1/8/13
[/TD]
[TD]22/2/14
[/TD]
[TD][/TD]
[TD]Paris
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alan
[/TD]
[TD]Paris
[/TD]
[TD]1/7/13
[/TD]
[TD]14/9/13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now What I want is a formula to count the number of staff in each location on a given month:


I have been trying to use (in cell G2) =COUNTIFS($B:$B,$F2,$C:$C,"<="&G$1,$D:$D,">="&EOMONTH(G1,0))+COUNTIFS($B:$B,$F2,$C:$C,">="&G$1,$D:$D,"<="&EOMONTH(G1,0))but I can get it to pick up the last month where an employee stays for a few months without counting other people twice.

I then tried =SUMPRODUCT(--($B$2:$B$26=F2),--(YEAR($C$2:$C$26)=YEAR(G$1)),--(MONTH($C$2:$C$26)<=MONTH((G$1))),--(MONTH($D$2:$D$26)>=MONTH((G$1)))) but this fails when I go from one year in to the next.

Losing the marbles here a bit and I am sure there is a relatively simple answer.

Thanks to all responders.

Stephen

 

[MikeWx]

I simplified this problem by putting 9/1/2013 in Cell G1, formatted as mmm'yy, to look like your format. I did the same for cells H1 through L1. My formula in Cell G2 is

=SUMPRODUCT(--($B$2:$B$5=$F2),--($D$2:$D$5>=G$1),--($C$2:$C$5<=EOMONTH(G$1,0)))

Will this work for you?