Counting multiple Criteria within a table

[Manexcel]

I am using Windows 10

I am using Office 2019

I am using UK based date formats

Use Case

I have a dataset which is used for tracking purposes on a specified dates. The number of specified dates in a year are variable, in number, and will be in future years.

Columns I15:AG34 are manually updated depending on category via data validation

I have created a table for my dataset

I am trying to count the number of occurrences for each person against the options that each cell allows e.g. PAC, NPC,ACI,NAC,CNH, NCI and NRA - in total and over set time periods

I have included in the attachment examples of pre and post worksheets

Challenges

a) If I add a new column AH onwards the formulas do not get updated to reflect the additional column so is excluded from the countifs formula

b) I am unclear how I can count the number of occurrences over specified time periods e.g. 1 and 3 and 5 year period and roll that forward.

What I would like to happen

1) As the number of columns will continue to be added (column AH onwards) I would like the formulas in columns (B15:H34) to update as well so that I don’t have to manually update (been using countifs and today() without success)

2) To create some metrics to; count the number of occurrences for each variable e.g. PAC, NPC, ACI, NAC, CNH, NCI and NRA for each member name and over;

• All time periods
• 1 year - From todays() date back one year from 2021 to 2020
• 3 years - From todays() date back 3 years from 2021 back to 2018
• 5 years - From todays() date back 5 years from 2021 back to 2015

Obviously, as we go into 2022 the 1 year count will automatically be from 2022 to 2021, the 3 year will be 2022 to 2019 and the 5 year 2022 to 2017 and so on as the years roll forward.

The metrics is going to take much of the screen real estate which is of concern to me. Ideally, I would like to locate metrics against each member name in a separate worksheet but have concerns getting the member names / metrics out of sync. Any thoughts of how I can overcome?

Thank you for your time and support.

Simple attendance records v0.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
14	Member Names	(PAC)	(NPC)	(ACI)	(NAC)	(CNH)	(NCI)	(NRA)	05/02/2015	09/04/2015	01/10/2015	04/02/2016	14/04/2016	06/10/2016	02/02/2017	13/04/2017	05/10/2017	01/02/2018	12/04/2018	04/10/2018	07/02/2019	11/04/2019	03/10/2019	06/02/2020	09/04/2020	01/10/2020	04/02/2021	08/04/2021	01/05/2021	02/05/2021	03/05/2021	20/05/2021
15	Person 1	4	12	1	1	4	0	3	NPC	NPC	NPC	PAC	NPC	PAC	NPC	NPC	NPC	NPC	NPC	NPC	PAC	NPC	PAC	NPC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
16	Person 2	2	1	1	1	4	0	3														PAC	NPC	PAC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
17	Person 3	16	0	2	0	4	0	3	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	PAC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
18	Person 4	12	4	2	0	4	0	3	NPC	PAC	PAC	PAC	PAC	PAC	NPC	NPC	PAC	PAC	NPC	PAC	PAC	PAC	PAC	PAC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
19	Person 5	11	3	2	0	4	0	3			PAC	PAC	NPC	PAC	PAC	PAC	PAC	PAC	NPC	NPC	PAC	PAC	PAC	PAC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
20	Person 6	2	11	2	0	4	0	3				PAC	NPC	NPC	NPC	PAC	NPC	NPC	NPC	NPC	NPC	NPC	NPC	NPC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
21	Person 50	5	3	2	0	4	0	3									NPC	NPC	PAC	PAC	PAC	PAC	PAC	NPC	CNH	CNH	CNH	CNH	NRA	NRA	NRA	ACI
22	Person 100	1	0	0	0	0	0	0	PAC
23	Person 101	1	0	0	0	0	0	0	PAC
24		0	0	0	0	0	0	0
25		0	0	0	0	0	0	0
26		0	0	0	0	0	0	0
27		0	0	0	0	0	0	0
28		0	0	0	0	0	0	0
29		0	0	0	0	0	0	0
30		0	0	0	0	0	0	0
31		0	0	0	0	0	0	0
32		0	0	0	0	0	0	0
33		0	0	0	0	0	0	0
34		0	0	0	0	0	0	0
Attendance

Cell Formulas
Range	Formula
B15:B34	B15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"pac")
C15:C34	C15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"npc")
D15:D34	D15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"aci")
E15:E34	E15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"nac")
F15:F34	F15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"cnh")
G15:G34	G15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"nci")
H15:H34	H15	=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"nra")

