Counting cells in a column in an array based on criteria

[tjdickinson]

In my workbook, the tab 'Data' includes the names of students who have completed either of two tests. (Note the actual workbook goes to column CG with 72 questions.)

2021-2022 CEFR Test Results.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	First name	Surname	Student Name	Test	Duration	Low EL/IN	High EL/IN	Low PI/UI	High PI/UI	Difficulty	Confidence	Notes	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
2	Bob	Hope	Bob Hope	2
3	Jimmy	Stewart	Jimmy Stewart	2	32:05	83	94	78	89	somewhat difficult	somewhat confident					A			A				C
4	Grace	Kelly	Grace Kelly	1	15:29	94	94	72	89	somewhat easy	not very confident																		D
5	Cary	Grant	Cary Grant	2	21:44	78	89	61	56	somewhat easy	somewhat confident							C	A			D			B
Data

The test questions for both tests are arranged in the sheet called 'Test Questions':

2021-2022 CEFR Test Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Question Number	Question-T1	A-T1	B-T1	C-T1	D-T1	Correct-T1	Question-T2	A-T2	B-T2	C-T2	D-T2	Correct-T2
2	1	A: Excuse me, are you from here? B: Sorry, ... . I'm from Lisbon.	A) I'm	B) I am	C) I'm not	D) I do not	C	A: Do you like my pasta sauce? B: It ... delicious!	A) tastes	B) will taste	C) is tasting	D) has tasted	A
3	2	Spain is famous for ... paella – a meal made with rice and fish.	A) it	B) its	C) they	D) their	B	A: Are you from around here? B: Yes, I ... in Lisbon all my life.	A) live	B) lived	C) am living	D) have lived	D
4	3	I have a sister. ... name's Nicole.	A) Her	B) She	C) Hers	D) She's	A	A: I need to buy a new printer. B: What ... of getting?	A) you thinking	B) do you think	C) will you think	D) are you thinking	D
5	4	Carlos ... basketball on Wednesdays.	A) play	B) plays	C) to play	D) playing	B	A: Was Karla on time for her interview? B: No, even though she ... early.	A) has left	B) had left	C) would leave	D) was leaving	B
6	5	... I go to the park to read my favourite book.	A) Never	B) Always	C) Sometimes	D) Hardly ever	C	We ... dinner when suddenly the lights went out.	A) have had	B) are having	C) would have	D) were having	D
Test Questions

Everything is presented in a sheet called 'Results': I select the student's name from a drop down list, and it shows me the results, the notes, and each question, with the correct answer and the student's incorrect answer indicated, connected to a spinner. This allows me to go over each student's results with them without disclosing another student's results.

2021-2022 CEFR Test Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Class	Student Name	Test	Duration	Low EL/IN	High EL/IN	Low PI/UI	High PI/UI	Difficulty	Confidence
2		5LAT	Grace Kelly	2	15:29	94	94	72	89	somewhat easy	not very confident
3
4		Notes:	0
5
6
7
8
9
10
11		Question
12		1	A: Do you like my pasta sauce? B: It ... delicious!
13
14		ü	A) tastes
15			B) will taste
16			C) is tasting
17			D) has tasted
18
Results

(NB: Results!C2 has an ActiveX dropdown linked to C2 and pulling the names from Data!D2:D150. Results!B12 has a conditional format indicating the difficulty of the question. Results!B13 has spinner arrows linked to Results!B12 to navigate the question number. Results!B14:B17 contain formulas which indicate the correct answer and the student's incorrect answer; the symbols are font-dependent (Wingdings).)

Now, my question:
In a new sheet 'Stats', I want to extract from 'Data' a count of how many students gave each answer.

2021-2022 CEFR Test Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Question	1-A	1-B	1-C	1-D	1-U	2-A	2-B	2-C	2-D	2-U
2	1
3	2
4	3
Stats

I'm looking for a formula I can (relatively easily) copy and paste into all the cells to do the count. I don't want to spend ages changing references in each cell.

Basically, the formula in Stats!B2 (and similarly for the other cells) needs to:

• Compare LEFT(Stats!B1,1) to Data!$E$2:$E$150 to know which rows to include
• Count the number of non-blank cells in Data!$F$2:$F$150 to know how many people have completed the test (only counting those rows with matching test number)
• Compare RIGHT(Stats!B1,1) to 'Test Questions'!$G$2:$G$73 if Test 1 or !$M$2:$M$73 if Test 2 to determine whether this option was the right answer for the question
  • If TRUE: then count non-blank cells in the column of array Data!$N$2:$CG$150 with index equal to Stats!A2 (so, A2=1 thus column N, A3=2 thus column O, A4=3 thus column P, etc.), but only counting the rows in which column E matches LEFT(Stats!B1,1). This should count the number of students who answered the question correctly.
  • If FALSE: count the number of times RIGHT(Stats!B1,1) appears in the column of array Data!$N$2:$CG$150 with index equal to Stats!A2 (so, A2=1 thus column N, A3=2 thus column O, A4=3 thus column P, etc.), but again only counting rows in which column E matches LEFT(Stats!B1,1). This counts the number of times this incorrect answer was selected.

But again, it should either be an array formula, or one that I can copy and paste in the other cells so that the references update automatically.

Finally, I want to add a data bars conditional format to Results!C14:C17 which indicates the corresponding data from the Stats! worksheet (how many people gave that response). Of course, it needs to identify the test number from Results!D2 to know which set of data to use.

Thank you very much for any help you can give me!