Share via

COUNTIFS (?) for counting multiple choice answers

Anonymous
2019-10-24T22:13:41+00:00

I need to calculate the number of correct multiple choice answers to a set of questions.

Here is a set of data that can be used and what I have done:  [In reality, there are about twenty (20) multiple choice questions with each question having only one correct answer out of five (5) possible selections (A, B, C, D or E).]

Version Q1 Q2 Q3 Q4 Q5 Q6 Q7
A C C C B D B B
B E D C E D A B
A B E C B E A A
B E E C B D C A
B E D C B D A D

Correct Answers:

Version Q1 Q2 Q3 Q4 Q5 Q6 Q7
A B E A B E C C
B E D C E D A B

Each row in the first table are responses by various test takers.  Table 2, the Correct Answers, contain the valid multiple choice response selection that is correct.  Any answer, including a blank, that doesn't math the Correct Answers for it's Test Version, is to be considered wrong.

  1. I need to determine, by test taker and the test takers test version, the number of correct answers.  My attempt, incorrect, for this request is =IF(H9=$H$6,COUNTIF(J9:V9,"="&J6),COUNTIF(J9:V9,"="&J7)) but this yields the number of responses by the test taker that is the same as in J6.  I was trying to increment/iterate down the column by test which is the initial IF test condition of H9=$H$6.  H9 is the test taker's test version and $H$6 is the Test A version.  FYI, there are only two test versions at this time.

Hope this helps

Thanks!

  1. I need to determine which question had the greatest number of correct or incorrect answers.  Either one is fine.
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2019-10-26T10:14:37+00:00

Enter below formula in cell C7 and copy to the right:

=SUMPRODUCT(--(LOOKUP(MATCH($B$9:$B$13,$A$4:$A$5,0),ROW($B$4:$H$5)-ROW($B$4)+1,B$4:B$5)=C$9:C$13))

This returns the TOTAL count per Q ie. separate for Q1, Q2, ... Q7.

Regards,

Amit Tandon

www.excelanytime.com

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2019-10-26T05:59:27+00:00

Option 1: Enter Array formula (Ctrl+Shift+Enter) in cell C16 and copy down and to the right:

=SUM(--(IF($B$9:$B$13=$B16,C$9:C$13)=B4))

Option 2: Enter Array formula (Ctrl+Shift+Enter) in cell C19 and copy down and to the right:

=SUM(--(IF($B$9:$B$13=$B16,C$9:C$13)=INDEX($B$4:$H$5,MATCH($B16,$A$4:$A$5,0),MATCH(C$8,$B$3:$H$3,0))))

You may use any of the above, depending on the data structure. Option 1 is the simple one presuming the "A" & "B" versions are aligned in the order of B16 & B17, else you may use Option 2. More options, simple ones, can be used in case your data structure is otherwise.

Regards,

Amit Tandon

www.excelanytime.com

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2019-10-25T23:50:54+00:00

Hi,

In cell C15, enter this array formula (Ctrl+Shift+Enter) and copy to the right

=SUMPRODUCT(MMULT(1*($B$9:$B$13&C$9:C$13=TRANSPOSE($A$4:$A$5&B$4:B$5)),{1;1}))

In cell C16, enter this array formula and copy to the right/down

=SUMPRODUCT(MMULT(1*($B$9:$B$13&C$9:C$13=TRANSPOSE($A$4:$A$5&B$4:B$5)),{1;1})*($B$9:$B$13=$A16))

Theoretically, the 15th row can also be a summation of the rows 16 and 17

Hope this helps.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2019-10-25T05:29:39+00:00

Enter below formula in cell K9 and copy down:

=SUMPRODUCT(--(INDEX(B$4:H$5,INDEX(MATCH(B9,A$4:A$5,0),),)=C9:I9))

The matching values are in red. Each name's version (A or B) is matched with the version in the Table 1 and then each of the Q1 to Q7 are compared. Some results are in variance to what you have mentioned. In case this is your requirement and if not then please explain how you are arriving at your results.

Regards,

Amit Tandon

www.excelanytime.com

Was this answer helpful?

1 person found this answer helpful.
0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-10-24T23:18:51+00:00

    Hi total1

    Download from the link below a file with the answer as per your requirements.

    https://www.dropbox.com/s/hw8gvdlax4cimwc/QAnswers.xlsx?dl=0

    This s my approach to find a solution to your question.

    Do let me know if you need more help

    Was this answer helpful?

    0 comments