COUNTIFS Between 2 Dates (Works with 1 date, but not when there's 2)..

[BenGee]

Here's the formula;
=COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,B8,'Employee Leave Tracker'!$C$4:$C$100,">="&$C$7,'Employee Leave Tracker'!$D$4:$D$100,"<="&$C$7)

Conditions are;
B8 = Name1
C7 = 30/04/2018

'Employee Leave Tracker'!B4 = Name1
'Employee Leave Tracker'!C4 = 30/04/2018
'Employee Leave Tracker'!D4 = 02/05/2018

With the above in mind, I'm convinced my formula should work. But it returns 0.

However, if I change 'Employee Leave Tracker'!D4 to the 30/04/2018, I then get 1.

I'm not really sure why. Does anyone have an idea?

Thanks in advance for your help. This seems so simple so I don't understand why I can't see it.

 

[Ron Coderre]

You want the criteria to be:
- Name list matches test name
- start list is less than or equal to test date
- end list is greater than or equal to test date
I'm pretty sure you just need to change the formula to this:

=COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,B8,'Employee Leave  Tracker'!$C$4:$C$100,"<="&$C$7,'Employee Leave  Tracker'!$D$4:$D$100,">="&$C$7)

Is that something you can work with?