# MATCH function across multi column range

1. ## MATCH function across multi column range

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

2. ## Re: MATCH function across multi column range

based on your sample, perhaps:

Formula:
`Please Login or Register  to view this content.`

you state the teachers may teach more than one subject but obviously in the sample data that's not the case -- however, the above would cater for that.

so, for ex, if you change S6 to HPE then the COUNT in B9 should increase by 1.

3. ## Re: MATCH function across multi column range

Hero. Who would have thought it would be that simple. I was completely over complicating it.

Thank you very much.

A

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1