Count how many matches in the same row across 3 columns

Question

I hope someone can help, I am looking for a formula solution to the following problem if possible.

I have a column of people's names, and for each of those people I have 3 columns of data from 3 different sources - I need to determine how many times the data in those 3 columns matches for each person. After extensive Googling, I could only find solutions where the result is summarised in a cell derived from a COUNTIF range, however I need the results summarised in the same row for each person.

For example: "Dave" is in cell A2, his results were: column B2 = FAIL, C2 = PASS and D2 = PASS - so in this instance we have 2 matches as there were 2 passes. "Sue" is in cell A3, her results were: column B3 = FAIL, C3 = FAIL and D3 = FAIL - so in this instance we have 3 matches as there were 3 Fails. "Colin" is in cell A4, his results were: column B4 = TBA, C4 = FAIL and D4 = PASS- so in this instance we have 0 matches as none of the results match.

Ideally, I would like the number of matches listed down in column E for each individual person, so Dave's matching results would be cell E2, Sue's would be in E3 and Dave's in E4.

Many thanks in advance for your help.

Kindest regards,

TE

What are you counting? Fail values or pass values? Why Dave got 2 (2 pass) but Sue got 3 (0 pass)? — Foxfire And Burns And Burns, Nov 26 '20 at 12:56
I'm essentially checking consistancy of the results, not the results themselves. — TheEndUK, Nov 26 '20 at 13:16

JvdV · Accepted Answer · 2020-11-26T14:23:39.583

4

You could try:

Formula in E1:

=INDEX({0,2,3},MAX(COUNTIF(B1:D1,B1:D1)))

A bit of explaination for those who are curious:

COUNTIF(B1:D1,B1:D1) - Will result in an array of three values (1 per column), on how often these values appear in the three cells.
MAX() - Get's the max value from the previous array.
INDEX({0,2,3}) - Since the outcome of MAX() can only be 1-3 we can feed this as the row parameter into an INDEX() function. This will then result in either 0, 2 or 3.

A little less verbose and possibly more explicit would be:

=MIN(((B1=C1)+(C1=D1)+(B1=D1))*2,3)

With this last formula we use the fact that TRUE and FALSE are the equivalent of 1 and 0, and therefor we can add multiple boolean values. With some math we can then get our wanted result.

edited Nov 26 '20 at 14:23

answered Nov 26 '20 at 13:09

JvdV

70,606
8
39
70

1

OMG, what kind of sorcery is this? Could you please explain the logic behind this? I've tested and it works perfectly, but I'm trying to understand how (and why lol) you developed this – Foxfire And Burns And Burns Nov 26 '20 at 13:12
1

@FoxfireAndBurnsAndBurns, I have edited the answer including some explaination and another possible formula. Hope it helps. – JvdV Nov 26 '20 at 14:29
1

Bro, you rock. Thanks for adding the explanation. It makes thinks clear. Thanks for sharing – Foxfire And Burns And Burns Nov 27 '20 at 07:28

score 2 · Answer 2 · answered Nov 26 '20 at 12:56

You can use IF() MAX() and COUNTIF combined.
In E2:

=IF(MAX(COUNTIF(B2:D2,B2),COUNTIF(B2:D2,C2),COUNTIF(B2:D2,D2))=1,0,MAX(COUNTIF(B2:D2,B2),COUNTIF(B2:D2,C2),COUNTIF(B2:D2,D2)))

So it does three separate countif to see how many is "duplicated" from each cell.
Takes the max of them and compare against 1, if that is true then return 0, else return what the max value was.

kirkg13 · Answer 3 · 2020-11-26T13:52:36.507

1

Would something like this work, in column E?

=if(countif(B2:D2,B2)=3,3,if(countif(B2:D2,B2)=2,2,if(countif(B2:D2,C2)=2,2,0)))

Let me know if that works for you.

edited Nov 26 '20 at 13:52

answered Nov 26 '20 at 13:01

kirkg13

2,955
1
8
12

This woould not work with Sue. It would return 0 and OP said Sue's matches is 3 – Foxfire And Burns And Burns Nov 26 '20 at 13:14
In my testing, this does return 3 for Sue. See image added to answer above. – kirkg13 Nov 26 '20 at 13:52

Count how many matches in the same row across 3 columns

3 Answers3