Conditional Min and Max in Excel 2010

Question

I would like to find Min and Max of Quantity (Column 2) based on Type (coloumn 1), is it possible to have this done?

I have tried this but the result was unexpected Similar question

Answer 1

Assuming your data above is in A2:B13, this works:

=MAX(IF(A2:A13="A",1,0)*(B2:B13))
=MAX(IF(A2:A13="B",1,0)*(B2:B13))
=MAX(IF(A2:A13="C",1,0)*(B2:B13))

You have to press ctrl+shft+Enter when you enter the formula into a cell. This finds all rows with the A, B, or C, and multiplies 1 with the value next to it if the letter matches your formula, and 0 if it doesn't match. Then you take the MAX() of the values.

<<< Edit >>>

As @GSerg suggested, you can also do it with these formulas, if you press ctrl+shft+Enter when entering them into each cell:

=MAX(IF(A:A="A",B:B))
=MAX(IF(A:A="B",B:B))
=MAX(IF(A:A="C",B:B))

A much more elegant way of doing it!