How to count with Multiple Criteria

[GMC The Macro Man]

Hi everyone, I hope you are all good
I'm looking to see if someone can help me with the best way to count using multiple criteria

I have a large table of student grades that I need to summarize which are in the attached table "Nat_Grades".
In Column "J3", I want to be able to count the number of pupils in S4 who have scored a "NAT 3" across the four subjects in the range C2:F22, (The answer is "7"). I then want to count the total number of Nat 4, Nat 5, Nat 6 & Nat 7's for pupils in S4. I would do the same for S5 and then S6.
This data would be entered in to cells J3:N5.
The table is just a small excerpt as the original has 25 different subjects and over 400 students so it would be a big task to do manually
I've looked across many forums but just cannot seem to find the formula to count across different columns with multiple criteria so I hope someone on here may help

TIA
GMC

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pupil ID	Year	ART	Biology	English	Maths
2	A1	S4	Nat 3		Nat 3	Nat 4			Year	Nat 3	Nat 4	Nat 5	Nat 6	Nat 7
3	A2	S4	Nat 4	Nat 3	Nat 3	Nat 4			S4
4	A3	S4	Nat 3	Nat 4	Nat 4	Nat 4			S5
5	A4	S4	Nat 4	Nat 4	Nat 4	Nat 4			S6
6	A5	S4	Nat 3		Nat 4	Nat 4
7	A6	S4		Nat 3	Nat 4	Nat 4
8	A7	S4		Nat 4	Nat 4	Nat 4
9	A8	S5	Nat 5		Nat 5	Nat 5
10	A9	S5	Nat 6	Nat 6	Nat 5	Nat 6
11	A10	S5		Nat 5	Nat 5	Nat 6
12	A11	S5	Nat 5	Nat 5	Nat 6	Nat 6
13	A12	S5	Nat 5		Nat 6	Nat 5
14	A13	S5		Nat 6	Nat 6	Nat 5
15	A14	S5	Nat 6	Nat 5	Nat 6	Nat 5
16	A15	S6	Nat 7		Nat 6	Nat 6
17	A16	S6		Nat 7	Nat 7	Nat 6
18	A17	S6	Nat 7	Nat 7	Nat 7	Nat 6
19	A18	S6	Nat 7	Nat 6	Nat 7	Nat 6
20	A19	S6	Nat 6	Nat 6	Nat 7	Nat 7
21	A20	S6			Nat 6	Nat 7
22	A21	S6		Nat 5	Nat 6	Nat 7
Sheet1

 

[dreid1011]

How about this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pupil ID	Year	ART	Biology	English	Maths
2	A1	S4	Nat 3		Nat 3	Nat 4			Year	Nat 3	Nat 4	Nat 5	Nat 6	Nat 7
3	A2	S4	Nat 4	Nat 3	Nat 3	Nat 4			S4	7	17	0	0	0
4	A3	S4	Nat 3	Nat 4	Nat 4	Nat 4			S5	0	0	13	11	0
5	A4	S4	Nat 4	Nat 4	Nat 4	Nat 4			S6	0	0	1	10	12
6	A5	S4	Nat 3		Nat 4	Nat 4
7	A6	S4		Nat 3	Nat 4	Nat 4
8	A7	S4		Nat 4	Nat 4	Nat 4
9	A8	S5	Nat 5		Nat 5	Nat 5
10	A9	S5	Nat 6	Nat 6	Nat 5	Nat 6
11	A10	S5		Nat 5	Nat 5	Nat 6
12	A11	S5	Nat 5	Nat 5	Nat 6	Nat 6
13	A12	S5	Nat 5		Nat 6	Nat 5
14	A13	S5		Nat 6	Nat 6	Nat 5
15	A14	S5	Nat 6	Nat 5	Nat 6	Nat 5
16	A15	S6	Nat 7		Nat 6	Nat 6
17	A16	S6		Nat 7	Nat 7	Nat 6
18	A17	S6	Nat 7	Nat 7	Nat 7	Nat 6
19	A18	S6	Nat 7	Nat 6	Nat 7	Nat 6
20	A19	S6	Nat 6	Nat 6	Nat 7	Nat 7
21	A20	S6			Nat 6	Nat 7
22	A21	S6		Nat 5	Nat 6	Nat 7
Sheet1

Cell Formulas
Range	Formula
J3:N5	J3	=SUMPRODUCT(($B$2:$B$22=$I3)*($C$2:$F$22=J$2))

 

[Eric W]

I think dreid1011 has answered the second part of your question.

I want to be able to count the number of pupils in S4 who have scored a "NAT 3" across the four subjects in the range C2:F22, (The answer is "7"). I then want to count the total number of Nat 4, Nat 5, Nat 6 & Nat 7's for pupils in S4.

