MAX with multiple conditions

[jakemelon]

Hi!

I need below conditions met and then need the MAX value from the qry_live_pipe_Crosstab'!$C$2:$C$999 range. For some reason this is not working help!


{=MAX(IF((Headcount!$A$2:$A$999=E2)*(Headcount!$D$2:$D$999="Yes")*('C:\[livepipe.xls]qry_live_pipe_Crosstab'!A2:A999=E2),'C:\[livepipe.xls]qry_live_pipe_Crosstab'!$C$2:$C$999))}

thanks

 

[Aladin Akyurek]

OK, not working, but what happens?

 

[jakemelon]

The max value it pulls is always wrong.

 

[Aladin Akyurek]

The max value it pulls is always wrong.

Not sure this bit of info is better than just "not working"...

Perhaps:

{=MAX(IF((Headcount!$A$2:$A$999=E2)*(Headcount!$D$2:$D$999="Yes")*('C:\[livepipe.xls]qry_live_pipe_Crosstab'!A2:A999=E2)*('C:\[livepipe.xls]qry_live_pipe_Crosstab'!$C$2:$C$999),'C:\[livepipe.xls]qry_live_pipe_Crosstab'!$C$2:$C$999))}

 

[jakemelon]

I think the issue may be that one of ranges is on a seperate workbook. The moment I move the data to the same workbook it starts working. Why would that be an issue?

thanks

 

[Aladin Akyurek]

I think the issue may be that one of ranges is on a seperate workbook. The moment I move the data to the same workbook it starts working. Why would that be an issue?

thanks

Jake,

Just checked your hunch. It simply does not hold. The following formula, similar to yours, works as intended:

=MAX(IF(($D$2:$D$6="a")*($E$2:$E$6="w")*([livepipe.xls]qry_live_pipe_Crosstab!$F$2:$F$6=40),[livepipe.xls]qry_live_pipe_Crosstab!$G$2:$G$6))

This also works when livepipe.xls is closed.