Counting multiple Criteria within a table

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
14Member Names(PAC)(NPC)(ACI)(NAC)(CNH)(NCI)(NRA)05/02/201509/04/201501/10/201504/02/201614/04/201606/10/201602/02/201713/04/201705/10/201701/02/201812/04/201804/10/201807/02/201911/04/201903/10/201906/02/202009/04/202001/10/202004/02/202108/04/202101/05/202102/05/202103/05/202120/05/2021
15Person 141211403NPCNPCNPCPACNPCPACNPCNPCNPCNPCNPCNPCPACNPCPACNPCCNHCNHCNHCNHNRANRANRAACI
16Person 22111403PACNPCPACCNHCNHCNHCNHNRANRANRAACI
17Person 316020403PACPACPACPACPACPACPACPACPACPACPACPACPACPACPACPACCNHCNHCNHCNHNRANRANRAACI
18Person 412420403NPCPACPACPACPACPACNPCNPCPACPACNPCPACPACPACPACPACCNHCNHCNHCNHNRANRANRAACI
19Person 511320403PACPACNPCPACPACPACPACPACNPCNPCPACPACPACPACCNHCNHCNHCNHNRANRANRAACI
20Person 621120403PACNPCNPCNPCPACNPCNPCNPCNPCNPCNPCNPCNPCCNHCNHCNHCNHNRANRANRAACI
21Person 505320403NPCNPCPACPACPACPACPACNPCCNHCNHCNHCNHNRANRANRAACI
22Person 1001000000PAC
23Person 1011000000PAC
240000000
250000000
260000000
270000000
280000000
290000000
300000000
310000000
320000000
330000000
340000000
Attendance
Cell Formulas
RangeFormula
B15:B34B15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"pac")
C15:C34C15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"npc")
D15:D34D15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"aci")
E15:E34E15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"nac")
F15:F34F15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"cnh")
G15:G34G15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"nci")
H15:H34H15=COUNTIFS(Member_Attendance_Records[@[05/02/2015]:[23/05/2021]],"nra")
Cells with Data Validation
CellAllowCriteria
AC15:AE17List=INDIRECT("member_attendance_category")
AF15:AF21List=INDIRECT("member_attendance_category")
I16:AB17List=INDIRECT("member_attendance_category")
I18:AE34List=INDIRECT("member_attendance_category")


Outcome

