0

I have a table with students' answers to 20 math problems like this:

     A      |  B  |  C  |  D  |  E  |...
------------+-----+-----+-----+-----+...
problem no  |  1  |  2  |  3  |  4  |...
------------+-----+-----+-----+-----+...
right answer|  3  |  2  |  A  |  15 |...
------------+-----+-----+-----+-----+...
student1    |  3  |  4  |  A  |  12 |...
student2    |  2  |  2  |  C  |  15 |...
student3    |  3  |  2  |  A  |  13 |...

Now a need a column that counts the 'right' answers for each student.

I can do it this so: 

=(IF(D$3=D5;1;0))+(IF(E$3=E5;1;0))+(IF(F$3=F5;1;0))+...
...but it's not the nicest way :)
zuups
  • 1,140
  • 1
  • 11
  • 17
  • You could condense this a bit in Excel 2007+ using `=countifs()` it would look something like `=countifs(D$3,D4,E$3,E5,F$3,F4,...)` Not sure if LibreOffice has a similar function. Come to think of it, you could use some boolean logic here and do `=(D$3=D5)+(E$3=E5)+(F$3=F5)+...` Each item will either return 0 or 1 and add to the total. – JNevill Oct 27 '14 at 14:00

1 Answers1

1

This is a typical use case for SUMPRODUCT:

  A             B  C  D  E   F  G
1 problem no    1  2  3  4      
2 right answer  3  2  A  15     right answers per student
3 student1      3  4  A  12     2
4 student2      2  2  C  15     2
5 student3      3  2  A  13     3

Formula in G3:
=SUMPRODUCT($B$2:$E$2=$B3:$E3)

If there are more problem numbers, then the column letters in $E$2 and $E3 have to be increased.

How it works: SUMPRODUCT takes its inner functions as array formulas. So the $B$2:$E$2=$B3:$E3 becomes a matrix of {TRUE, FALSE, TRUE, FALSE} depending of if $B$2=$B3, $C$2=$C3, $D$2=$D3, $E$2=$E3.

In Libreoffice or Openoffice TRUE is 1 and FALSE is 0. So the SUMPRODUCT sums all TRUEs.

In Excel you have to get the boolean values in numeric context first. So the Formula in Excel will be =SUMPRODUCT(($B$2:$E$2=$B3:$E3)*1).

The formula in Row 3 then can be filled down for all student rows. The $ before the row number 2 ensures that thereby the row of the right answers not changes.

Greetings

Axel

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Works well, thank you! There are some extra $ signs that can be removed e g =SUMPRODUCT(B$2:E$2=B3:E3) – zuups Oct 27 '14 at 20:52