Countif with dates

[mleeangel]

Trying to get a count using hired before date and termed after date.
am trying to use the cell the date is in. I'm attaching a sampl
spreadsheet with the formula that I'm using. Thanks for your help an
showing me how to fix what I am messing up.
Thanks
Michell

+-------------------------------------------------------------------
|Filename: countifs.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=637
+-------------------------------------------------------------------

 

[Spencer101]

Trying to get a count using hired before date and termed after date.
am trying to use the cell the date is in. I'm attaching a sampl
spreadsheet with the formula that I'm using. Thanks for your help an
showing me how to fix what I am messing up
Thank
Michell

Try changing the formula in D2 to

=COUNTIFS(SHEET1!B:B,B2,SHEET1!H:H,\"<\"&$D$1,SHEET1!I:I,\">\"&$D$1
Does that fix it for you

You may actually wish to use the below version as it will also captur
any that fall on the date in D

=COUNTIFS(SHEET1!B:B,B2,SHEET1!H:H,\"<=\"&$D$1,SHEET1!I:I,\">=\"&$D$1
And this one will allow you to drag the formula to populate the othe
cells

*=COUNTIFS(Sheet1!$B:$B,$B2,Sheet1!$H:$H,"<="&D$1,Sheet1!$I:$I,">="&D$1)

+-------------------------------------------------------------------
+-------------------------------------------------------------------