6

enter image description here

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

Community
  • 1
  • 1
NCC
  • 819
  • 12
  • 25
  • 43

6 Answers6

7

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!

James L.
  • 9,384
  • 5
  • 38
  • 77
  • 1
    Why the wrong/harmful multiplication? See [the question the OP linked to](http://stackoverflow.com/questions/10746792/excel-find-min-max-date-in-a-range-if-it-matches-criteria-of-other-columns) for the correct way. – GSerg Jul 19 '12 at 22:42
  • @GSerg - thanks for pointing that out. I hadn't done it the other way before. Much better! (+1) – James L. Jul 19 '12 at 22:49
  • 4
    For the record (and as I noted in the linked question) this method is prohibitively slow when working with large numbers of records. A pivot table is more efficient in the general case. – andy holaday Jul 20 '12 at 00:15
1

Anything other than a PivotTable (as suggested by @andy holaday) seems sheer masochism (unless for a very good but presumably very peculiar reason):

SO11570223 example

Note that for illustration I doubled the OP's data quantities for B and these again for C.

pnuts
  • 58,317
  • 11
  • 87
  • 139
0

This works without ctrl+shift+enter, but your table should be sorted by a TYPE column.

Let's assume that your table is placed in B3:C15, then in A4 put

=IF(B4=B3;A3;A3+1)

in E4 - "1", in E5 - "2" , in E6 - "3", in F4 put:

=MAX(INDIRECT("C" & MATCH(E4;$A$1:$A$17;0) & ":C" & MATCH(E4;$A$1:$A$17;1) ))

and copy it to F5 and F6

in G4 put:

=MIN(INDIRECT("C" & MATCH(E4;$A$1:$A$17;0) & ":C" & MATCH(E4;$A$1:$A$17;1) ))

and copy it to G5 and G6

enter image description here

MATCH function handles strings incorrectly, so I had to number TYPEs, you can use VLOOKUP to change numbers in column E to a strings

In my table I used this solution to find strings with maximum values this way:

enter image description here

plavozont
  • 807
  • 9
  • 17
0

A rather sneaky but simple way to do it is
1. create a new column that concatenates both Type and Qty and Call it "TypeQty" or whatever you'd like
2. Sort (Ascending) the new table ie Type,Qty and TypeQty all together but sort on the TypeQty column.
3. apply a formulat that checks if the type in the row above is the same as the current row. if not then mark that row because its the last of the current type.

you will end up with the "mark" only the max rows for each type. See screenshots

enter image description here

KuriaNdungu
  • 623
  • 8
  • 20
0

@JamesL's solution always resulted in zero for me when attempting

=MIN(IF(A2:A13="A",1,0)*(B2:B13))

If I set it up with an arbitrarily large number for the false result, then it worked

=MIN(IF(A2:A13="A",1,99999)*(B2:B13))

However, @GSerg's elegant solution also works for the min:

=MIN(IF(A:A="A",B:B))

MichaelG
  • 1
-2

I would suggest to use =large(if(...=...;...);k) to solve that problem.

NathanOliver
  • 171,901
  • 28
  • 288
  • 402
Iltis
  • 1