Counting cells in a column in an array based on criteria

tjdickinson

Board Regular
Joined
Jun 26, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1First nameSurnameStudent NameTestDurationLow EL/INHigh EL/INLow PI/UIHigh PI/UIDifficultyConfidenceNotes1234567891011121314151617
2BobHopeBob Hope2
3JimmyStewartJimmy Stewart232:0583947889somewhat difficultsomewhat confidentAAC
4GraceKellyGrace Kelly115:2994947289somewhat easynot very confidentD
5CaryGrantCary Grant221:4478896156somewhat easysomewhat confidentCADB
Data

The test questions for both tests are arranged in the sheet called '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.

(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
ABCDEFGHIJK
1Question1-A1-B1-C1-D1-U2-A2-B2-C2-D2-U
21
32
43
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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,495
Messages
5,832,020
Members
430,104
Latest member
briannnnleong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top