Microsoft Excel - how to subtotal columns from another sheet matched to the values in validation list

HI,
on sheet  Other Achievement I've got 2 validation lists: one of them is to select for example gender, ethnicity .... another one to select accordingly: F/M or different ethnicity.
 
now I want to use sumproduct  to count the number of students on sheet grades with criteria from validation list matched to table on sheet SelLIST

(for example: if data validation list is selected to gender and to F use sheet grades, column D, values F ,
but if data validation is selected to Ethnicity and to Any other white background as ethnicity use column grades!E =Any other white background .)

sorry, I can not upload any files (tried IE and Chrome), below are pics:
 Thank you in advance for help and suggestions

sheet Other Achievement 



formula in B6: when filtered on gender =F and DR:=5 it should return 95 

=(SUMPRODUCT(SUBTOTAL(3,OFFSET(grade!$A$3:$A$270,ROW(grade!$A$3:$A$270)-MIN(ROW(grade!$A$3:$A$270)),,1))*(grade!$DR$3:$DR$270>=5)*(ISNUMBER(MATCH(A4,VLOOKUP(A3,U,3,FALSE)))))) 


sheet grades:

sheet SelLIST (named range: U : A2:C10)

Hello Ewa,

Possibly the formula that you want goes something like this:

=SUMPRODUCT((OFFSET(grade!A3:A270,0,3)=A4)*(OFFSET(grade!A3:A270,0,8)>=5))

The column offsets would be determined from your lookup table rather than the range address strings that you currently have although it appears that the DR column is constant for all queries so that doesn't have to be an offset if you don't want to.

If the number of students is likely to vary with time you could also make the base range (ColumnA) dynamic so that you don't have to go changing the formulas every time the number of students changes.  This would be a good reason to leave the ColumnDR reference as an offset.

Hope this helps.  Let me know how you go.

Harry

Hello again Harry :),

I'll be trying to work with Offset today but I'll still attach the file  (attaching works today :) ) 
as i'm not very familiar with offset, trying to learn thru...
Could you have a  look please?
match column in another sheet .zip 
I also want to clear one of the validation list when the other changes, trying to learn VBA, i think i need to use OFfset here too, could you see where I'm doing something wrong wrong please?

Many thanks
Ewa

Hello Ewa,

Have a look at this.  I have included some notes in the sheet.

match column in another sheet.zip

Harry

This is brilliant Harry! 

I'm now using this formuala for criteria in A3:4, separate for ALL, Female and Male:

=SUMPRODUCT((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,
MATCH(A3,grade!$A$2:$DR$2,)-1)=$A$4)
*(OFFSET(grade!$DR$3,0,0,student_no,1)>=5)
*(OFFSET(grade!$D$3,0,0,student_no,1)="f"))


and thank you for vba code, it does exactly what I needed. 

Ewa

Hi, really sorry to be a pain but how can i modify the formula to sum instead of count?
Thank you again for your time and expertise
Ewa

=SUMPRODUCT((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,
MATCH(A3,grade!$A$2:$DR$2,)-1)=$A$4)
*(OFFSET(grade!$DR$3,0,0,student_no,1)>=5)

Hi Ewa,

Maybe this is what you want.

=SUMPRODUCT((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,MATCH(A3,grade!$A$2:$DR$2,)-1)=$A$4)*(OFFSET(grade!$DR$3,0,0,student_no,1)>=5),OFFSET(grade!$DR$3,0,0,student_no,1))

Let me know if not.

Harry

Thank you, it does exactly what I need 

Hi Harry, you'll be really fed up with me but could you advise me how to convert this formula to average cells? (same sheet)

I've got this one and it works:
=AVERAGE((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,MATCH($A$3,grade!$A$2:$DS$2,)-1)=$A$4),OFFSET(grade!$Dr$3,0,0,student_no,1))

but when I want to add another condition I'm getting a wrong result :
=AVERAGE((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,MATCH($A$3,grade!$A$2:$DS$2,)-1)=$A$4),(OFFSET(grade!$D$3,0,0,student_no,1)="f"),OFFSET(grade!$Dr$3,0,0,student_no,1)) 

Hello Ewa,

You need a multiply (*) between your conditionals not a comma.

Regards

Harry

no, I tried multiply , it gives the wrong result,
even if I put value that is not in that column for example "d" (only f and m are possible) i get almost the same result as if I entered F or M, something
 else must be wrong 
=AVERAGE((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,MATCH($A$3,grade!$A$2:$DS$2,)-1)=$A$4)*(OFFSET(grade!$D$3,0,0,student_no,1)="d"),OFFSET(grade!$DX$3,0,0,student_no,1))

got it! :-) I simply divided sumproduct by the number of matched students:

=(SUMPRODUCT((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,MATCH($A$3,grade!$A$2:$DS$2,)-1)=$A$4)*(OFFSET(grade!$A$3,0,0,student_no,1)<>"")*(OFFSET(grade!$D$3,0,0,student_no,1)="f"),OFFSET(grade!$DX$3,0,0,student_no,1)))/
SUMPRODUCT((OFFSET(OFFSET(grade!$A$3,0,0,student_no,1),0,MATCH($A$3,grade!$A$2:$DS$2,)-1)=$A$4)*(OFFSET(grade!$D$3,0,0,student_no,1)="f"))

Hello Ewa,

Yes, sorry about leading you astray.  I have to admit that I only took a quick look at it and it only half registered that you were no longer in SUMPRODUCT mode.  Good to see that you worked it out.

Regards

Harry