Scoring a test - any way to do this without VBA?

[JD]

I have a spreadsheet like this:

Key | A | C | D | C | B| A | E |
Name 1 | A | C | D | B | B | A | E |
Name 2 | A | B | D | C | B | A | A |
Name 3 | B | C | A | B | B | C | A |

Going across in columns B - H (in this example) are each person's answers.

The first row is the "key" to the test, i.e. the correct answers.

I would like to calculate the number of correct answers in the last column
for each person.

I know this will work: =IF(B2=$B$1,1,0)+IF(C2=$C$1,1,0)+IF(D2=$D$1,1,)+.....

This will compare each cell (answer) to the value in row 1 (key), return 1
if equal, thereby totalling up the correct values. Works fine.

But I was looking to see if there might be an even better way to do it,
where I would not have to repeat the IF() for every column - each test may
have a varying number (sometimes large, as many as 100) of answers, so it
could get rather tedious and error prone to type in that long formula. Is
there a way I can do the above, but just specify a range instead of
repeating each comparison?

I'm sure I could do this with VBA, but I am wondering if there is a more
concise way.

 

[Norman Bates]

Cell B5 contains "Key"
This is an array formula. Fill it down.
=SUM(IF($C$5:$I$5=C6:I6,1,0))
'__________________________



in message
I have a spreadsheet like this:
Key | A | C | D | C | B| A | E |
Name 1 | A | C | D | B | B | A | E |
Name 2 | A | B | D | C | B | A | A |
Name 3 | B | C | A | B | B | C | A |

Going across in columns B - H (in this example) are each person's answers.
The first row is the "key" to the test, i.e. the correct answers.
I would like to calculate the number of correct answers in the last column
for each person.
I know this will work: =IF(B2=$B$1,1,0)+IF(C2=$C$1,1,0)+IF(D2=$D$1,1,)+.....
This will compare each cell (answer) to the value in row 1 (key), return 1
if equal, thereby totalling up the correct values. Works fine.

But I was looking to see if there might be an even better way to do it,
where I would not have to repeat the IF() for every column - each test may
have a varying number (sometimes large, as many as 100) of answers, so it
could get rather tedious and error prone to type in that long formula. Is
there a way I can do the above, but just specify a range instead of
repeating each comparison?
I'm sure I could do this with VBA, but I am wondering if there is a more
concise way.

 

[Rick Rothstein \(MVP - VB\)]

Put this in the second row of whatever column you are using for your scores
and copy down...

=SUMPRODUCT((B2:L2=$B$1:$L$1)*(B2:L2<>""))

If you need to go past Column H, change the 3 H's to whatever will be the
last column to hold a score.

Rick

 

[Rick Rothstein \(MVP - VB\)]

The formula was supposed to be...

=SUMPRODUCT((B2:H2=$B$1:$H$1)*(B2:H2<>""))

where the Column H that I referred to in my write up is actually used in the
formula. In any event, just set the right-most column reference to the
maximum column that will contain scores.

Rick

 

[JD]

I got the other method using SUMPRODUCT() to work. I did not get your
method to work.

The "key" is in the first row, cells $B1:$H1

The answers are in B2:H2, B3:H3, etc.

Changing your formula to =SUM(IF($B$1:$H$1=B2:H2,1,0)) gives #VALUE!.

 

[JD]

Thanks, that works great!

The formula was supposed to be...

=SUMPRODUCT((B2:H2=$B$1:$H$1)*(B2:H2<>""))

where the Column H that I referred to in my write up is actually used in
the formula. In any event, just set the right-most column reference to the
maximum column that will contain scores.

Rick