Cells with Data Validation
Cell	Allow	Criteria
AC15:AE17	List	=INDIRECT("member_attendance_category")
AF15:AF21	List	=INDIRECT("member_attendance_category")
I16:AB17	List	=INDIRECT("member_attendance_category")
I18:AE34	List	=INDIRECT("member_attendance_category")

Outcome

Member Names

All Years (PAC)

Last Year (PAC)

Last 3 years (PAC)

Last 5 years (PAC)

All Years (NPC)

Last Year (NPC)

Last 3 years (NPC)

Last 5 years (NPC)

All Years (ACI)

All Years (NAC)

All Years (CNH)

All Years (NCI)

All Years (NRA)

Last Year (NRA)

Last 3 years (NRA)

Last 5 years (NRA)

05/02/2015

09/04/2015

01/10/2015

04/02/2016

14/04/2016

06/10/2016

02/02/2017

13/04/2017

05/10/2017

01/02/2018

12/04/2018

04/10/2018

07/02/2019

11/04/2019

03/10/2019

06/02/2020

09/04/2020

01/10/2020

04/02/2021

08/04/2021

01/05/2021

02/05/2021

03/05/2021

20/05/2021

23/05/2021

Person 1

5

1

3

4

8

1

5

12

3

3

3

3

NPC

NPC

NPC

PAC

NPC

PAC

NPC

NPC

NPC

NPC

NPC

NPC

PAC

NPC

PAC

NPC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

PAC

Person 2

2

1

2

2

1

0

1

1

3

3

3

3

PAC

NPC

PAC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

NAC

Person 3

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

PAC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

ACI

Person 4

NPC

PAC

PAC

PAC

PAC

PAC

NPC

NPC

PAC

PAC

NPC

PAC

PAC

PAC

PAC

PAC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

ACI

Person 5

PAC

PAC

NPC

PAC

PAC

PAC

PAC

PAC

NPC

NPC

PAC

PAC

PAC

PAC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

ACI

Person 6

PAC

NPC

NPC

NPC

PAC

NPC

NPC

NPC

NPC

NPC

NPC

NPC

NPC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

ACI

Person 50

NPC

NPC

PAC

PAC

PAC

PAC

PAC

NPC

CNH

CNH

CNH

CNH

NRA

NRA

NRA

ACI

ACI

Person 100

PAC

Person 101

PAC

 

[jasonb75]

Assuming that your source table will look something like this without the metrics.

Manexcel.xlsx
	A	B	C	D	E	F	G	H
1	Member Names	05/02/2015	09/04/2015	01/10/2015	04/02/2016	14/04/2016	06/10/2016	02/02/2017
2	Person 1	NPC	NPC	NPC	PAC	NPC	PAC	NPC
3	Person 2
4	Person 3	PAC	PAC	PAC	PAC	PAC	PAC	PAC
5	Person 4	NPC	PAC	PAC	PAC	PAC	PAC	NPC
6	Person 5			PAC	PAC	NPC	PAC	PAC
7	Person 6				PAC	NPC	NPC	NPC
8	Person 50
9	Person 100	PAC
10	Person 101	PAC
Sheet1

This method should return the required results. Note that the small helper table at the bottom can be moved / hidden away as needed.

Manexcel.xlsx
	A	B	C	D	E	F	G	H	I
1		All	1 Year	3 Years	5 Years	All	1 Year	3 Years	5 Years
2	Member Names	PAC	PAC	PAC	PAC	NPC	NPC	NPC	NPC
3	Person 1	4	0	2	3	12	0	4	9
4	Person 2	2	0	2	2	1	0	1	1
5	Person 3	16	0	6	12	0	0	0	0
6	Person 4	12	0	5	9	4	0	1	3
7	Person 5	11	0	4	9	3	0	2	3
8	Person 6	2	0	0	1	11	0	6	11
9	Person 50	5	0	5	5	3	0	1	3
10	Person 100	1	0	0	0	0	0	0	0
11	Person 101	1	0	0	0	0	0	0	0
12
13		All	1 year	3 Years	5 Years
14	Base date	N/A	24/05/2020	24/05/2018	24/05/2016
15	Start Date	N/A	09/04/2020	12/04/2018	14/04/2016
16	Start Index	2	18	12	6
Sheet2

