+ Reply to Thread
Results 1 to 3 of 3

MATCH function across multi column range

  1. #1
    Registered User
    Join Date
    05-09-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    2

    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
    Attached Images Attached Images

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: MATCH function across multi column range

    based on your sample, perhaps:

    Formula: copy to clipboard
    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. #3
    Registered User
    Join Date
    05-09-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    2

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multi-column dropdown list using multi-column dynamic range (no VBA)
    By ysdai7287 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-04-2018, 03:59 AM
  2. [SOLVED] Multi function look up/if/match
    By Chris2208 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-10-2017, 02:31 AM
  3. [SOLVED] how to ignore column (without result) in INDEX MATCH multi column
    By irruzzz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-09-2014, 07:05 AM
  4. [SOLVED] AutoFill Function to Match Range created by Column & Row
    By Trevasaurus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2013, 11:38 AM
  5. Replies: 19
    Last Post: 10-05-2012, 01:03 PM
  6. Multi level match function
    By drean03 in forum Excel General
    Replies: 4
    Last Post: 11-19-2009, 07:55 AM
  7. Excel 2007 : Multi level Match function
    By drean03 in forum Excel General
    Replies: 1
    Last Post: 11-19-2009, 07:37 AM

Tags for this Thread

Bookmarks

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