Wrapping a 'MAX' Function around an IF Statement?

[jesz1987]

Hi,

I am trying to wrap a MAX function of some sort around some sort of IF statement. The same sort of thing as a SUMIF, but a MAXIF, if that makes sense?

So i have for instance one sheet with many different registrations on some registrations appear more than once. Another colounm on that sheet has a max speed. What i want to do is, a formula to look for a certain registration (going off another sheet), and then get the MAX speed, from the set of MAX speeds for that registration.

So if i had 5 lines for registration DV51 AZZ, it would have 5 max speeds. On the next sheet i want it to look up DV51 AZZ, and bring back the MAX number (speed) from the 5 max speeds.

I hope this makes sense! And i hope its possible??

Thanks for any help
i love this forum.
Jez

[NBVC]

something like:

=MAX(IF(A1:A10="x",B1:B10))

confirmed with CTRL+SHIFT+ENTER not just ENTER

where you are looking for "x" in A1:A10 and bringing max from all matches from B1:B10

[fortheloveofgod]

cant you just pivot it all and set ther field to max?

[jesz1987]

Thanks guys, the forumula works great and you fortheloveofgod i think may well just made it much easier i didnt think of this. thanks very much

[jesz1987]

Actually this isnt working. I know why i couldent do a pivot now, its because it needs to go into a pivot table eventually, but needs to go into a 'matrix' sheet which looks at many other different sheets before hand.So the pivot wont work unless i do a seperate table.

The forumula isnt working either. Its just bringing back '0'
Im going to attach my sheet to try and explain. Thanks

[jesz1987]

Example MAX.xlsx

I have uploaded my spreadsheet i have inserted a comment into it and what the outcome should be.
Any help would be great.
Cheers

[NBVC]

I got the same answer (76) with:

=MAX(IF(Masternaught!$B$2:$B$7629=Matrix!A2,Masternaught!$J$2:$J$7629))

confirmed with CTRL+SHIFT+ENTER not just ENTER

[jesz1987]

Thanks NBVC, works great. Your a star. Can i ask, what is the difference with this CTRL+SHIFT ENTER, instead of just ENTER?

[fortheloveofgod]

it makes it an array

[NBVC]

Here is more on Array Formulas