countif with dates but more complicated

[odarestephen]

Hi

I am beaten. Time to ask the experts...

Employe Name (A1)	Location (B1)	Start Date (C1)	End Date (D1)	Blank (E1)	Location (F1)	Sep'13 (G1)	Oct'13 (H1)	Nov'13 (I1)	Dec'13 (J1)	Jan'14 (K1)	Feb'14 (L1)
Mike	Australia	5/10/13	14/12/13		Australia
Tim	Australia	1/8/13	14/1/14		Madrid
John	Madrid	1/8/13	22/2/14		Paris
Alan	Paris	1/7/13	14/9/13

<tbody>
</tbody>

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?