Sep 20 2021 08:53 AM
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
Sep 20 2021 09:06 AM
SolutionSee the attached sample workbook.
Sep 20 2021 09:13 AM
Sep 20 2021 09:14 AM
I do not work on Mac, but if you have no MAXIFS when
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) )
Sep 20 2021 09:06 AM
Solution