Dear 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.
Sometimes teachers teach more than one subject etc. so they change frequently. I realised that I could rearrange the data use a "consolidating multiple ranges" pivot and using a standard COUNTIF function but I work on Mac Excel 2016 so that function is not available.
*I am new to this forum so I hope I have followed the procedures correctly. Many thanks in advance for any help.
A
Bookmarks