+ Reply to Thread
Results 1 to 3 of 3

sumproduct IF two arrays match a range of criteria?

  1. #1
    Registered User
    Join Date
    11-09-2013
    Location
    South Shields, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    sumproduct IF two arrays match a range of criteria?

    student tracking % English and maths.xlsHi there, I have an issue with a tracking sheet for looking at students grades, there are two columns (arrays?) both equal in length, which contain the students grades for subjects English and maths. I want to get excel to look down each column and count the number of matches when both columns meet certain criteria. ie the grades are in the form of A+,A,A-,B+,B,B-,..... im looking to count all the matches of A+ to C-, and any combination there of for each row. sometimes a student might be above a C- in one subject but not the other. (this is what im trying to identify as a target group, by calculating how many students have c- or above in both subjects and therefore the students left are who i target) i hope this makes sense?

    I have added an attachment below to show what im working on. in cell O103 i would like it to return the percentage of students who are achieving C- or better in both English and maths. I am assuming its just a matter of formatting the cell for percentage once the formula is correct.

    Can anyone help??
    Cheers Paul
    Last edited by Paul Sword; 11-09-2013 at 08:01 PM. Reason: added attachment

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sumproduct IF two arrays match a range of criteria?

    This formula should do what you want

    =SUMPRODUCT(ISNUMBER(MATCH(LEFT(O3:O64),{"a","b","c"},0)*MATCH(LEFT(P3:P64),{"a","b","c"},0))+0)/COUNTA(P3:P64)
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-09-2013
    Location
    South Shields, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: sumproduct IF two arrays match a range of criteria?

    Thankyou so much for that, it works a treat. one last thing, well two really.
    firstly in the formula it says match(left on both, could you explain why please, id just like to try and understand how its working,
    and Secondly, is there a quick way of totalling the just the English grades from C- to A+ (column O3:O64) and then do the same for maths P3:P64. each result would go in cells O101 and O102 respectivly??

    thanks again for helping with earlier issue, You're a Ledgend Man!!!

    Paul

    Oooo i think i have it, ive used

    =SUMPRODUCT(ISNUMBER(MATCH(LEFT(O3:O64),{"a","b","c"},0)*1)+0) to return the number of students in the range o3:o64 who have a C- or better,

    then /counta(o3:o64) to give decimal percentage??

    I would be greatful if you could check that ive done it the correct way!
    Cheers Paul
    Last edited by Paul Sword; 11-10-2013 at 11:00 AM.

+ 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. VBA Arrays: Loading with Range vs Loop and selecting data given Criteria
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-17-2013, 10:30 PM
  2. SUMPRODUCT from using Criteria Arrays
    By quinceag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 06:30 PM
  3. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  4. Sum cells that match multiple criteria -- SUMPRODUCT
    By KINNEY0201 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2010, 04:59 PM
  5. [SOLVED] SUMPRODUCT: How to get number of rows which match criteria
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 07:05 AM

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