I have a spreadsheet containing student responses to an ACT practice test. For the English section, there are 75 items in 75 columns. Each row corresponds to a student. I have figured out formulas to convert a,b,c, and d to 1's and 0's for correct and incorrect, and count the number correct. I created an array and use vlookup to convert to a scale score.
My next task is to count the number of correct responses that apply to each subscore. There are 40 items in "Usage/Mechanics" (UM), and 35 in "Rhetorical Skills" (RH).
I inserted a row in my scoring table with a "1" in the column of each UM item, and a "2" for each RH. I want to count all correct responses in each row if the value of row 2 in the same column is a "1" and use that to calculate the UM sub-score. Then I will do the same for RH.
I have been playing around with COUNTIF, SUMIF, and VLOOKUP, but I don't know how to communicate to excel exactly what I am trying to do. Thanks in advance for any guidance.
Bookmarks