Calculate revenue from list of dates

[trent172]

Hi,

Using Microsoft Excel for Mac 2021 v16

I am looking to build a simple calculator that can review a list of bookings and calculate the revenue between two dates I select.

Here is the source table:

And here is the calculator I want to calculate total revenue in B14 (from accommodation revenue > column D in source table above) between any two dates I select:

Any formula help you could share would be greatly appreciated.

Thanks

 

[alansidman]

Please reload your data using XL2BB. We cannot manipulate data in a picture.

 

[trent172]

Couldn't get XL2BB to work. Here is the table copy:

Arrival	Departure	Nights	Accomm
12/10/23	25/10/23	14​	$700​
26-10-23	8/11/23	14​	$700​
10/9/23	23-9-23	14​	$500​
24-9-23	7/10/23	14​	$500​
8/10/23	21/10/23	14​	$500​
8/9/23	15/9/23	7​	$400​
16/9/23	22/9/23	7​	$400​
Revenue Calculator
From:	23-9-23
To:	29-9-23
Total revenue:

 

[alansidman]

I would build a parameter query in Power Query. See this link for my solution.

Parameter Query.xlsm | Powered by Box

app.box.com

 

[Dave Patton]

Ensure Column D has actual numbers and not text.

Sumifs.xlsm
	A	B	C	D
1
2	Arrival	Departure	Nights	Accomm
3	8-Sep-23	15-Sep-23	7	400
4	10-Sep-23	23-Sep-23	14	500
5	16-Sep-23	22-Sep-23	7	400
6	24-Sep-23	7-Oct-23	14	500
7	8-Oct-23	21-Oct-23	14	500
8	12-Oct-23	25-Oct-23	14	700
9	26-Oct-23	8-Nov-23	14	700
10
11
12	Revenue Calculator
13	From:	23-Sep-23
14	To:	29-Sep-23
15	Amount	500
7a

Cell Formulas
Range	Formula
B15	B15	=SUMIFS(D3:D9,B3:B9,">="&B13,B3:B9,"<="&B14)

 

[trent172]

Ensure Column D has actual numbers and not text.

Sumifs.xlsm
	A	B	C	D
1
2	Arrival	Departure	Nights	Accomm
3	8-Sep-23	15-Sep-23	7	400
4	10-Sep-23	23-Sep-23	14	500
5	16-Sep-23	22-Sep-23	7	400
6	24-Sep-23	7-Oct-23	14	500
7	8-Oct-23	21-Oct-23	14	500
8	12-Oct-23	25-Oct-23	14	700
9	26-Oct-23	8-Nov-23	14	700
10
11
12	Revenue Calculator
13	From:	23-Sep-23
14	To:	29-Sep-23
15	Amount	500
7a

Cell Formulas
Range	Formula
B15	B15	=SUMIFS(D3:D9,B3:B9,">="&B13,B3:B9,"<="&B14)

Thanks Dave for this.

Can the formula calculate "between" dates?

For example if I want revenue between 8-Sep to 9-Sep, it would need to divide that first booking's revenue in the table above and calculate a total revenue of $114.29