Count with Multiple Criteria - Follow up

[GMC The Macro Man]

Hi there,
This is a follow up question on a post i listed the other day and thanks to all who replied.
Here is the link for the previous question. How to count with Multiple Criteria

I'm looking for help on Outputs 2 and 3 on the attached sheet, and here is a breaking down of what is contained in the sheet.
1. The data table holds the pupil details and the subjects they are taking with their Target & Actual grades (They are given a Target Grade at the beginning of term and sit 3 exams through the year. The score for each exam is entered and this allows the teachers to see if the pupil is on Track to achieve their Target Grade)
2. The data table shown is only a small excerpt from the LIVE table which holds over 400 pupils with 15 columns for pupil personal details and then 25 subjects all with 5 columns each.
Requirement
Output 1. Completed with thanks to dreid1011, Eric W and Phuoc.
Output 2. I need to count how many pupils by YEAR, then by Nat Level and then by TARGET Grade. (I think this is a SUMPRODUCT similar to Output 1 but I don't know how to add the extra criteria to the formula)
Output 3. I need to count how many pupils by YEAR then by Nat Level but this time by Actual Grade at the time of the report. This will allow me to see a whole school view between the 2 Outputs on how many pupils are on Target (The pupils sit 3 exams, Oct, Jan and Mar. I want the latest results to be captured. Would it be better if I put a helper column at the end of each subject which would track the current Actual Grade)
I could then report this as Target v Actual in graph or table form.

If you are reading this post then thank you for doing so. Any help is welcome and I'm open to other ways for collating these reports if you have other ideas.

Thanks
GMC

Nat Grades.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	AG	AH
1	Pupil ID	Pupil Name	Year	ART Nat Level	Art Target Grade	Art Oct	Art Kan	Art Mar	Biology Nat Level	Biology Target Grade	Biology Oct	Biology Jan	Biology Mar	English Nat Level	English Target Grade	English Oct	English Jan	English Mar	Maths Nat Level	Maths Target Grade	Maths Oct	Maths Jan	Maths Mar			This table has been completed using the formula submitted by dreid1011
2	A1	Alice	S4	Nat 3	4	6								Nat 3	3	3			Nat 4	5	4					Year	Nat 3	Nat 4	Nat 5	Nat 6	Nat 7
3	A2	Alex	S4	Nat 4	4	6			Nat 3	3	4			Nat 3	3	3			Nat 4	5	4				1	S4	7	17	0	0	0
4	A3	Andrew	S4	Nat 3	3	5			Nat 4	3	3			Nat 4	4	3			Nat 4	5	4					S5	0	0	13	11	0
5	A4	Bobby	S4	Nat 4	3	5			Nat 4	2	3			Nat 4	4	3			Nat 4	3	6					S6	0	0	0	11	12
6	A5	Brian	S4	Nat 3	1	2								Nat 4	4	3			Nat 4	3	6
7	A6	Betty	S4						Nat 3	5	4			Nat 4	4	5			Nat 4	3	5					Count of Pupils by YEAR then by Nat Level who have a TARGET GRADE 1 to 6
8	A7	Charles	S4						Nat 4	5	5			Nat 4	4	5			Nat 4	4	4					Year	Nat Level	1	2	3	4	5	6
9	A8	Colin	S5	Nat 5	5	3								Nat 5	2	3			Nat 5	5	4				2	S4	Nat 3
10	A9	Derek	S5	Nat 6	5	3			Nat 6	3	5			Nat 5	2	3			Nat 6	4	5					S4	Nat 4
11	A10	Diane	S5						Nat 5	2	4			Nat 5	2	2			Nat 6	3	5					S5	Nat 5
12	A11	Edward	S5	Nat 5	6	7			Nat 5	4	4			Nat 6	3	2			Nat 6	4	4					S5	Nat 6
13	A12	Esther	S5	Nat 5	4	5								Nat 6	3	3			Nat 5	5	5					S6	Nat 6
14	A13	Frank	S5						Nat 6	3	4			Nat 6	2	3			Nat 5	3	5					S6	Nat 7
15	A14	Fiona	S5	Nat 6	4	4			Nat 5	3	4			Nat 6	1	3			Nat 5	4	5
16	A15	George	S6	Nat 7	2	2								Nat 6	1	3			Nat 6	5	4					Count of Pupils by YEAR then by Nat Level who their latest Actual Grade - Oct to Mar
17	A16	Gemma	S6						Nat 7	1	2			Nat 7	1	2			Nat 6	3	4					Year	Nat Level	1	2	3	4	5	6
18	A17	Harry	S6	Nat 7	2	2			Nat 7	1	1			Nat 7	1	2			Nat 6	4	4				3	S4	Nat 3
19	A18	Helen	S6	Nat 7	2	2			Nat 6	3	3			Nat 7	1	2			Nat 6	5	5					S4	Nat 4
20	A19	John	S6	Nat 6	1	3			Nat 6	3	3			Nat 7	2	2			Nat 7	3	3					S5	Nat 5
21	A20	Jenny	S6											Nat 6	3	2			Nat 7	2	3					S5	Nat 6
22	A21	Kevin	S6						Nat 6	4	3			Nat 6	3	4			Nat 7	3	3					S6	Nat 6
23																										S6	Nat 7
24
25
Sheet1

Cell Formulas
Range	Formula
AA3	AA3	=SUMPRODUCT((Nat_Grade[Year]=Z$3)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AA$2))
AB3	AB3	=SUMPRODUCT((Nat_Grade[Year]=Z$3)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AB$2))
AC3	AC3	=SUMPRODUCT((Nat_Grade[Year]=Z$3)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AC$2))
AD3	AD3	=SUMPRODUCT((Nat_Grade[Year]=Z$3)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AD$2))
AE3	AE3	=SUMPRODUCT((Nat_Grade[Year]=Z$3)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AE$2))
AA4	AA4	=SUMPRODUCT((Nat_Grade[Year]=Z$4)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AA$2))
AB4	AB4	=SUMPRODUCT((Nat_Grade[Year]=Z$4)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AB$2))
AC4	AC4	=SUMPRODUCT((Nat_Grade[Year]=Z$4)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AC$2))
AD4	AD4	=SUMPRODUCT((Nat_Grade[Year]=Z$4)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AD$2))
AE4	AE4	=SUMPRODUCT((Nat_Grade[Year]=Z$4)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AE$2))
AA5	AA5	=SUMPRODUCT((Nat_Grade[Year]=Z$5)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AA$2))
AB5	AB5	=SUMPRODUCT((Nat_Grade[Year]=Z$5)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AB$2))
AC5	AC5	=SUMPRODUCT((Nat_Grade[Year]=Z$5)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AC$2))
AD5	AD5	=SUMPRODUCT((Nat_Grade[Year]=Z$5)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AD$2))
AE5	AE5	=SUMPRODUCT((Nat_Grade[Year]=Z$5)*(Nat_Grade[[ART Nat Level]:[Maths Mar]]=AE$2))

 

[dermie_72]

countifs will allow you to count on multiple criteria, just select the range, then the criteria and it will return all items as an AND, so where all of the criteria are met.

Countifs help.

 

[myall_blues]

For any given year and NAT level (as shown for your result table 2) what is the target grade?
For example, Alice is in S4 NAT3 for Art and English, but the target grades are 4 and 3 respectively. Similarly, Andrew has NAT4 targets of 3, 4 and 5. Or do you not mind double counts?