I am trying to work out how I can achieve a MAX() type selection on a range of cells, but limit the cells over which I perform the MAX() according to the value of the adjacent cell.
As a new member, I can’t attach a jpg of what I mean, unfortunately, but here is a text description:-
Cells A1:A9 contain a random mix of “Apples”, “Oranges” and “Bananas”. Each cell in B1:B9 contain a number of fruit of the type contained in the adjacent “A” cell.
What I want is to put in Cell D1 (for example) the MAX() of the values in B1:B9, but only for (say) “Apples”.
If I do MAX(B1:B9), I (rightly) get the simple highest value, regardless fo the type of fruit. What I need is some way to filter on the fruit and MAX() only matching cells.
Is there a reasonably simple way to do this without modifying (e.g. by sorting) the source data?