COUNTIF, MATCH, and OFFSET in Array

[adamscourfield]

Hi all,

I hoping for some advice here. I am trying to calculate the success rate of members of staff. I have each student's score in a number of subjects (A2:J6) and there teacher for each subject (L:U)
I want to COUNT the total number of student scores over a certain amount (e.g. >5) for the member of staff in cell A9. I can use a COUNTIF(OFFSET combination to count the number of scores over a certain amount, and I can count how many students each member of staff teaches with a COUNT(MATCH but I can't figure out how to make them work together.

To summerise, I want to:

COUNT how many times the teacher in A9 has got students more than a grade 5 across across range (A2:J6), but only in the subjects that they teach them in (hence the OFFSET of -11 columns to return the value). I have attached a screenshot to hopefully make things a little clearer.

*I am new to this forum so I hope I have followed the procedures correctly. Many thanks in advance for any help.


A

 

[Fluff]

If the teachers per subject don't change, as shown in your sample. Then try
=COUNTIF(INDEX(A2:J6,0,MATCH(INDEX(L1:U1,MATCH(A9,L2:U2,0)),A1:J1,0)),">5")

 

[adamscourfield]

Thanks Fluff,

What if I wanted to search throughout the entire range (L2:U6)? Also on the full data some teachers might teach different subjects so the formula would have to account for that. Is that possible?

A

 

[Fluff]

That's almost certainly posible, but unfortunately beyond my knowledge of formulae.

 

[adamscourfield]

Thank you for your help anyway Fluff.

A

 

[Fluff]

You're welcome

 

[sheetspread]

This works also:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	English	Math	Science	History	Geography	PE	MFL	Music	Art	PE		English	Math	Science	History	Geography	PE	MFL	Music	Art	PE
2	5	7	7	5	9	0	6	7	5	5		HPE	ALA	MWA	AOS	WFE	CGR	DWO	MWA	MWA	PGE
3	6	6	7	5	9	0	7	7	4	3		HPE	ALA	MWA	AOS	WFE	CGR	DWO	EPR	AMO	PGE
4	2	2	3	2	3	0	3	5	3	3		HPE	ALA	AOS	AOS	WFE	CGR	DWO	PGE	AMO	PGE
5	3	0	2	1	2	0	1	2	4	0		HPE	ALA	MWA	AOS	HPE	CGR	DWO	EPR	AMO	PGE
6	5	3	5	3	8	0	6	6	5	5		HPE	ALA	MWA	AOS	WFE	CGR	DWO	EPR	AMO	PGE
7
8	Teacher	>5
9	MWA	3
Sheet1

Cell Formulas
Range	Formula
B9	B9	=SUMPRODUCT(--($A$2:$J$6>5),--($L$2:$U$6=A9))

I recommend a better data structure, however, example to follow.....

 

[sheetspread]

Book1
	A	B	C	D	E	F	G
1	Student	Subject	Score	Teacher		Teacher	>5
2	AAA	English	5	HPE		MWA	3
3	AAA	Math	7	ALA
4	AAA	Science	7	MWA
5	AAA	History	5	AOS
6	AAA	Geography	9	WFE
7	AAA	PE	0	CGR
8	AAA	MFL	6	DWO
9	AAA	Music	7	MWA
10	AAA	Art	5	MWA
11	AAA	PE	5	PGE
12	BBB	English	6	HPE
13	BBB	Math	6	ALA
14	BBB	Science	7	MWA
15	BBB	History	5	AOS
16	BBB	Geography	9	WFE
17	BBB	PE	0	CGR
18	BBB	MFL	7	DWO
19	BBB	Music	7	EPR
20	BBB	Art	4	AMO
21	BBB	PE	3	PGE
22	CCC	English	2	HPE
23	CCC	Math	2	ALA
24	CCC	Science	3	AOS
25	CCC	History	2	AOS
26	CCC	Geography	3	WFE
27	CCC	PE	0	CGR
28	CCC	MFL	3	DWO
29	CCC	Music	5	PGE
30	CCC	Art	3	AMO
31	CCC	PE	3	PGE
32	DDD	English	3	HPE
33	DDD	Math	0	ALA
34	DDD	Science	2	MWA
35	DDD	History	1	AOS
36	DDD	Geography	2	HPE
37	DDD	PE	0	CGR
38	DDD	MFL	1	DWO
39	DDD	Music	2	EPR
40	DDD	Art	4	AMO
41	DDD	PE	0	PGE
42	EEE	English	5	HPE
43	EEE	Math	3	ALA
44	EEE	Science	5	MWA
45	EEE	History	3	AOS
46	EEE	Geography	8	WFE
47	EEE	PE	0	CGR
48	EEE	MFL	6	DWO
49	EEE	Music	6	EPR
50	EEE	Art	5	AMO
51	EEE	PE	5	PGE
Sheet4

Cell Formulas
Range	Formula
G2	G2	=COUNTIFS($C$2:$C$51,">5",$D$2:$D$51,F2)

will show you how to unpivot

 

[adamscourfield]

Thank you so much Sheetspread.

I am working on Mac however, and function has been removed for Excel for Mac 2016 which is extremely frustrating. However I have a better understanding of how to layout the data, which is great.

If you have any Mac related advice I would be highly appreciative.

A

 

[sheetspread]

=SUM((IF($L$2:$U$6=A9,IF($A$2:$J$6>5,1)))) ctrl-shift-enter also works in windows

Not sure about mac (which might be cmd-shift enter?)