find highest No. in a column but inclusion is based on data in other cells

Hello, Thank you for your time.

I’m keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes it’s only 50.

I’ve used MAX to find the highest score in Column D of a spreadsheet and it did what I required.

However I now wish to find the highest number in Column D - but only include rows if Column C = P and column E =100.


Col B Col C Col D Col E Col F
date Location score out of % hit

25/10/2008 A 62 100 62
08/11/2008 P 35 100 35
22/11/2008 A 42 100 42
06/12/2008 P 66 100 66
20/12/2008 A 60 100 60
03/01/2009 P 29 50 58
17/01/2009 A 51 100 51
31/01/2009 P 40 100 40


In other words I want to find the highest score for location P but only if that week it was out of 100 shots

It would return 66.

Then I can do the same formula for location A and it would return 62.


I can’t see how to do this and have searched the forum to no avail. It doesn’t look like I can just use the MAX anymore and I’ve tried incorporating that into a (nested) IF but unsuccessfully. I’d appreciate some guidance please.

Thanks
Pete

Pete, you would need to use an Array, ie

=MAX(($C$2:$C$100="P")*($E$2:$E$100=100)*($D$2:$D$100))
confirmed with CTRL + SHIFT + ENTER

or alternatively this INDEX but it won't perform much better than the above, worse so on large data sets - but does not require CSE.

=MAX(INDEX(($C$2:$C$100="P")*($E$2:$E$100=100)*($D$2:$D$100),0))

Have you thought of using a Pivot Table ? This could give you all of your results in a neat matrix.... eg push the table into a Pivot, set Location as Row Field, set Out Of as Column Field and score as Data Field set to MAX.