For each row, check if answer is correct and unique

[jokr1]

I have columns A-D, where A contains the correct answer to each question.
person1, person2 and person3 has the following answers. As you see person1 has got all the correct answers. But how can I count how many times person1 was the only one with the correct answer with a formula?

 

[kweaver]

Maybe this?

=SUMPRODUCT((A2:A6=B2:B6)*(A2:A6<>C2:C6)*(A2:A6<>D2:D6))

 

[anthonyexcel]

Another way!

=SUMPRODUCT(--(MMULT(--(B2:D6=A2:A6),{1;1;1})=1))

 

[Radoslaw Poprawski]

or use XOR function

 

[Fluff]

or use XOR function

Please show how to do it, rather than making vague (and unhelpful) comments.

 

[Radoslaw Poprawski]

like this for instance

Book3
	A	B	C	D	E	F	G	H
1	correct answer	p1	p2	p3		step1: XOR check	step2 convert Trues / false to 0,1	step3: sum it up
2	1	1	2	3		TRUE	1	2
3	3	3	5	3		FALSE	0
4	2	2	2	6		FALSE	0
5	8	8	4	3		TRUE	1
6	4	4	1	4		FALSE	0
7
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=XOR(A2=B2,A2=C2,A2=D2)
G2:G6	G2	=--XOR(A2=B2,A2=C2,A2=D2)
H2	H2	=SUM(G2:G6)

 

[billszysz]

Excel 365

=SUM(--(MMULT((B2:D6=A2:A6)*(A2:A6=B2:B6),SEQUENCE(COLUMNS(B2:D6),,1,0))=1))

or (easier to change ranges)

=LET(correct, A2:A6, all, B2:D6, person, B2:B6,
SUM(--(MMULT((all=correct)*(correct=person),SEQUENCE(COLUMNS(all),,1,0))=1))
)