Need Help with a Formula

[jmsjr]

I first created a spreadsheet with answers to a series of questions. Let's
say there are 10 questions and I want a final column to tabulate how many
questions are answered correctly. The questions could be done with
true/false or multiple
choice, whichever is easiest. What formula(s) should I use to do this? I
am thinking that the final column would display a number that is the sum of
the total number of right answers.

 

[T. Valko]

You would need to have an answer key and then you'd just compare the users
answers to this key.

Suppose the 10 questions are in the range A1:A10 and the user enters their
answers in the range B1:B10.

In some range (out of sight of the users) you'd list the correct answers for
each question. Let's assume you enter the correct answers in the range
AA1:AA10.

Then you can use a formula like this to get the number of correct answers:

=SUMPRODUCT(--(B1:B10=AA1:AA10))

 

[Gord Dibben]

Easy enough to sum up correct answers with a formula like

=COUNTIF(A1:A10,"Y") which counts the number of questions with the answer Y

Sounds like you want something a little more complex which can probably be done.

But Excel needs some guidance on which is correct or incorrect answer.

You know which, so now just a matter of letting Excel know.


Gord Dibben MS Excel MVP