Roll up, roll up!
I have a spreadsheet which I'm using to try to analyse exam results for school pupils. Most pupils will study 8 subjects, but some will only do 6 or 7. Each pupil will be awarded a grade between 1 and 6 for each subject they sit.
I have a formula which will count the number of grade 5s and 6s, a separate formula which counts 3s and 4s and a third which counts 1s and 2s. Each of these formulae is predicated on there being exactly 8 subjects per pupil, but I need it to automatically adapt to a pupil having only 6 or 7 grades (or 5 or 9 for that matter).
Make sense?
Each pupil is identified by an ID number and the data is sorted by this number so that all the grades for a single pupil are grouped. The formula which counts the 5s and 6s is:
=IF(AND($C2=$C3,ISNUMBER($D2) ),IF($C2<>$C1,(COUNTIF($D2:$D9,5)+(COUNTIF($D2:$D9,6))),""),"")
The context is shown in the screenshot below. The problem is the fixed cell range (D2:D9). How can I vary this to count only the 5s and 6s for the same pupil?

scqf by stuartie_c, on Flickr

