Finding MAX value with multiple conditions

[sharky12345]

I need a formula that will find the highest numerical value in the range B2:B5000 but only if the following criteria is met;

The value in column C must be the same as cell A3 and the value in column B must be the same as cell A1.

Can anyone assist me please?

 

[AlanY]

confirm with shift-control-enter

=MAX(IF($C$2:$C$5000=$A$3,IF($B$2:$B$5000=$A$1,$B$2:$B$5000)))

 

[sharky12345]

Alany,

Thanks - I have tried this;

LastRow = Sheet18.Cells(Rows.Count, "A").End(xlUp).Row
Range("C3:C" & LastRow).FormulaArray = "=MAX(IF(Installations!$C$2:$C$5000=A3,IF(Installations!$A$2:$A$5000=$A$1,Installations!$B$2:$B$5000)))"

The problem is that it is not copying down and it is keeping the 'A3' part for every row if you understand what I mean.

Is there a way around it?

 

[luke_forster]

I find that it is actually easier to us a =sumproduct formula to get conditional maxes than it is to use the array formula that AlanY mentioned. That said it is hard to give an example that I think will solve your problem as the max would always just be the value in cell A1 based on the above.