Quote:
Originally Posted by mleeangel
Trying to get a count using hired before date and termed after date. I am trying to use the cell the date is in. I'm attaching a sample spreadsheet with the formula that I'm using. Thanks for your help and showing me how to fix what I am messing up.
Thanks
Michelle
|
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 capture any that fall on the date in D1
=COUNTIFS(Sheet1!B:B,B2,Sheet1!H:H,"<="&$D$1,Sheet 1!I:I,"="&$D$1)
And this one will allow you to drag the formula to populate the other cells:
=COUNTIFS(Sheet1!$B:$B,$B2,Sheet1!$H:$H,"<="&D$1,S heet1!$I:$I,"="&D$1)