Count instances between 2 dates

[VHaig]

Hi

I need to record staff sickness in a spreadsheet. One of the things I need to record is the number of times someone has been off in a 6 month period.

In cell G1 I have =today()

In cell G2 I have =G1-183 (approx. 6 month period in days)

In cell G5 I would like a formula which looks at column A and returns the number of times that name has been mentioned within the dates in cells G1 and G2, whilst also using the dates in column B or C (Haven't decided yet).

Any geniuses out there who can help?

Viv

 

[NARAYANK991]

Hi ,

Will this work ?

=COUNTIFS($A$2:$A$12,F5,$B$2:$B$12,"<=" & $G$1,$C$2:$C$12,">=" & $G$2)

Narayan

 

[Deepak]

Put in G5 & Drag Down as well.

=SUMPRODUCT(($B$2:$B$12>=$G$2)*($C$2:$C$12<=$G$1)*($A$2:$A$12=F5))

 

[VHaig]

That appears to be exactly what I'm looking for. Many thanks.