Cell Formulas
Range	Formula
A3:A11	A3	=INDEX(Member_Attendance_Records[Member Names],ROWS(A$3:A3))
B3:I11	B3	=COUNTIF(INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),INDEX($B$16:$E$16,MATCH(B$1,$B$13:$E$13,0))):INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),MATCH("zzz",Member_Attendance_Records[#Headers])),B$2)
C14	C14	=EDATE(TODAY(),-12)
D14	D14	=EDATE(TODAY(),-36)
E14	E14	=EDATE(TODAY(),-60)
C15:E15	C15	=LOOKUP(C14,Member_Attendance_Records[#Headers]/1)
C16:E16	C16	=MATCH(TEXT(C15,"dd/mm/yyyy"),Member_Attendance_Records[#Headers],0)

 

[Manexcel]

Thank you for replying so quickly. It is very much appreciated. I have started to do my testing and so far all looks ok.

Just one query at present. Is it possible for the helper table to always start on 01/01/nnnn either one year or 3 years or 5 years ago? The reason for asking is depending on "todays date" the records for earlier in that time period don't appear to being counted up as it will only look back exactly for 12 or 36 or 60 months. I trust this makes sense? Thanks again.

 

[jasonb75]

See if this works.

Manexcel.xlsx
	A	B	C	D	E	F	G	H	I
1		All	1 Year	3 Years	5 Years	All	1 Year	3 Years	5 Years
2	Member Names	PAC	PAC	PAC	PAC	NPC	NPC	NPC	NPC
3	Person 1	4	0	2	4	12	1	5	9
4	Person 2	2	1	2	2	1	0	1	1
5	Person 3	16	1	7	13	0	0	0	0
6	Person 4	12	1	6	10	4	0	1	3
7	Person 5	11	1	5	10	3	0	2	3
8	Person 6	2	0	0	2	11	1	7	11
9	Person 50	5	0	5	5	3	1	2	3
10	Person 100	1	0	0	0	0	0	0	0
11	Person 101	1	0	0	0	0	0	0	0
12
13		All	1 year	3 Years	5 Years
14	Base date	N/A	01/01/2020	01/01/2018	01/01/2016
15	Start Date	05/02/2015	06/02/2020	01/02/2018	04/02/2016
16	Start Index	2	17	11	5
Sheet2

Cell Formulas
Range	Formula
A3:A11	A3	=INDEX(Member_Attendance_Records[Member Names],ROWS(A$3:A3))
B3:I11	B3	=COUNTIF(INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),INDEX($B$16:$E$16,MATCH(B$1,$B$13:$E$13,0))):INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),MATCH("zzz",Member_Attendance_Records[#Headers])),B$2)
C14	C14	=DATE(YEAR(TODAY())-1,1,1)
D14	D14	=DATE(YEAR(TODAY())-3,1,1)
E14	E14	=DATE(YEAR(TODAY())-5,1,1)
B15:E15	C15	=INDEX(Member_Attendance_Records[#Headers],C16)
C16:E16	C16	=MATCH(TRUE,(Member_Attendance_Records[#Headers]/1)>C14,0)

 

[Manexcel]

Thank you for the updated formulas. However, I seem to be having an issue with the formulas with the 'start index' range (C16:e16). If I enter the numbers, from your formulas, in these cells manually all appears to be ok but if i use the formulas as requested, I get #N/A.

I have attached my results. I am sure I am missing something here but I just cannot see it. Your assistance would be appreciated. Many thanks.

Simple attendance records v0.3.xlsx
	A	B	C	D	E	F	G	H	I	J
1		All	1 Year	3 Years	5 Years	All	1 Year	3 Years	5 Years
2	Member Names	PAC	PAC	PAC	PAC	NPC	NPC	NPC	NPC
3	Person 1	4	#N/A	2	4	12	#N/A	5	9
4	Person 2	2	#N/A	2	2	1	#N/A	1	1
5	Person 3	16	#N/A	7	13	0	#N/A	0	0
6	Person 4	12	#N/A	6	10	4	#N/A	1	3
7	Person 5	11	#N/A	5	10	3	#N/A	2	3
8	Person 6	2	#N/A	0	2	11	#N/A	7	11
9	Person 50	5	#N/A	5	5	3	#N/A	2	3
10	Person 100	1	#N/A	0	0	0	#N/A	0	0
11	Person 101	1	#N/A	0	0	0	#N/A	0	0
12
13		All	1 year	3 Years	5 Years
14	Base date	N/A	01/01/2020	01/01/2018	01/01/2016
15	Start Date	05/02/2015	#N/A	01/02/2018	04/02/2016
16	Start Index	2	#N/A	11	5
Attendance

Cell Formulas
Range	Formula
A3:A11	A3	=INDEX(Member_Attendance_Records[Member Names],ROWS(A$22:A22))
B3:I11	B3	=COUNTIF(INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),INDEX($B$16:$E$16,MATCH(B$1,$B$13:$E$13,0))):INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),MATCH("zzz",Member_Attendance_Records[#Headers])),B$2)
D14	D14	=DATE(YEAR(TODAY())-3,1,1)
E14	E14	=DATE(YEAR(TODAY())-5,1,1)
B15:E15	D15	=INDEX(Member_Attendance_Records[#Headers],D16)
C14	C14	=DATE(YEAR(TODAY())-1,1,1)
C16	C16	=MATCH(TRUE,(Member_Attendance_Records[#Headers]/1)>C14,0)

 

[jasonb75]

The problem is starting in C16. An error there would suggest that the dates in the table headers are either entered in a way that excel can not convert to a proper date, or that there is no date on or after 1/1/21

The other errors are all being caused by that one.

Also it looks as if D16 and E16 don't have the formulas so they will not update as necessary.

 

[Manexcel]

The problem is starting in C16. An error there would suggest that the dates in the table headers are either entered in a way that excel can not convert to a proper date, or that there is no date on or after 1/1/21

The other errors are all being caused by that one.

Also it looks as if D16 and E16 don't have the formulas so they will not update as necessary.

In doing some additional testing and investigation...

I have checked the dates, in my source data - the header row via the formula =CELL("format",A1) to AA and the result for each cell returns a value of D1 which I am given to understand is saying it is a date field. I have also re formatted the header row as date fields. But I appear to get the same results.

I have also checked to ensure that I have dates on or after 01/01/2021 which I do. Some I am now confused...

However, having performed some additional research I have come across that the "match function" might need to be an array formula. Would this cause the problem because if I make the formulas in cells C16:E16 array formulas all seems to be ok. Below is my latest with all the formulas but with C16:E16 being made an array formula.

I thank you for your time and patience.

Simple attendance records v0.5.xlsx
	A	B	C	D	E	F	G	H	I
1		All	1 Year	3 Years	5 Years	All	1 Year	3 Years	5 Years
2	Member Names	PAC	PAC	PAC	PAC	NPC	NPC	NPC	NPC
3	Person 1	4	0	2	4	12	1	5	9
4	Person 2	2	1	2	2	1	0	1	1
5	Person 3	16	1	7	13	0	0	0	0
6	Person 4	12	1	6	10	4	0	1	3
7	Person 5	11	1	5	10	3	0	2	3
8	Person 6	2	0	0	2	11	1	7	11
9	Person 50	5	0	5	5	3	1	2	3
10	Person 100	1	1	1	1	1	1	1	1
11	Person 101	0	0	0	0	0	0	0	0
12
13		All	1 year	3 Years	5 Years
14	Base date	N/A	01/01/2020	01/01/2018	01/01/2016
15	Start Date	05/02/2015	06/02/2020	01/02/2018	04/02/2016
16	Start Index	2	17	11	5
Attendance

Cell Formulas
Range	Formula
A3:A11	A3	=INDEX(Member_Attendance_Records[Member Names],ROWS(A$22:A22))
B3:I11	B3	=COUNTIF(INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),INDEX($B$16:$E$16,MATCH(B$1,$B$13:$E$13,0))):INDEX(Member_Attendance_Records[#Data],ROWS(B$3:B3),MATCH("zzz",Member_Attendance_Records[#Headers])),B$2)
C14	C14	=DATE(YEAR(TODAY())-1,1,1)
D14	D14	=DATE(YEAR(TODAY())-3,1,1)
E14	E14	=DATE(YEAR(TODAY())-5,1,1)
B15:E15	C15	=INDEX(Member_Attendance_Records[#Headers],C16)
C16:E16	C16	=MATCH(TRUE,(Member_Attendance_Records[#Headers]/1)>C14,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

Would this cause the problem because if I make the formulas in cells C16:E16 array formulas all seems to be ok.

Yes, that would be the problem.

It had slipped my mind as I'm using office 365, which doesn't need array confirmation.

I have also re formatted the header row as date fields.

With a structured table (that is what you are using) the headers are always text regardless of the cell format. The CELL function looks at the format of the cell, not the content of the cell.

 

[Manexcel]

Making the cells an array formula has resolved the issue and all appears to be ok. Thank you for your support, time and patience. It is very much appreciated.

This can now be closed.

 

[Manexcel]

Yes, that would be the problem.

It had slipped my mind as I'm using office 365, which doesn't need array confirmation.

With a structured table (that is what you are using) the headers are always text regardless of the cell format. The CELL function looks at the format of the cell, not the content of the cell.

Making the cells an array formula has resolved the issue and all appears to be ok. Thank you for your support, time and patience. It is very much appreciated.

This can now be closed.