Countifs with Multiple Criteria

[GMC The Macro Man]

Hi everyone, can someone help with this query
On the attached sheet, I need to calculate for each pupil, how many exams they are sitting for each Nat Level, and how many are they "Predicted" to pass at that level.
Columns M, O and Q counts the number for each of the Nat Levels in the range D2:J2 using this formula
M2=COUNTIFS($D$2:$J$2,"Nat 4")
O2=COUNTIFS($D$2:$J$2,"Nat 5")
Q2=COUNTIFS($D$2:$J$2,"Nat 6")

The next part is where I am stuck.
In columns N, P and R, how would I calculate the number of "Predicted" passes for each NAT Level by pupil (1 to 6 are pass, 7 to 9 are fail). I've manually entered the values her taken from columns F, I and L

A quick reference to the column names
A&D = Art & Design
BIO = Biology
BUS=Business
Nat Level = National Level grade at which a pupil will sit an exam at the end of year, according to their age, year group, knowledge.
Target = An agreed score at which the pupil should achieve in the exam
Predicted = Based the pupils daily work, this is the predicted score that they will achieve (1 to 6 are pass, 7 to 9 are fail)
Count NAT 4 = Counts the number of exams the pupil sat in the 3 subjects that equal to Nat 4. (the same for Nat 5 and then Nat 6)
Count of Pass = Counts the number of passes (1 to 6) from the predicted columns (F, I and L) - (the same for Nat 5 and then Nat 6)

The table is just a small excerpt from the main workbook where there are 26 subjects and over 400 pupils so any help would be great

TIA
GMC

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Full Name	Year Group	Reg Class	A&D Nat Level	A&D Target	A&D Predicted	Bio Nat Level	Bio Target	Bio Predicted	Bus Nat Level	Bus Target	Bus Predicted		Count NAT 4	Count of PASS	Count NAT 5	Count of PASS	Count NAT 6	Count of PASS
2	Abbie Running	S4	4A	Nat 5	5	6	Nat 6	4	4	Nat 5	6	8		0	0	2	1	1	1
3	Aiden Gall	S4	4A	Nat 4	5	5	Nat 5	4	4	Nat 6	6	7		1	1	1	1	1	0
4	Aiden John Kearney	S4	4A	Nat 4	5	5	Nat 4	4	7	Nat 6	6	6		2	1	0	0	1	1
5	Alana Callaghan	S4	4A	Nat 5	5	7	Nat 5	4	8	Nat 4	6	6		1	1	2	0	0	0
Sheet1

Cell Formulas
Range	Formula
N2:N5	N2	=COUNTIFS($D2:$J2,"Nat 4")
P2:P5	P2	=COUNTIFS($D2:$J2,"Nat 5")
R2:R5	R2	=COUNTIFS($D2:$J2,"Nat 6")

 

[Kaper]

Try new, "universal" version of N2 (assuming there is always 3letters, space and single digit in designation) :

=COUNTIF($D2:$J2,RIGHT(N$1,5))

(but of course you can keep the "manually edited formula")
and in O2:

=COUNTIFS($D2:$J2,RIGHT(N$1,5),$F$1:$L$1,"* Predicted",$F2:$L2,"<7")

If you have both new,
Now copy this pair of formulas to columns on roght (P&Q then R & S)
if only second one - copy to Q2 and S2

and now copy all 6 formulas down.

 

[GMC The Macro Man]

Try new, "universal" version of N2 (assuming there is always 3letters, space and single digit in designation) :

=COUNTIF($D2:$J2,RIGHT(N$1,5))

(but of course you can keep the "manually edited formula")
and in O2:

=COUNTIFS($D2:$J2,RIGHT(N$1,5),$F$1:$L$1,"* Predicted",$F2:$L2,"<7")

If you have both new,
Now copy this pair of formulas to columns on roght (P&Q then R & S)
if only second one - copy to Q2 and S2

and now copy all 6 formulas down.

Hey Kaper

Thank you so much for this formula, I have tried it on the main workbook and it works perfectly. (Of course it would) . Thanks again

 

[Kaper]

Thanks for feedback, kind words and for marking answer as a solution.