Member NamesAll 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/201509/04/201501/10/201504/02/201614/04/201606/10/201602/02/201713/04/201705/10/201701/02/201812/04/201804/10/201807/02/201911/04/201903/10/201906/02/202009/04/202001/10/202004/02/202108/04/202101/05/202102/05/202103/05/202120/05/202123/05/2021
Person 15134815123333NPCNPCNPCPACNPCPACNPCNPCNPCNPCNPCNPCPACNPCPACNPCCNHCNHCNHCNHNRANRANRAACIPAC
Person 2212210113333PACNPCPACCNHCNHCNHCNHNRANRANRAACINAC
Person 3PACPACPACPACPACPACPACPACPACPACPACPACPACPACPACPACCNHCNHCNHCNHNRANRANRAACIACI
Person 4NPCPACPACPACPACPACNPCNPCPACPACNPCPACPACPACPACPACCNHCNHCNHCNHNRANRANRAACIACI
Person 5PACPACNPCPACPACPACPACPACNPCNPCPACPACPACPACCNHCNHCNHCNHNRANRANRAACIACI
Person 6PACNPCNPCNPCPACNPCNPCNPCNPCNPCNPCNPCNPCCNHCNHCNHCNHNRANRANRAACIACI
Person 50NPCNPCPACPACPACPACPACNPCCNHCNHCNHCNHNRANRANRAACIACI
Person 100PAC
Person 101PAC
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
Assuming that your source table will look something like this without the metrics.
Manexcel.xlsx
ABCDEFGH
1Member Names05/02/201509/04/201501/10/201504/02/201614/04/201606/10/201602/02/2017
2Person 1NPCNPCNPCPACNPCPACNPC
3Person 2
4Person 3PACPACPACPACPACPACPAC
5Person 4NPCPACPACPACPACPACNPC
6Person 5PACPACNPCPACPAC
7Person 6PACNPCNPCNPC
8Person 50
9Person 100PAC
10Person 101PAC
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
ABCDEFGHI
1All1 Year3 Years5 YearsAll1 Year3 Years5 Years
2Member NamesPACPACPACPACNPCNPCNPCNPC
3Person 1402312049
4Person 220221011
5Person 31606120000
6Person 4120594013
7Person 5110493023
8Person 62001110611
9Person 5050553013
10Person 10010000000
11Person 10110000000
12
13All1 year3 Years5 Years
14Base dateN/A24/05/202024/05/201824/05/2016
15Start DateN/A09/04/202012/04/201814/04/2016
16Start Index218126
Sheet2
Cell Formulas
RangeFormula
A3:A11A3=INDEX(Member_Attendance_Records[Member Names],ROWS(A$3:A3))
B3:I11B3=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)
C14C14=EDATE(TODAY(),-12)
D14D14=EDATE(TODAY(),-36)
E14E14=EDATE(TODAY(),-60)
C15:E15C15=LOOKUP(C14,Member_Attendance_Records[#Headers]/1)
C16:E16C16=MATCH(TEXT(C15,"dd/mm/yyyy"),Member_Attendance_Records[#Headers],0)
 

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
See if this works.
Manexcel.xlsx
ABCDEFGHI
1All1 Year3 Years5 YearsAll1 Year3 Years5 Years
2Member NamesPACPACPACPACNPCNPCNPCNPC
3Person 1402412159
4Person 221221011
5Person 31617130000
6Person 41216104013
7Person 51115103023
8Person 62002111711
9Person 5050553123
10Person 10010000000
11Person 10110000000
12
13All1 year3 Years5 Years
14Base dateN/A01/01/202001/01/201801/01/2016
15Start Date05/02/201506/02/202001/02/201804/02/2016
16Start Index217115
Sheet2
Cell Formulas
RangeFormula
A3:A11A3=INDEX(Member_Attendance_Records[Member Names],ROWS(A$3:A3))
B3:I11B3=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)
C14C14=DATE(YEAR(TODAY())-1,1,1)
D14D14=DATE(YEAR(TODAY())-3,1,1)
E14E14=DATE(YEAR(TODAY())-5,1,1)
B15:E15C15=INDEX(Member_Attendance_Records[#Headers],C16)
C16:E16C16=MATCH(TRUE,(Member_Attendance_Records[#Headers]/1)>C14,0)
 

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
125
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJ
1All1 Year3 Years5 YearsAll1 Year3 Years5 Years
2Member NamesPACPACPACPACNPCNPCNPCNPC
3Person 14#N/A2412#N/A59
4Person 22#N/A221#N/A11
5Person 316#N/A7130#N/A00
6Person 412#N/A6104#N/A13
7Person 511#N/A5103#N/A23
8Person 62#N/A0211#N/A711
9Person 505#N/A553#N/A23
10Person 1001#N/A000#N/A00
11Person 1011#N/A000#N/A00
12
13All1 year3 Years5 Years
14Base dateN/A01/01/202001/01/201801/01/2016
15Start Date05/02/2015#N/A01/02/201804/02/2016
16Start Index2#N/A115
Attendance
Cell Formulas
RangeFormula
A3:A11A3=INDEX(Member_Attendance_Records[Member Names],ROWS(A$22:A22))
B3:I11B3=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)
D14D14=DATE(YEAR(TODAY())-3,1,1)
E14E14=DATE(YEAR(TODAY())-5,1,1)
B15:E15D15=INDEX(Member_Attendance_Records[#Headers],D16)
C14C14=DATE(YEAR(TODAY())-1,1,1)
C16C16=MATCH(TRUE,(Member_Attendance_Records[#Headers]/1)>C14,0)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 28, 2015
Messages
125
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHI
1All1 Year3 Years5 YearsAll1 Year3 Years5 Years
2Member NamesPACPACPACPACNPCNPCNPCNPC
3Person 1402412159
4Person 221221011
5Person 31617130000
6Person 41216104013
7Person 51115103023
8Person 62002111711
9Person 5050553123
10Person 10011111111
11Person 10100000000
12
13All1 year3 Years5 Years
14Base dateN/A01/01/202001/01/201801/01/2016
15Start Date05/02/201506/02/202001/02/201804/02/2016
16Start Index217115
Attendance
Cell Formulas
RangeFormula
A3:A11A3=INDEX(Member_Attendance_Records[Member Names],ROWS(A$22:A22))
B3:I11B3=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)
C14C14=DATE(YEAR(TODAY())-1,1,1)
D14D14=DATE(YEAR(TODAY())-3,1,1)
E14E14=DATE(YEAR(TODAY())-5,1,1)
B15:E15C15=INDEX(Member_Attendance_Records[#Headers],C16)
C16:E16C16=MATCH(TRUE,(Member_Attendance_Records[#Headers]/1)>C14,0)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 28, 2015
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,613
Messages
5,707,394
Members
421,508
Latest member
Jalayne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top