Count between 2 lookup values

[littlefish71]

Hi there,

I have a table which is a shift roster. I would like to enter 2 dates as lookup values and count the number of days the person is "OFF" between those dates. The dates need to be changed so cell reference is not an option. Can someone help?


1/01/2017 OFF
2/01/2017 OFF
3/01/2017 OFF
4/01/2017 D1
5/01/2017 D2
6/01/2017 N1
7/01/2017 N2
8/01/2017 OFF
9/01/2017 OFF
10/01/2017 OFF
11/01/2017 OFF
12/01/2017 D1
13/01/2017 D2
14/01/2017 N1
15/01/2017 N2
16/01/2017 OFF
17/01/2017 OFF
18/01/2017 OFF
19/01/2017 OFF
20/01/2017 D1
21/01/2017 D2
22/01/2017 N1
23/01/2017 N2
24/01/2017 OFF
25/01/2017 OFF
26/01/2017 OFF
27/01/2017 OFF
28/01/2017 D1
29/01/2017 D2
30/01/2017 N1
31/01/2017 N2
1/02/2017 OFF
2/02/2017 OFF

 

[Marcelo Branco]

Try COUNTIFS

Something like

=COUNTIFS(A$2:A$200,">="&D2,A$2:A$200,"<="&E2,B$2:B$200,"OFF")

where
D2 = start date
E2 = end date

Hope this helps

M.

 

[littlefish71]

Thank you Marcelo, I was getting caught up in using vlookup rather than the range. cheers