Group attendance monitoring (no. of groups attended last week, month, since start)

[aWorkingPig]

Hi all, please help if you can.

I would like..
I've been trying to set up a monitoring tool that keeps track of client attendance at group and 121 sessions for the previous week, month and since arrival.

I have tried..
I have been trying to use the MAX function but have not found a way to work it.

I have..
I have a report I generate each week (similar to below in blue)
and I have another document (tab) that I would like to display the summary of attendance.
Link to simplified data

 

[jdellasala]

I downloaded the file (you could use XL2BB instead), and find NO data that matches the Last Week or Last Month criteria in the second table (Green header). I confirmed this manually using manual filters on the Blue table, as well as some 365 COUNTIFS:

Group attendance data.xlsx
	F	G	H	I	J	K	L
1	Arrival	Client ID	Arrival - 7	Number of Groups attended last week	Arrival - 30	Number of Groups attended last month	Number of Groups attended since arrival
2	01/01/2022	C001	12/25/2021	0	12/02/2021	0	9
3	01/14/2022	C002	01/07/2022	0	12/15/2021	0	12
4	02/06/2022	C003	01/30/2022	0	01/07/2022	0	14
5	08/19/2022	C004	08/12/2022	0	07/20/2022	0	15
6	VOID	C005	#N/A	#N/A	#N/A	#N/A	#N/A
7	VOID	C006	#N/A	#N/A	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR([@Arrival]-7,NA())
I2:I7	I2	=IF(ISERROR([@[Arrival - 7]]),NA(),COUNTIFS(Table1[Client ID],[@[Client ID]],Table1[Event type],"Group",Table1[Date attended],">="&[@[Arrival - 7]],Table1[Date attended],"<="&[@Arrival]))
J2:J7	J2	=IFERROR([@Arrival]-30,NA())
K2:K7	K2	=IF(ISERROR([@[Arrival - 30]]),NA(),COUNTIFS(Table1[Client ID],[@[Client ID]],Table1[Event type],"Group",Table1[Date attended],">="&[@[Arrival - 30]],Table1[Date attended],"<="&[@Arrival]))
L2:L7	L2	=IF(ISERROR([@[Number of Groups attended last month]]),NA(),COUNTIFS(Table1[Client ID],[@[Client ID]],Table1[Event type],"Group"))

 

[Skybluekid]

Hi

The below may help:

In Cell I3

In Cell J3

In Cell K3

 

[aWorkingPig]

Thanks both, really helpful solutions. An I learnt something