Calculate Occupancy using Arrival and Last Night

[bastacks]

I'm having trouble when calculating the nightly hotel occupancy using the arrival date and last night stayed.

Column A: Calendar dates (12/31/16. 1/1/17, etc)
Column B: Blank, no data
Column C: Arrival
Column D: Last night

The formula I was given is below, but the results are always 0 (zero). What am I doing wrong?

=COUNTIF($C$3:$C$9999,"<="&$A3)-COUNTIF($D$3:$D$9999,"<="&$A3)

 

[StephenCrump]

Welcome to the Forum!

Is this what you're looking for?

B14: =COUNTIFS(C$2:C$11,"<="&A14,D$2:D$11,">="&A14)

Excel 2010
	A	B	C	D
1			Arrive	Last night
2			10 Jul 2017	12 Jul 2017
3			10 Jul 2017	13 Jul 2017
4			11 Jul 2017	11 Jul 2017
5			11 Jul 2017	12 Jul 2017
6			11 Jul 2017	15 Jul 2017
7			12 Jul 2017	12 Jul 2017
8			12 Jul 2017	13 Jul 2017
9			12 Jul 2017	15 Jul 2017
10			13 Jul 2017	13 Jul 2017
11			13 Jul 2017	16 Jul 2017
12
13	Date to check	Occupancy
14	12 Jul 2017	7
15	13 Jul 2017	6
16	14 Jul 2017	3
Sheet1

 

[bastacks]

Thanks, but I'm still getting a "0" result.
Formula in B3=
=COUNTIFS(C$2:C$50,"<="&A3,D$2:D$50,">="&A3)

A	B	C	D	E
Date		Arrivals	Last Night	Departure
12/31/2016	0	11/30/2016	12/31/2016	1/1/2017
1/1/2017	0	12/1/2016	12/31/2016	1/1/2017
1/2/2017	0	12/1/2016	12/31/2016	1/1/2017
1/3/2017	0	12/1/2016	12/31/2016	1/1/2017
1/4/2017	0	12/1/2016	12/31/2016	1/1/2017
1/5/2017	0	12/1/2016	12/31/2016	1/1/2017
1/6/2017	0	12/1/2016	12/31/2016	1/1/2017
1/7/2017	0	12/1/2016	12/31/2016	1/1/2017
1/8/2017	0	12/1/2016	12/31/2016	1/1/2017
1/9/2017	0	12/1/2016	12/31/2016	1/1/2017
1/10/2017	0	12/1/2016	12/31/2016	1/1/2017
1/11/2017	0	12/1/2016	12/31/2016	1/1/2017
1/12/2017	0	12/1/2016	12/31/2016	1/1/2017
1/13/2017	0	12/1/2016	12/31/2016	1/1/2017
1/14/2017	0	12/1/2016	12/31/2016	1/1/2017
1/15/2017	0	12/1/2016	12/31/2016	1/1/2017
1/16/2017	0	12/1/2016	12/31/2016	1/1/2017
1/17/2017	0	12/1/2016	12/31/2016	1/1/2017
1/18/2017	0	12/22/2016	12/31/2016	1/1/2017
1/19/2017	0	12/25/2016	12/31/2016	1/1/2017
1/20/2017	0	12/26/2016	1/1/2017	1/2/2017
1/21/2017	0	12/26/2016	1/1/2017	1/2/2017
1/22/2017	0	12/26/2016	1/1/2017	1/2/2017
1/23/2017	0	12/28/2016	12/31/2016	1/1/2017
1/24/2017	0	12/28/2016	12/31/2016	1/1/2017
1/25/2017	0	12/28/2016	12/31/2016	1/1/2017
1/26/2017	0	12/29/2016	12/31/2016	1/1/2017
1/27/2017	0	12/29/2016	12/31/2016	1/1/2017
1/28/2017	0	12/29/2016	1/1/2017	1/2/2017
1/29/2017	0	12/29/2016	1/31/2017	2/1/2017
1/30/2017	0	12/30/2016	12/31/2016	1/1/2017
1/31/2017	0	12/30/2016	12/31/2016	1/1/2017

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[bastacks]

I'm getting a 0 result

B2: =COUNTIFS(C$2:C$50,"<="&A2,D$2:D$50,">="&A2)

Date	Occupied Units	Arrivals	Last Night	Departure
12/31/2016	0	11/30/2016	12/31/2016	1/1/2017
1/1/2017	0	12/1/2016	12/31/2016	1/1/2017
1/2/2017	0	12/1/2016	12/31/2016	1/1/2017
1/3/2017	0	12/1/2016	12/31/2016	1/1/2017
1/4/2017	0	12/1/2016	12/31/2016	1/1/2017
1/5/2017	0	12/1/2016	12/31/2016	1/1/2017
1/6/2017	0	12/1/2016	12/31/2016	1/1/2017
1/7/2017	0	12/1/2016	12/31/2016	1/1/2017
1/8/2017	0	12/1/2016	12/31/2016	1/1/2017
1/9/2017	0	12/1/2016	12/31/2016	1/1/2017
1/10/2017	0	12/1/2016	12/31/2016	1/1/2017
1/11/2017	0	12/1/2016	12/31/2016	1/1/2017
1/12/2017	0	12/1/2016	12/31/2016	1/1/2017
1/13/2017	0	12/1/2016	12/31/2016	1/1/2017
1/14/2017	0	12/1/2016	12/31/2016	1/1/2017
1/15/2017	0	12/1/2016	12/31/2016	1/1/2017
1/16/2017	0	12/1/2016	12/31/2016	1/1/2017
1/17/2017	0	12/1/2016	12/31/2016	1/1/2017
1/18/2017	0	12/22/2016	12/31/2016	1/1/2017
1/19/2017	0	12/25/2016	12/31/2016	1/1/2017
1/20/2017	0	12/26/2016	1/1/2017	1/2/2017
1/21/2017	0	12/26/2016	1/1/2017	1/2/2017
1/22/2017	0	12/26/2016	1/1/2017	1/2/2017
1/23/2017	0	12/28/2016	12/31/2016	1/1/2017
1/24/2017	0	12/28/2016	12/31/2016	1/1/2017
1/25/2017	0	12/28/2016	12/31/2016	1/1/2017
1/26/2017	0	12/29/2016	12/31/2016	1/1/2017
1/27/2017	0	12/29/2016	12/31/2016	1/1/2017
1/28/2017	0	12/29/2016	1/1/2017	1/2/2017
1/29/2017	0	12/29/2016	1/31/2017	2/1/2017
1/30/2017	0	12/30/2016	12/31/2016	1/1/2017
1/31/2017	0	12/30/2016	12/31/2016	1/1/2017
2/1/2017	0	12/30/2016	12/31/2016	1/1/2017
2/2/2017	0	12/30/2016	12/31/2016	1/1/2017
2/3/2017	0	12/30/2016	1/1/2017	1/2/2017
2/4/2017	0	12/30/2016	1/1/2017	1/2/2017
2/5/2017	0	12/31/2016	1/2/2017	1/3/2017
2/6/2017	0	12/31/2016	1/3/2017	1/4/2017

<tbody>
</tbody>