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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A
(Person)[/TD]
[TD]Column B
(Defects Found)[/TD]
[TD]Column C
(Reviews Performed)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]49[/TD]
[/TR]
</tbody>[/TABLE]

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.