If Then Count calculation on Questionnaire

[GMC The Macro Man]

Good morning everyone
I'm trying to create a formula which looks at a cell and based on that criteria, goes to the data table and counts the number of entries and returns a % of correct answers. I believe it could be an IF, THEN, COUNT sort of thing but I don't know how to write it so I hope someone can help.
The attached file has 3 sheets.
Pupil List = has the user details
Questions = A list of questions & answers from pupils. These are imported from MS Forms questionnaires and these are done weekly so a pupil could have multiple dates
Results = The pupil sits a 1 to 1 verbal test and the score entered in Col D and Date in Col E. If they PASS they sit an test in MS FORMS and the data is captured in Questions sheet.

In RESULTS sheet Col H, I want to return the % score by counting the number of questions / the number correct = % for that date criteria

For Example
Robert sits the test in MS Forms on 8th Aug which has 5 questions and he gets 3 correct so 60% ( so the criteria is Robert, 8th Aug, Count questions divided by correct answers = %)

I hope I have explained this correctly

Questionairre.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Pupil Name	Class	Teachers Name	Voice Test Score	Assessment Date	Voice Test Result	Pupil Name	MS FORM Assessment Score %			Pass Mark
2	Robert	S3	Mr Brown	70	01/08/2023	Failed					75
3	Divya	S4	Mrs Green	82	04/08/2023	Passed	Divya
4	Robert	S3	Mr Brown	91	08/08/2023	Passed	Robert
5	Divya	S4	Mrs Green	79	11/08/2023	Passed	Divya
6	Robert	S3	Mr Brown	82	15/08/2023	Passed	Robert
7
8
9
10
11
12	Name is entered manually from drop down list	Returns the CLASS & TEACHERS name based on Column A		Manual entry - If score is 75 or above then they sit the MS FORM Test on the Assessment date		Output based on Value of Column D	IF PASSED enter Pupils Name - IF not PASSED then STOP	IF PASSED - Goto Questions Sheet, MATCH Pupil Name & Assessment Date - COUNT number of questions and correct answers and return %			Pass Mark
Results

Cell Formulas
Range	Formula
B2	B2	=XLOOKUP($A2,Pupil_List[Pupil Name],Pupil_List[Class],"")
C2:C5	C2	=XLOOKUP($A2,Pupil_List[Pupil Name],Pupil_List[Teacher],"",0)
B3:B5	B3	=XLOOKUP($A3,Pupil_List[Pupil Name],Pupil_List[Class]," ",0)
B6:B7	B6	=XLOOKUP($A6,DataTable[Pupil Name],DataTable[Class],"",0)
C6:C7	C6	=XLOOKUP($A6,DataTable[Pupil Name],DataTable[Teachers Name],"",0)
F2:F6	F2	=IF(D2<$K$2,"Failed","Passed")
G2:G6	G2	=IF($F2="Failed","",$A2)

Cells with Data Validation
Cell	Allow	Criteria
A2:A6	List	='Pupil List'!$A$2:$A$5

Questionairre.xlsx
	A	B	C	D	E	F
1	Pupil Name	Class	Teachers Name	Question	Right / Wrong	Assesment Date
2	Divya	S4	Mrs Green	1. What sort of creature is a dingo?	1	04/08/2023
3	Divya	S4	Mrs Green	2. In which country is the Taj Mahal?	1	04/08/2023
4	Divya	S4	Mrs Green	3. Who was the first man to walk on the moon?	0	04/08/2023
5	Divya	S4	Mrs Green	4. What are the names of Harry Potter’s parents?	1	04/08/2023
6	Divya	S4	Mrs Green	5. What are the four oceans called?	1	04/08/2023
7	Robert	S3	Mr Brown	1. Which language is spoken in Australia?	1	08/08/2023
8	Robert	S3	Mr Brown	2. What is the name of the tree that produces acorns?	1	08/08/2023
9	Robert	S3	Mr Brown	3. What is a baby kangaroo called?	0	08/08/2023
10	Robert	S3	Mr Brown	4. How many sides does a hexagon have?	1	08/08/2023
11	Robert	S3	Mr Brown	5. How many strings does a violin have?	0	08/08/2023
12	Divya	S4	Mrs Green	1. Which language is spoken in Australia?	1	11/08/2023
13	Divya	S4	Mrs Green	2. What is the name of the tree that produces acorns?	1	11/08/2023
14	Divya	S4	Mrs Green	3. What is a baby kangaroo called?	1	11/08/2023
15	Divya	S4	Mrs Green	4. How many sides does a hexagon have?	1	11/08/2023
16	Divya	S4	Mrs Green	5. How many strings does a violin have?	1	11/08/2023
17	Robert	S3	Mr Brown	1. What sort of creature is a dingo?	1	15/08/2023
18	Robert	S3	Mr Brown	2. In which country is the Taj Mahal?	1	15/08/2023
19	Robert	S3	Mr Brown	3. Who was the first man to walk on the moon?	0	15/08/2023
20	Robert	S3	Mr Brown	4. What are the names of Harry Potter’s parents?	0	15/08/2023
21	Robert	S3	Mr Brown	5. What are the four oceans called?	1	15/08/2023
Questions

Questionairre.xlsx
	A	B	C
1	Pupil Name	Class	Teacher
2	Divya	S4	Mrs Green
3	Peter	S4	Mrs Green
4	Robert	S3	Mr Brown
5	Sally	S3	Mr Brown
Pupil List

 

[Fluff]

How about

=LET(f,FILTER(Questions!$E$2:$E$100,(Questions!$A$2:$A$100=A2)*(Questions!$F$2:$F$100=E2)),ROWS(f)/SUM(f))

 

[GMC The Macro Man]

Hi Fluff,
As always, thank you for taking the time to read my posts. On your solution I'm not seeing the correct output. (see image).
If you look at Divya for the 4th Aug - She answered 5 questions but only got 4 right so that should be 80% and not 125%. The others are the same except Divya on 11th Aug which is 5 out of 5 so 100% so thats correct.
Am i doing something wrong ?

 

[Fluff]

If you want 80% then it should be the "number correct/number of questions" rather than

number of questions / the number correct

So you would need

=LET(f,FILTER(Questions!$E$2:$E$100,(Questions!$A$2:$A$100=A2)*(Questions!$F$2:$F$100=E2)),SUM(f)/ROWS(f))

 

[GMC The Macro Man]

Apologies bud, I should have noticed the way I had written the request. Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.

 

[GMC The Macro Man]

Sorry mate but I have just been asked a quick question on this.
The test is assigned and is normally completed on the same day, but say the pupil does not complete it same day but does on the next school day which is +1 or +3 (Fri - Mon).
Would there be a calc in the existing formula that you could add to say look at the assessment date and return the exact or date (+ within a couple of days.)

The pupils do these weekly so they are 7 days apart

 

[Fluff]

How about

=LET(f,FILTER(Questions!$E$2:$E$100,(Questions!$A$2:$A$100=A2)*(Questions!$F$2:$F$100>=E2)*(Questions!$F$2:$F$100<=E2+3)),SUM(f)/ROWS(f))