Count between dates plus one more criteria

labello27

New Member
Joined
Feb 2, 2009
Messages
26
Hi,

Wonder if you could help me with the following:
I want to count the number of Room Types each day as the example. Thanks for your help

Arrival Grid 2.xlsx
BCDEFGHIJKLMNOPQRST
112-Jan-2223-Jan-22CheckIn DateCheckOut DateRoom Type 2
212-Jan-2213-Jan-2214-Jan-2215-Jan-2216-Jan-2217-Jan-2218-Jan-2219-Jan-2220-Jan-2221-Jan-2222-Jan-2223-Jan-2224-Jan-22 15-Jan-2219-Jan-22TDXG
3OSUG13-Jan-2220-Jan-22KDXG
4O1SG13-Jan-2220-Jan-22KDXG
5KSUG14-Jan-2220-Jan-22KDXG
6KDXG26181820202010614-Jan-2220-Jan-22KDXG
7KWRG14-Jan-2220-Jan-22TDXG
8TDXG12222114-Jan-2220-Jan-22KDXG
9TWSG14-Jan-2220-Jan-22KDXG
10TOTG1111111111115-Jan-2220-Jan-22TDXG
11KOTG15-Jan-2220-Jan-22KDXG
12TVUG15-Jan-2220-Jan-22KDXG
13KEXG15-Jan-2220-Jan-22KDXG
14KFTG15-Jan-2220-Jan-22KDXG
15XKLG15-Jan-2221-Jan-22KDXG
16XOTG15-Jan-2221-Jan-22KDXG
17XBUG17-Jan-2221-Jan-22KDXG
18XLXG17-Jan-2221-Jan-22KDXG
19XLOG15-Jan-2222-Jan-22KDXG
20XFTG15-Jan-2222-Jan-22KDXG
21XEXG15-Jan-2222-Jan-22KDXG
221382121232322117115-Jan-2222-Jan-22KDXG
2315-Jan-2222-Jan-22KDXG
2412-Jan-2223-Jan-22TOTG
2515-Jan-2223-Jan-22KDXG
Sheet1
Cell Formulas
RangeFormula
C1C1=MIN('[Arrival Grid.xlsx]RL Temp w CC '!C2:C25)
D1D1=MAX('[Arrival Grid.xlsx]RL Temp w CC '!D2:D25)
C2C2=C1
D2:P2D2=IF(C2<=$D$1,C2+1,"")
C22:M22C22=SUM(C3:C21)
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
How about
+Fluff 1.xlsm
BCDEFGHIJKLMNOPQRST
112-Jan-2223-Jan-22CheckIn DateCheckOut DateRoom Type 2
212-Jan-2213-Jan-2214-Jan-2215-Jan-2216-Jan-2217-Jan-2218-Jan-2219-Jan-2220-Jan-2221-Jan-2222-Jan-2223-Jan-2224-Jan-2215-Jan-2219-Jan-22TDXG
3OSUG0000000000013-Jan-2220-Jan-22KDXG
4O1SG0000000000013-Jan-2220-Jan-22KDXG
5KSUG0000000000014-Jan-2220-Jan-22KDXG
6KDXG02618182020202010614-Jan-2220-Jan-22KDXG
7KWRG0000000000014-Jan-2220-Jan-22TDXG
8TDXG0013333320014-Jan-2220-Jan-22KDXG
9TWSG0000000000014-Jan-2220-Jan-22KDXG
10TOTG1111111111115-Jan-2220-Jan-22TDXG
11KOTG0000000000015-Jan-2220-Jan-22KDXG
12TVUG0000000000015-Jan-2220-Jan-22KDXG
13KEXG0000000000015-Jan-2220-Jan-22KDXG
14KFTG0000000000015-Jan-2220-Jan-22KDXG
15XKLG0000000000015-Jan-2221-Jan-22KDXG
16XOTG0000000000015-Jan-2221-Jan-22KDXG
17XBUG0000000000017-Jan-2221-Jan-22KDXG
18XLXG0000000000017-Jan-2221-Jan-22KDXG
19XLOG0000000000015-Jan-2222-Jan-22KDXG
20XFTG0000000000015-Jan-2222-Jan-22KDXG
21XEXG0000000000015-Jan-2222-Jan-22KDXG
221382121232322117115-Jan-2222-Jan-22KDXG
2315-Jan-2222-Jan-22KDXG
2412-Jan-2223-Jan-22TOTG
2515-Jan-2223-Jan-22KDXG
Main
Cell Formulas
RangeFormula
C3:M21C3=COUNTIFS($T:$T,$B3,$R:$R,"<="&C$2,$S:$S,">="&C$2)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top