Vlookup match sum between dates

[LDHall]

I am a teacher with a spreadsheet that contains all my pupils from a particular class and the marks they scored on questions different days.

I want to be able to select a start date and end date from a list I have set up on all possible dates and excel to calculate how many marks that each pupil got between those dates.

I have the following already set up which returns how many points a particular pupil scored on the start date:

=VLOOKUP(A3,SUMMARY!A2:AQM52,MATCH(E2,SUMMARY!A2:AQM2,0),FALSE)

and this returns how many points a particular pupil scored on the end date:

=VLOOKUP(A3,SUMMARY!A2:AQM52,MATCH(F2,SUMMARY!A2:AQM2,0),FALSE)

A3 is the cell with the pupil's name, A2:AQM52 are all the possible dates, E2 is the cell I have set up for the start date, F2 the end date.

I just can't figure out how to sum between these two selected dates.

 

[gaz_chops]

Welcome to the forum.

Try sumifs, something like

=SUMIFS(Marks Range,Names Range,A3,SUMMARY!A2:AQM52,">="&E2,SUMMARY!A2:AQM52,"<="&F2)

 

[gaz_chops]

Sorry I misread your ranges!

Try sumproduct

=SUMPRODUCT((SUMMARY!$A$2:$A$52=$A$3)*(SUMMARY!$B$2:$AQM$2>=$B$15)*(SUMMARY!$B$2:$AQM$2<=$C$15)*(SUMMARY!$B$3:$AQM$52))