He answered the part in blue. The answer to the green part is trickier. As far as I can tell, the answer to that is 0. There are no S4 pupils who have Nat 3 in all 4 subjects. There are 2 who have Nat 3 and Nat 4 mixed, and one with all Nat 4. The rest are missing at least one score. I came up with a formula that comes up with a count of students who scored at least Nat 3 in all 4 subjects. And Nat 4 or above in all four subjects, and so on. Let us know if this is what you're looking for, or if you want a variation, like at least 3 scores, or something like that.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pupil ID	Year	ART	Biology	English	Maths
2	A1	S4	Nat 3		Nat 3	Nat 4			Year	Nat 3	Nat 4	Nat 5	Nat 6	Nat 7
3	A2	S4	Nat 4	Nat 3	Nat 3	Nat 4			S4	2	1	0	0	0
4	A3	S4	Nat 3	Nat 4	Nat 4	Nat 4			S5	0	0	3	0	0
5	A4	S4	Nat 4	Nat 4	Nat 4	Nat 4			S6	0	0	0	3	0
6	A5	S4	Nat 3		Nat 4	Nat 4
7	A6	S4		Nat 3	Nat 4	Nat 4
8	A7	S4		Nat 4	Nat 4	Nat 4
9	A8	S5	Nat 5		Nat 5	Nat 5
10	A9	S5	Nat 6	Nat 6	Nat 5	Nat 6
11	A10	S5		Nat 5	Nat 5	Nat 6
12	A11	S5	Nat 5	Nat 5	Nat 6	Nat 6
13	A12	S5	Nat 5		Nat 6	Nat 5
14	A13	S5		Nat 6	Nat 6	Nat 5
15	A14	S5	Nat 6	Nat 5	Nat 6	Nat 5
16	A15	S6	Nat 7		Nat 6	Nat 6
17	A16	S6		Nat 7	Nat 7	Nat 6
18	A17	S6	Nat 7	Nat 7	Nat 7	Nat 6
19	A18	S6	Nat 7	Nat 6	Nat 7	Nat 6
20	A19	S6	Nat 6	Nat 6	Nat 7	Nat 7
21	A20	S6			Nat 6	Nat 7
22	A21	S6		Nat 5	Nat 6	Nat 7
23
Sheet6

Cell Formulas
Range	Formula
J3:M5	J3	=SUM(--(MMULT(--(RIGHT($C$2:$F$22,1)>=RIGHT(J$2,1))*($B$2:$B$22=$I3),{1;1;1;1})=4))-SUM(K3:$O3)
N3:N5	N3	=SUM(--(MMULT(--(RIGHT($C$2:$F$22,1)>=RIGHT(N$2,1))*($B$2:$B$22=$I3),{1;1;1;1})=4))-SUM($O3:O3)

 

[Phuoc]

The result is the same without using the RIGHT function.

=SUM(--(MMULT(($C$2:$F$22>=J$2)*($B$2:$B$22=$I3),{1;1;1;1})=4))-SUM(K3:$O3)

 

[GMC The Macro Man]

How about this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pupil ID	Year	ART	Biology	English	Maths
2	A1	S4	Nat 3		Nat 3	Nat 4			Year	Nat 3	Nat 4	Nat 5	Nat 6	Nat 7
3	A2	S4	Nat 4	Nat 3	Nat 3	Nat 4			S4	7	17	0	0	0
4	A3	S4	Nat 3	Nat 4	Nat 4	Nat 4			S5	0	0	13	11	0
5	A4	S4	Nat 4	Nat 4	Nat 4	Nat 4			S6	0	0	1	10	12
6	A5	S4	Nat 3		Nat 4	Nat 4
7	A6	S4		Nat 3	Nat 4	Nat 4
8	A7	S4		Nat 4	Nat 4	Nat 4
9	A8	S5	Nat 5		Nat 5	Nat 5
10	A9	S5	Nat 6	Nat 6	Nat 5	Nat 6
11	A10	S5		Nat 5	Nat 5	Nat 6
12	A11	S5	Nat 5	Nat 5	Nat 6	Nat 6
13	A12	S5	Nat 5		Nat 6	Nat 5
14	A13	S5		Nat 6	Nat 6	Nat 5
15	A14	S5	Nat 6	Nat 5	Nat 6	Nat 5
16	A15	S6	Nat 7		Nat 6	Nat 6
17	A16	S6		Nat 7	Nat 7	Nat 6
18	A17	S6	Nat 7	Nat 7	Nat 7	Nat 6
19	A18	S6	Nat 7	Nat 6	Nat 7	Nat 6
20	A19	S6	Nat 6	Nat 6	Nat 7	Nat 7
21	A20	S6			Nat 6	Nat 7
22	A21	S6		Nat 5	Nat 6	Nat 7
Sheet1

Cell Formulas
Range	Formula
J3:N5	J3	=SUMPRODUCT(($B$2:$B$22=$I3)*($C$2:$F$22=J$2))

Thank you dreid1011, this helped me get the results i was looking for

 

[GMC The Macro Man]

The result is the same without using the RIGHT function.

=SUM(--(MMULT(($C$2:$F$22>=J$2)*($B$2:$B$22=$I3),{1;1;1;1})=4))-SUM(K3:$O3)

Thank you Phuoc for taking the time to look at my solution

 

[dreid1011]

Thank you dreid1011, this helped me get the results i was looking for

You're welcome, and happy to help.