Find MAX(Column C) where Row in Column B = 1

[Twatwood]

I have a need to find out which Person has the highest number of reviews performed that found 1 defect

Sample data:

	Column A (Person)	Column B (Defects Found)	Column C (Reviews Performed)
1	1	0	65
2	1	1	42
3	1	2	85
4	1	4	54
5	2	0	25
6	2	1	74
7	2	3	45
8	3	1	108
9	3	3	15
10	3	5	85
11	4	1	49

<tbody>
</tbody>

In the above, person 2 did 74 reviews that found 1 defect
Once I get the 74 review count, I plan to do an INDEX/MATCH to identify that it was Person 2

To get the 74 I tried this, but it didn't work

{=IF(B1:B11=1,MAX(C1:C11),0)}

Any suggestions on how to fix this?

 

[Tom Urtis]

Based on your expressed logic, why would you not have a greater expectation for returning Person 3 than you indicated you have for Person 2, because Person 3 had 108 reviews performed with only 1 defect found.

 

[jtakw]

Hi,

The correct result should be 108 from Person 3, also pointed out by Tom, try it this way, CSE formula cause I don't have MAXIF:

Book1
	A	B	C	D	E
1	(Person)	(Defects Found)	(Reviews Performed)
2	1	0	65		108
3	1	1	42
4	1	2	85
5	1	4	54
6	2	0	25
7	2	1	74
8	2	3	45
9	3	1	108
10	3	3	15
11	3	5	85
12	4	1	49
Sheet85

Cell Formulas
Range	Formula
E2	{=MAX(IF(B2:B12=1,C2:C12))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Twatwood]

Yes, the correct answer is 108 (my bad)

And, that works.

Thanks mate.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	(Person)	(Defects Found)	(Reviews Performed)		1
2	1	0	65		108
3	1	1	42		who?
4	1	2	85		3
5	1	4	54		8
6	2	0	25
7	2	1	74
8	2	3	45
9	3	1	108
10	3	3	15
11	3	5	85
12	4	1	49
13	5	3	108
14	8	1	108
Sheet1

In E2 just enter:

=MAXIFS($C$2:$C$14,$B$2:$B$14,E$1)

If there is no MAXIFS on your system, control+shift+enter, not just enter:

=MAX(IF($B$2:$B$14=E$1,$C$2:$C$14))

In E4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(E$4:E4)>COUNTIFS($B$2:$B$14,E$1,$C$2:$C$14,E$2),"",INDEX($A$2:$A$14,SMALL(IF($B$2:$B$14=E$1,IF($C$2:$C$14=E$2,ROW($A$2:$A$14)-ROW($A$2)+1)),ROWS(E$4:E4))))

 

[jtakw]

Yes, the correct answer is 108 (my bad)And, that works. Thanks mate.

You're welcome.