Maximum value for a sub-set of records

%3CLINGO-SUB%20id%3D%22lingo-sub-2767645%22%20slang%3D%22en-US%22%3EMaximum%20value%20for%20a%20sub-set%20of%20records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767645%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20am%20trying%20to%20find%20the%20maximum%20value%20for%20a%20sub-set%20of%20records%20(what%20I%20really%20need%20is%20a%20MAXIF%20function!).%20%26nbsp%3BFor%20example%20see%20table%20below%3A%20the%20maximum%20score%20for%20Apple%20is%205%20(Simon)%20and%20the%20maximum%20for%20pear%20is%2016%20(Louise)%3C%2FP%3E%3CP%3EName%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Fruit%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Score%3C%2FP%3E%3CP%3EAnn%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BApple%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3C%2FP%3E%3CP%3EPeter%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPear%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%203%3C%2FP%3E%3CP%3EBen%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BApple%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FP%3E%3CP%3ELouise%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPear%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B16%3C%2FP%3E%3CP%3ESimon%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BApple%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20gratefully%20received.%20I%20am%20using%20Excel%20for%20Mac%20v%2016.52%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2767645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi I am trying to find the maximum value for a sub-set of records (what I really need is a MAXIF function!).  For example see table below: the maximum score for Apple is 5 (Simon) and the maximum for pear is 16 (Louise)

Name                   Fruit                     Score

Ann                      Apple                    2

Peter                    Pear                       3

Ben                      Apple                    1

Louise                  Pear                      16

Simon                  Apple                    5

 

Any ideas gratefully received. I am using Excel for Mac v 16.52

 

3 Replies

@JEM80 

See the attached sample workbook.

Perfect thanks - I couldn't find this formula in the help

@JEM80 

I do not work on Mac, but if you have no MAXIFS when

image.png

with

=AGGREGATE(14,6,1/($C$3:$C$7=$G3)*$E$3:$E$7,1)

=INDEX($B$3:$B$7, AGGREGATE(14,6,1/($C$3:$C$7=$G3)*(ROW($B$3:$B$7)-ROW($B$2)),1) )