SUM COUNTIFS with AND + OR

[GMC The Macro Man]

Hi there, I'm hoping someone can help me with this formula where I'm think I need to use TWO "OR" functions in the criteria
In the attached sheet, I need to count the number of pupils as follows
CELL I2 - SUM/Count the number of pupils in YEAR S4, who are in BAND 1 or 2 and have scored an A
CELL I3 - SUM/Count the number of pupils in YEAR S4, who are in BAND 3 to 10 and have scored an A
I used these formulas to get this
=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"A",Nat_Grade[Band],{"1","2"}))
=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"A",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))

For the rest of this range, I2 to K7, the formula will be similar as above but change the YEAR group to S5 or S6 and the Maths to B or C.
Column "L" is where I have my challenge as this requires TWO "OR" functions (unless you guys can find an easier way).
For column L I need to SUM/Countifs the number of pupils, who are in BAND 1 or 2, (and here is the 2nd "OR") and have scored a D or E.

I cannot seem to get this to work. I've tried using AND but i'm not putting it correctly in the formula.

Could someone please help
Thanks
GMC

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Pupil ID	Pupil Name	Year	Band	Maths		Year	Band	A	B	C	D or E
2	A1	Alice	P1	1	A		S4	1~2	0	1	1
3	A2	Alex	S4	2	B			3~10	0	0	0
4	A3	Andrew	S4	1	C		S5	1~2	0	0	1
5	A4	Bobby	S4	3	D			3~10	2	2	1
6	A5	Brian	S4	2	E		S6	1~2	0	1	0
7	A6	Betty	S4	4	E			3~10	1	1	1
8	A7	Charles	S4	1	D
9	A8	Colin	S5	7	C
10	A9	Derek	S5	6	B		=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"A",Nat_Grade[Band],{"1","2"}))
11	A10	Diane	S5	5	A		=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"A",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
12	A11	Edward	S5	4	A
13	A12	Esther	S5	7	B
14	A13	Frank	S5	1	C
15	A14	Fiona	S5	2	D
16	A15	George	S6	6	E
17	A16	Gemma	S6	8	E
18	A17	Harry	S6	9	D
19	A18	Helen	S6	10	C
20	A19	John	S6	1	B
21	A20	Jenny	S6	3	A
22	A21	Kevin	S6	5	B
23
24
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"A",Nat_Grade[Band],{"1","2"}))
J2	J2	=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"B",Nat_Grade[Band],{"1","2"}))
K2	K2	=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"C",Nat_Grade[Band],{"1","2"}))
I3	I3	=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"A",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
J3	J3	=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"B",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
K3	K3	=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],"C",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
I4	I4	=SUM(COUNTIFS(Nat_Grade[Year],$G$4,Nat_Grade[Maths],"A",Nat_Grade[Band],{"1","2"}))
J4	J4	=SUM(COUNTIFS(Nat_Grade[Year],$G$4,Nat_Grade[Maths],"B",Nat_Grade[Band],{"1","2"}))
K4	K4	=SUM(COUNTIFS(Nat_Grade[Year],$G$4,Nat_Grade[Maths],"C",Nat_Grade[Band],{"1","2"}))
I5	I5	=SUM(COUNTIFS(Nat_Grade[Year],$G$4,Nat_Grade[Maths],"A",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
J5	J5	=SUM(COUNTIFS(Nat_Grade[Year],$G$4,Nat_Grade[Maths],"B",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
K5	K5	=SUM(COUNTIFS(Nat_Grade[Year],$G$4,Nat_Grade[Maths],"C",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
I6	I6	=SUM(COUNTIFS(Nat_Grade[Year],$G$6,Nat_Grade[Maths],"A",Nat_Grade[Band],{"1","2"}))
J6	J6	=SUM(COUNTIFS(Nat_Grade[Year],$G$6,Nat_Grade[Maths],"B",Nat_Grade[Band],{"1","2"}))
K6	K6	=SUM(COUNTIFS(Nat_Grade[Year],$G$6,Nat_Grade[Maths],"C",Nat_Grade[Band],{"1","2"}))
I7	I7	=SUM(COUNTIFS(Nat_Grade[Year],$G$6,Nat_Grade[Maths],"A",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
J7	J7	=SUM(COUNTIFS(Nat_Grade[Year],$G$6,Nat_Grade[Maths],"B",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))
K7	K7	=SUM(COUNTIFS(Nat_Grade[Year],$G$6,Nat_Grade[Maths],"C",Nat_Grade[Band],{"3","4","5","6","7","8","9","10"}))

 

[Fluff]

How about

=SUM(COUNTIFS(Nat_Grade[Year],$G$2,Nat_Grade[Maths],{"D";"E"},Nat_Grade[Band],{"1","2"}))

 

[GMC The Macro Man]

So its a Semi Colon instead of the comma which I was trying.

As always Fluff...Many Thanks

 

[Fluff]

You're welcome & thanks for the feedback.