Countif with multiple criteria and date ranges

[weaz1991]

Need a bit of help with this. Doing a project for a made up hotel, in essence I have 3 columns, Arrival date, leave date, and rate.

Arrival date and leave date are self explanatory. The rate is either: "EB", "NP" or "WI", and the rate is decided based on when the room was booked, but is not really relevant.

What i need to do is count up how many rooms are booked on a given day, and of those booked on that day how many are "EB", for example.

I have a solution, but it is impractical. I have used the conditional sum wizard, and a list of hidden numbers to solve the problem, but i am trying to change it to a better formulae.

A big problem is that I am using dates. I have a list of dates 30 long that need finding out and along with three rates it makes 90 formulae, each one with 2 dates in them that need changing by hand, 180 changes that i could really do without.

i was hoping you could help me.

Below is a mock up of the table of data.

Arrival date Leave Date Rate
30/11/2008 01/12/2008 NP
30/11/2008 01/12/2008 EB
29/11/2008 01/12/2008 NP
29/11/2008 30/11/2008 WI
29/11/2008 30/11/2008 NP
28/11/2008 01/12/2008 NP
28/11/2008 01/12/2008 EB
28/11/2008 01/12/2008 EB
28/11/2008 01/12/2008 EB
28/11/2008 30/11/2008 EB
28/11/2008 30/11/2008 EB
28/11/2008 29/11/2008 WI
28/11/2008 29/11/2008 WI
27/11/2008 02/12/2008 EB
27/11/2008 01/12/2008 NP
27/11/2008 01/12/2008 EB
27/11/2008 30/11/2008 EB
26/11/2008 02/12/2008 EB
26/11/2008 01/12/2008 EB
26/11/2008 30/11/2008 NP
26/11/2008 29/11/2008 EB

The actual formulae will go on a different sheet, in a table

Date EB Rooms NP Rooms WI Rooms
30/11/2008
29/11/2008
28/11/2008
27/11/2008
26/11/2008
25/11/2008
24/11/2008

I really need help with it and would be glad of any.

Thanks,

Weazel

Also, it would be even better if a formulae could be copied and pasted down the columns, going along is no problem as there are only 3 columns.

 

[texasalynn]

try this

Macros for MrExcel.xls
	A	B	C	D
1	Arrivaldate	LeaveDate	Rate
2	11/30/2008	12/01/2008	NP
3	11/30/2008	12/01/2008	EB
4	11/29/2008	12/01/2008	NP
5	11/29/2008	11/30/2008	WI
6	11/29/2008	11/30/2008	NP
7	11/28/2008	12/01/2008	NP
8	11/28/2008	12/01/2008	EB
9	11/28/2008	12/01/2008	EB
10	11/28/2008	12/01/2008	EB
11	11/28/2008	11/30/2008	EB
12	11/28/2008	11/30/2008	EB
13	11/28/2008	11/29/2008	WI
14	11/28/2008	11/29/2008	WI
15	11/27/2008	12/02/2008	EB
16	11/27/2008	12/01/2008	NP
17	11/27/2008	12/01/2008	EB
18	11/27/2008	11/30/2008	EB
19	11/26/2008	12/02/2008	EB
20	11/26/2008	12/01/2008	EB
21	11/26/2008	11/30/2008	NP
22	11/26/2008	11/29/2008	EB
Sheet4

Macros for MrExcel.xls
	A	B	C	D
1	Date	EBRooms	NPRooms	WIRooms
2	11/30/2008	1	1	0
3	11/29/2008	0	2	1
4	11/28/2008	5	1	2
5	11/27/2008	3	1	0
6	11/26/2008	3	1	0
7	11/25/2008	0	0	0
8	11/24/2008	0	0	0
Sheet6

 

[jbeaucaire]

This works for me on your data, with data on Sheet1, results on Sheet2:

=SUMPRODUCT((Sheet1!$A$2:$A$22=Sheet2!$A2)*(Sheet1!$C$2:$C$22=Sheet2!B$1))

 

[weaz1991]

I appreciate what you have done, But the formulae you have given only show the results were the arrival date and the rate are the same. The things is that if the date you are looking for, say the 28/12/08, is between the arrival date and leave date that needs to be checked against the rate, your formulae only checks the arrival date against the rate.

Bad on my part, didn't explain it all that well, would you be so kind as to give it another go?

 

[jbeaucaire]

I appreciate what you have done, But the formulae you have given only show the results were the arrival date and the rate are the same. The things is that if the date you are looking for, say the 28/12/08, is between the arrival date and leave date that needs to be checked against the rate, your formulae only checks the arrival date against the rate.

Bad on my part, didn't explain it all that well, would you be so kind as to give it another go?

Indeed, I missed. My apologies. try this instead:

=SUMPRODUCT((Sheet1!$A$2:$A$22<=Sheet2!$A2)*(Sheet1!$B$2:$B$22>=Sheet2!$A2)*(Sheet1!$C$2:$C$22=B$1))

 

[texasalynn]

Indeed, I missed. My apologies. try this instead:

=SUMPRODUCT((Sheet1!$A$2:$A$22<=Sheet2!$A2)*(Sheet1!$B$2:$B$22>=Sheet2!$A2)*(Sheet1!$C$2:$C$22=B$1))

I thought the same thing but it gives 0's

 

[weaz1991]

got it now, from the original formulae i changed the = to <=. I then copied the formulae changed the criteria to check to the leave date, I then took the arrive date formulae, from the leave date formulae to give the number of rooms booked on a given date, with the given rate.

=(SUMPRODUCT(--(Arrival dates<=Check date),--(Rates=Check rate)))-(SUMPRODUCT(--(Leave Dates<=Check Date),--(Rate=Check Rate)))

Thanks for the help, would not have done it with out you!

Weazel