Count Not of Grades

[GMC The Macro Man]

Hi Team, I hope you are all doing well

I have 400 students who will sit at least 5 exams from a list of 25 subjects. The report is downloaded from the mainframe in a continuous table form so it looks like this.

St Maurice High School - June 2024.xlsx
	A	B	C	D	E
1	ID No	Forename	Year Group	Nat Level	Grade
2	P12345	Aaliyah	S6
3	P12346	Abby	S6	Nat 6	4
4	P12347	Aidan	S6
5	P12348	Arran	S6	Nat 6	6
6	P12349	Ayden	S6
Sheet3

It will show the list of 400 students and how they scored on the 1st subject, and then on the bottom line it will show the 400 pupils on the 2nd subject, then on the bottom line it will show the 400 on the 3rd subject.. and so on ... It will continuously add this 25 times for the 25 subjects.

What I am trying to achieve is from this data table, to count, the total of NAT Levels each individual pupil sat at "Nat 3, Nat4, Nat 5 & Nat 6" and how many of these Levels had a grade of less than 7. (Less than 7 means a Pass).

I want my output to look like this it to look like this table below ( If you filter the main Data table at the bottom, you will "P12346" has only sat 3 at NAT 6 and all are below "7")

St Maurice High School - June 2024.xlsx
	H	I	J	K	L	M	N	O	P	Q
1	ID No	Year Group	Nat 3	7	Nat 4	7	Nat 5	7	Nat 6	7
3	P12346	S6	0	0	0	0	0	0	3	3
12	P12355	S5	0	0	0	0	2	1	0	0
29	P12372	S4	1	1	2	1	0	0	0	0
Sheet3

