# sumproduct IF two arrays match a range of criteria?

1. ## 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  Register To Reply

2. ## 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)  Register To Reply

3. ## 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  Register To Reply