MAX IF array with multiple conditions

[leonp]

Hello,

Can someone help me understand why my formula isn’tworking? I found online how to returnthe maximum value if two criteria are met yet I can’t figure out why it isn’tworking.

The formula is as follows…..

MAX(IF('Data'!$A:$A='Result'!B2,IF('Data'!$B:$B='Result'!B3,'Data'!$C:$C)))

There’s a lot of data so I can’t really attach the file butto summarise:

Data column A is a machine number (e.g. machine 1, machine2, machine 3), Result B2 is a specific machine (e.g. machine 2)
Data column B is a product (e.g. product 1, product 2,product 3), Result B3 is a specific product (e.g. product 3)
Data column C is a time of production (e.g. 07:00, 08:15,10:08)

What I want the formula to return is the latest time a productwas completed on a given machine.

Hope that makes sense.

Thanks!

 

[steve the fish]

Re: MAX IF array with multiple conditions help!

Did you enter it CTRL-SHIFT-ENTER? Other things to note are you will have a very slow formula if you use full column references for these array formulas and there is a big difference between machine2 and machine 2 with a space so make sure you are correct with your naming.

 

[Weazel]

Re: MAX IF array with multiple conditions help!

the formula you have is an array and would need to entered with control shift enter

you could try something like....

=AGGREGATE(14,6,Result!C:C/((Result!A:A=B3)*(Result!B:B=B2)),1)

which is just committed with enter

 

[mikerickson]

Re: MAX IF array with multiple conditions help!

I'm a little suspicious of the relative addressing.
It appears that Result page is arranged transposed from the Data page, in that you are testing Data!A:A against Result!B2 and Data!B:B against Result!B3

And if you drag the formula down one cell, the formula will adjust to compare B3 against Data!A:A