Here is the data i used for this conversation, but the size of this would be 400 pupils multiplied by 25 subjects so 10k rows. ( I Don't mean to confuse you here)
I hope someone can help here
Thanks

St Maurice High School - June 2024.xlsx
	A	B	C	D	E
1	ID No	Forename	Year Group	Nat Level	Grade
2	P12345	Aaliyah	S6
3	P12346	Abby	S6	Nat 6	4
4	P12347	Aidan	S6
5	P12348	Arran	S6	Nat 6	6
6	P12349	Ayden	S6
7	P12350	Aidan	S6
8	P12351	Aimee Marie	S6	Nat 6	3
9	P12352	Alexa Ann	S6
10	P12353	Alisha Selina	S6
11	P12354	Andrew Lukas	S6	Nat 6	8
12	P12355	Zoe McCann	S5
13	P12356	Aiden	S5
14	P12357	Aimee Kathleen	S5	Nat 5	7
15	P12358	Alleisha	S5
16	P12359	Brandon David	S5
17	P12360	Abbie	S5	Nat 5	5
18	P12361	Bethany	S5
19	P12362	Caitlin	S5
20	P12363	Carly	S5	Nat 5	3
21	P12364	Dylan	S5
22	P12365	Adam	S4
23	P12366	Aimee	S4	Nat 4	1
24	P12367	Bobby David	S4
25	P12368	Cole	S4
26	P12369	Corry	S4	Nat 4	7
27	P12370	Aaron Anthony	S4
28	P12371	Aaron	S4
29	P12372	Beth	S4	Nat 3	6
30	P12373	Aiden	S4
31	P12374	Aimee	S4	Nat 4	4
32	P12345	Aaliyah	S6
33	P12346	Abby	S6	Nat 6	2
34	P12347	Aidan	S6	Nat 6	6
35	P12348	Arran	S6	Nat 6	6
36	P12349	Ayden	S6
37	P12350	Aidan	S6
38	P12351	Aimee Marie	S6	Nat 6	3
39	P12352	Alexa Ann	S6
40	P12353	Alisha Selina	S6	Nat 6	7
41	P12354	Andrew Lukas	S6
42	P12355	Zoe McCann	S5	Nat 5	9
43	P12356	Aiden	S5
44	P12357	Aimee Kathleen	S5	Nat 4	3
45	P12358	Alleisha	S5	Nat 5	4
46	P12359	Brandon David	S5
47	P12360	Abbie	S5	Nat 4	5
48	P12361	Bethany	S5
49	P12362	Caitlin	S5	Nat 6	6
50	P12363	Carly	S5
51	P12364	Dylan	S5	Nat 5	2
52	P12365	Adam	S4
53	P12366	Aimee	S4	Nat 5	7
54	P12367	Bobby David	S4	Nat 5	8
55	P12368	Cole	S4
56	P12369	Corry	S4	Nat 3	3
57	P12370	Aaron Anthony	S4	Nat 3	5
58	P12371	Aaron	S4
59	P12372	Beth	S4	Nat 4	6
60	P12373	Aiden	S4
61	P12374	Aimee	S4	Nat 4	7
62	P12345	Aaliyah	S6
63	P12346	Abby	S6	Nat 6	2
64	P12347	Aidan	S6
65	P12348	Arran	S6	Nat 6	6
66	P12349	Ayden	S6
67	P12350	Aidan	S6	Nat 5	4
68	P12351	Aimee Marie	S6	Nat 5	5
69	P12352	Alexa Ann	S6	Nat 6	5
70	P12353	Alisha Selina	S6
71	P12354	Andrew Lukas	S6
72	P12355	Zoe McCann	S5	Nat 5	3
73	P12356	Aiden	S5
74	P12357	Aimee Kathleen	S5
75	P12358	Alleisha	S5	Nat 5	3
76	P12359	Brandon David	S5
77	P12360	Abbie	S5	Nat 5	2
78	P12361	Bethany	S5
79	P12362	Caitlin	S5
80	P12363	Carly	S5	Nat 6	2
81	P12364	Dylan	S5
82	P12365	Adam	S4	Nat 5	6
83	P12366	Aimee	S4
84	P12367	Bobby David	S4
85	P12368	Cole	S4	Nat 4	8
86	P12369	Corry	S4
87	P12370	Aaron Anthony	S4	Nat 4	6
88	P12371	Aaron	S4
89	P12372	Beth	S4	Nat 4	4
90	P12373	Aiden	S4	Nat 4	9
91	P12374	Aimee	S4	Nat 3	2
Sheet3

 

[GMC The Macro Man]

I found this on Youtube, ..thanks to Computergaga. This will give me what I'm looking for at the moment, but if anyone knows a better way to complete it would be appreciated

I used this for counting the NAT Levels - =SUMPRODUCT((Table37[ID No]=$H2)*(Table37[Nat Level]=J$1))
And this for counting the Nat Levels which are less than 7 - =SUMPRODUCT((Table37[ID No]=$H2)*(Table37[Nat Level]=J$1)*(Table37[Grade]<K$1))

Thanks for looking
George

 

[dreid1011]

Similar to what you already found:

Book1
	G	H	I	J	K	L	M	N	O	P
1	ID No	Year Group	Nat 3	7	Nat 4	7	Nat 5	7	Nat 6	7
2	P12345	S6	0	0	0	0	0	0	0	0
3	P12346	S6	0	0	0	0	0	0	3	3
4	P12347	S6	0	0	0	0	0	0	1	1
5	P12348	S6	0	0	0	0	0	0	3	3
6	P12349	S6	0	0	0	0	0	0	0	0
7	P12350	S6	0	0	0	0	1	1	0	0
8	P12351	S6	0	0	0	0	1	1	2	2
9	P12352	S6	0	0	0	0	0	0	1	1
10	P12353	S6	0	0	0	0	0	0	1	0
11	P12354	S6	0	0	0	0	0	0	1	0
12	P12355	S5	0	0	0	0	2	1	0	0
13	P12356	S5	0	0	0	0	0	0	0	0
14	P12357	S5	0	0	1	1	1	0	0	0
15	P12358	S5	0	0	0	0	2	2	0	0
16	P12359	S5	0	0	0	0	0	0	0	0
17	P12360	S5	0	0	1	1	2	2	0	0
18	P12361	S5	0	0	0	0	0	0	0	0
19	P12362	S5	0	0	0	0	0	0	1	1
20	P12363	S5	0	0	0	0	1	1	1	1
21	P12364	S5	0	0	0	0	1	1	0	0
22	P12365	S4	0	0	0	0	1	1	0	0
23	P12366	S4	0	0	1	1	1	0	0	0
24	P12367	S4	0	0	0	0	1	0	0	0
25	P12368	S4	0	0	1	0	0	0	0	0
26	P12369	S4	1	1	1	0	0	0	0	0
27	P12370	S4	1	1	1	1	0	0	0	0
28	P12371	S4	0	0	0	0	0	0	0	0
29	P12372	S4	1	1	2	2	0	0	0	0
30	P12373	S4	0	0	1	0	0	0	0	0
31	P12374	S4	1	1	2	1	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
G2:H31	G2	=CHOOSECOLS(UNIQUE(A2:C91),{1,3})
I2:I31,K2:K31,M2:M31,O2:O31	I2	=COUNTIFS($A$2:$A$91,$G2,$D$2:$D$91,I$1)
J2:J31,L2:L31,N2:N31,P2:P31	J2	=COUNTIFS($A$2:$A$91,$G2,$D$2:$D$91,I$1,$E$2:$E$91,"<7")
Dynamic array formulas.

 

[GMC The Macro Man]

Similar to what you already found:

Book1
	G	H	I	J	K	L	M	N	O	P
1	ID No	Year Group	Nat 3	7	Nat 4	7	Nat 5	7	Nat 6	7
2	P12345	S6	0	0	0	0	0	0	0	0
3	P12346	S6	0	0	0	0	0	0	3	3
4	P12347	S6	0	0	0	0	0	0	1	1
5	P12348	S6	0	0	0	0	0	0	3	3
6	P12349	S6	0	0	0	0	0	0	0	0
7	P12350	S6	0	0	0	0	1	1	0	0
8	P12351	S6	0	0	0	0	1	1	2	2
9	P12352	S6	0	0	0	0	0	0	1	1
10	P12353	S6	0	0	0	0	0	0	1	0
11	P12354	S6	0	0	0	0	0	0	1	0
12	P12355	S5	0	0	0	0	2	1	0	0
13	P12356	S5	0	0	0	0	0	0	0	0
14	P12357	S5	0	0	1	1	1	0	0	0
15	P12358	S5	0	0	0	0	2	2	0	0
16	P12359	S5	0	0	0	0	0	0	0	0
17	P12360	S5	0	0	1	1	2	2	0	0
18	P12361	S5	0	0	0	0	0	0	0	0
19	P12362	S5	0	0	0	0	0	0	1	1
20	P12363	S5	0	0	0	0	1	1	1	1
21	P12364	S5	0	0	0	0	1	1	0	0
22	P12365	S4	0	0	0	0	1	1	0	0
23	P12366	S4	0	0	1	1	1	0	0	0
24	P12367	S4	0	0	0	0	1	0	0	0
25	P12368	S4	0	0	1	0	0	0	0	0
26	P12369	S4	1	1	1	0	0	0	0	0
27	P12370	S4	1	1	1	1	0	0	0	0
28	P12371	S4	0	0	0	0	0	0	0	0
29	P12372	S4	1	1	2	2	0	0	0	0
30	P12373	S4	0	0	1	0	0	0	0	0
31	P12374	S4	1	1	2	1	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
G2:H31	G2	=CHOOSECOLS(UNIQUE(A2:C91),{1,3})
I2:I31,K2:K31,M2:M31,O2:O31	I2	=COUNTIFS($A$2:$A$91,$G2,$D$2:$D$91,I$1)
J2:J31,L2:L31,N2:N31,P2:P31	J2	=COUNTIFS($A$2:$A$91,$G2,$D$2:$D$91,I$1,$E$2:$E$91,"<7")
Dynamic array formulas.

Cheers and thanks for taking the time to look. I'm halfway through putting my solutions in so I will stick with this at the moment, but I will certainly give yours a go later
All the best
George