Max value based on multiple variables

**Kaziglu Bey** · 07-28-2009, 10:17 AM

Basically what I have is something like this:

Please Login or Register  to view this content.

Column A = Various part numbers
Column B = Quantities per each
Column C = Size of the container being used

What I am looking to do is this:

Please Login or Register  to view this content.

Column A = Various part numbers
Column B = Quantities per each
Column C = Size of the container being used
Column D = The max quantity per part number per each unique container size.

Will a formula complete this task or do I need to go the route of a macro?

**DonkeyOte** · 07-28-2009, 10:39 AM

You will need to use a MAX array, eg

D1: =MAX(IF(($A$1:$A$10=$A1)*($C$1:$C$10=$C1),$B$1:$B$10))
confirmed with CTRL + SHIFT + ENTER
copied down

When using Arrays be sure to keep range sizes to a minimum (pre XL2007 entire column references are not permitted - ie will generate NUM errors)

On an aside if you were able to sort your data by A, C and B (desc) you would make things quite a bit simpler...

**Palmetto** · 07-28-2009, 10:41 AM

You need to provide some correlation between quantity and container size, which you haven't given.

Idea:
Use a look up table that correlates container and quantity, then use a look up formula.

Anyway, based on your example for just the two sizes:
=if(C1< 2,100,150)

**Kaziglu Bey** · 07-28-2009, 11:15 AM

Originally Posted by DonkeyOte

You will need to use a MAX array, eg

D1: =MAX(IF(($A$1:$A$10=$A1)*($C$1:$C$10=$C1),$B$1:$B$10))
confirmed with CTRL + SHIFT + ENTER
copied down

When using Arrays be sure to keep range sizes to a minimum (pre XL2007 entire column references are not permitted - ie will generate NUM errors)

On an aside if you were able to sort your data by A, C and B (desc) you would make things quite a bit simpler...

Thanks, that's it and works perfect for the application I need it for.

**lucraft** · 06-28-2011, 06:11 AM

Sorry to open this one again, but is there something wrong with this

=MAX(IF(('Cab Level'!$C$3:$C$5152=$D3)*('Cab Level'!$H$3:$H$5152=$A3),'Cab Level'!$M$3:$M$5152))

It doesnt error, but returns blank cells for all

Any help would be greatly appreciated.

Thanks in advance.

**DonkeyOte** · 06-28-2011, 06:21 AM

It's an Array and requires confirmation using CTRL + SHIFT + ENTER rather than just Enter per a "normal" formula. If the Array has been set correctly the formula will appear encased within { } [note these can not be added manually]

If you have follow ups please create your own thread and add a link to to this thread if still relevant.