Determining patient census at the end of each month and on demand

[joeystraw]

I have a basic table with patient names, admission dates and discharge dates. I need a way to determine the head count of admitted patients at the end of every month (i.e. monthly report) as well as on demand (entering a specific month/year and getting a census). Current census count is simple and straightforward...if patient has an admit date but no discharge date then the count with these 2 criteria. Querying past census counts is what I can't figure out.

Book2.xlsx
	A	B	C	D	E	F	G
3	Bed	Current Patient?	Last Name	First Name	Admit Date	Admitted?	Discharge Date
4	C-133	Y	Boyle	Jacoby	12/24/2018	TRUE	2/2/2020
5	C-129	Y	Rodriguez	Jaime	2/25/2020	TRUE	6/13/2020
6	C-131	Y	Snow	Carsen	4/18/2020	TRUE
7	C-124	Y	Steele	Drake	5/26/2018	TRUE
8	B-113	Y	Cuevas	Royce	1/8/2018	TRUE	9/1/2020
9	B-116	Y	Russell	Coleman	1/8/2019	TRUE	5/15/2020
10	B-112	Y	Richard	Beckett	2/19/2019	TRUE	4/30/2019
11	A-110	Y	Bryan	George	5/10/2018	TRUE	9/25/2019
12	C-132	Y	Sampson	Landyn	7/8/2020	TRUE
13	C-122	Y	Valentine	Trystan	3/20/2018	TRUE	4/23/2019
14	C-128	Y	Neal	Aryan	11/3/2019	TRUE	2/17/2020
15	A-108	Y	Lane	Adriel	10/23/2018	TRUE	10/22/2019
16	A-104	Y	Perkins	CANDICE	2/11/2018	TRUE	1/3/2019
17	A-107	Y	Bowers	KINDRA	11/16/2019	TRUE
18	B-120	Y	Baird	PAIGE	4/7/2018	TRUE
19	C-123	Y	Mora	YEN	1/25/2020	TRUE	11/6/2020
20	B-121	Y	Bird	BRITTNEY	2/18/2018	TRUE	8/7/2019
21	C-127	Y	Malone	JANICE	5/13/2019	TRUE	5/17/2020
22	B-114	Y	Walter	ANTOINETTE	12/31/2017	TRUE	1/11/2018
23	B-117	Y	Mann	MARY	2/18/2018	TRUE	1/1/2020
24	B-111	Y	Wheeler	JUDY	6/29/2017	TRUE
25	B-118	Y	Morgan	SHANNON	4/16/2018	TRUE	8/8/2020
26	A-105	Y	Todd	BRYANA	2/26/2018	TRUE	5/24/2018
27	A-103	Y	Wolfe	VIVIANNA	11/5/2018	TRUE	7/3/2019
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A4:A27	List	=BED
B4:B27	List	=Current_Pt
F4:F27	List	=TRUE_FALSE

 

[Toadstool]

Hi JoeyStraw,

Can you test if this works for you?

JoeyStraw.xlsx
	A	B	C	D	E	F	G	H	I	J	K
3	Bed	Current Patient?	Last Name	First Name	Admit Date	Admitted?	Discharge Date		Month	Year	Head Count
4	C-133	Y	Boyle	Jacoby	12/24/2018	TRUE	2/2/2020		1	2020	11
5	C-129	Y	Rodriguez	Jaime	2/25/2020	TRUE	6/13/2020
6	C-131	Y	Snow	Carsen	4/18/2020	TRUE
7	C-124	Y	Steele	Drake	5/26/2018	TRUE
8	B-113	Y	Cuevas	Royce	1/8/2018	TRUE	9/1/2020
9	B-116	Y	Russell	Coleman	1/8/2019	TRUE	5/15/2020
10	B-112	Y	Richard	Beckett	2/19/2019	TRUE	4/30/2019
11	A-110	Y	Bryan	George	5/10/2018	TRUE	9/25/2019
12	C-132	Y	Sampson	Landyn	7/8/2020	TRUE
13	C-122	Y	Valentine	Trystan	3/20/2018	TRUE	4/23/2019
14	C-128	Y	Neal	Aryan	11/3/2019	TRUE	2/17/2020
15	A-108	Y	Lane	Adriel	10/23/2018	TRUE	10/22/2019
16	A-104	Y	Perkins	CANDICE	2/11/2018	TRUE	1/3/2019
17	A-107	Y	Bowers	KINDRA	11/16/2019	TRUE
18	B-120	Y	Baird	PAIGE	4/7/2018	TRUE
19	C-123	Y	Mora	YEN	1/25/2020	TRUE	11/6/2020
20	B-121	Y	Bird	BRITTNEY	2/18/2018	TRUE	8/7/2019
21	C-127	Y	Malone	JANICE	5/13/2019	TRUE	5/17/2020
22	B-114	Y	Walter	ANTOINETTE	12/31/2017	TRUE	1/11/2018
23	B-117	Y	Mann	MARY	2/18/2018	TRUE	1/1/2020
24	B-111	Y	Wheeler	JUDY	6/29/2017	TRUE
25	B-118	Y	Morgan	SHANNON	4/16/2018	TRUE	8/8/2020
26	A-105	Y	Todd	BRYANA	2/26/2018	TRUE	5/24/2018
27	A-103	Y	Wolfe	VIVIANNA	11/5/2018	TRUE	7/3/2019
Sheet1

Cell Formulas
Range	Formula
K4	K4	=COUNTIFS($E$4:$E$27,"<="&EOMONTH(DATE($J$4,$I$4,1),0),$G$4:$G$27,">="&EOMONTH(DATE($J$4,$I$4,1),0))+COUNTIFS($E$4:$E$27,"<="&EOMONTH(DATE($J$4,$I$4,1),0),$G$4:$G$27,"")

 

[joeystraw]

Works perfectly...thank you for your assistance and quick reply!