Monthly totals from list with date ranges

[adamlucasmiller]

Hi,

I have a list of bookings from accounts and would like to select a month, year, and account name (B2, B3, B4) and see the total revenue from that account for the selected month/year in D2. What is the best way to go about this?

Sample spreadsheet link: Test Spreadsheet

 

[Bosquedeguate]

Does this give you the totals you need?
I based the SUMIFS total on the calculated end of month date. So, the "income" for the first month of the start to end date will show the income for that month, but it will be zero for the last month.
for example: for your provided data
3/2022 totals will include $1998 for the first and following months, but
the totals for 3/2023 will exclude $1998 for unit 115 (since it was reported in 3/2022). I assumed you do not want to count the rent twice.
Anyway, test this an see it gives you the totals you need.
The formula includes the case for "All" being selected.

RentTotals.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2	Month	3	Revenue
3	Year	2024	Room Nights
4	Account	Bindel	Units
5	Annual Total	$5,778.00
6
7
8
9
10
11
12
13	Sentral Agent	Account	Unit Number	Product	Date Booked	Start Date of Booking	End Date of Booking	MTM After Lease End?	Average Daily Rate	Monthly Rent
14		Bindel	115	LIVE UF		3/21/2022	3/20/2023		$66.60	$1,998.00
15		Bindel	231	LIVE UF		12/30/2022	4/29/2024		$63.00	$1,890.00
16		Bindel	102	LIVE UF		3/21/2022	7/16/2024		$66.60	$1,998.00
17		Bindel	520	LIVE UF		12/30/2022	4/29/2024		$63.00	$1,890.00
18	AM	BCD Travel	218	LIVE UF		7/24/2023	8/24/2023		$88.00	$2,640.00
19	AM	NCH	419	DF		8/3/2023	9/3/2023		$146.67	$4,400.00
20	AM	NCH	217	DF		8/3/2023	9/3/2023		$146.67	$4,400.00
21	AM	NCH	113	DF		8/3/2023	9/3/2023		$146.67	$4,400.00
Sheet1

Cell Formulas
Range	Formula
B5	B5	=IF(B4="All",SUMIFS(J14:J1000,F14:F1000,"<="&EOMONTH(DATE(B3,B2,1),0),G14:G1000,">="&EOMONTH(DATE(B3,B2,1),0)),SUMIFS(J14:J1000,B14:B1000,"="&B4,F14:F1000,"<="&EOMONTH(DATE(B3,B2,1),0),G14:G1000,">="&EOMONTH(DATE(B3,B2,1),0)))

Cells with Data Validation
Cell	Allow	Criteria
B2	List	All,1,2,3,4,5,6,7,8,9,10,11,12
B3	List	All,2021,2022,2023,2024,2025,2026,2027,2028
B4	List	Bindel,BCH Travel,NCH,All