Multiple Criteria for Max

• I am trying to find the maximum value in 4th column when 3 other criteria is met in 3 other columns. Here is my equation below...

=MAX(ifs(\$C\$1:\$C\$289774=M1)*(\$D\$1:\$D\$289774=N1)*(\$E\$1:\$E\$289774=O1),\$I\$1:\$I\$289774)

The maximum value I am searching for is in "column I", but I need the criteria in column c, column d, and column e already met. The maximum value for all of these criteria would show up in column P
[TABLE="width: 1024"]

[tr]

[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="width: 64, align: center"][/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl63, width: 64"]M[/TD]
[TD="class: xl63, width: 64"]N[/TD]
[TD="class: xl64, width: 64"]O[/TD]
[TD="width: 64, align: center"]P[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]726430[/TD]
[TD="class: xl65, width: 64"]14920[/TD]
[TD="class: xl65, width: 64"]1933[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[TD="class: xl65, width: 64"]22[/TD]
[TD="class: xl65, width: 64"]17[/TD]
[TD="class: xl65, width: 64"]0[/TD]
[TD="class: xl65, width: 64"]85[/TD]
[TD="class: xl65, width: 64"]66[/TD]
[TD="class: xl65, width: 64"]1700[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl63, width: 64"]1933[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl64, width: 64"]22[/TD]
[TD="width: 64, align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]52[/TD]
[TD="class: xl65"]51[/TD]
[TD="class: xl65"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1934[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]78[/TD]
[TD="class: xl65"]55[/TD]
[TD="class: xl65"]1700[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1935[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]60[/TD]
[TD="class: xl65"]55[/TD]
[TD="class: xl65"]1300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1936[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]86[/TD]
[TD="class: xl65"]69[/TD]
[TD="class: xl65"]2100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1937[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]65[/TD]
[TD="class: xl65"]52[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1938[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]64[/TD]
[TD="class: xl65"]58[/TD]
[TD="class: xl65"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1939[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]70[/TD]
[TD="class: xl65"]59[/TD]
[TD="class: xl65"]1300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1940[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]79[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1941[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]83[/TD]
[TD="class: xl65"]65[/TD]
[TD="class: xl65"]1700[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1942[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]83[/TD]
[TD="class: xl65"]69[/TD]
[TD="class: xl65"]2100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1943[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1944[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]64[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1945[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]70[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]1300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1946[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[/TABLE]

Thanks in advance for the help!

Jeff

• Re: Multiple Criteria for Max

Thanks so much for the help!

• Re: Multiple Criteria for Max

This should be slighlty quicker:
=MAX(IF(\$C\$1:\$C\$289774=M1,IF(\$D\$1:\$D\$289774=N1,IF(\$E\$1:\$E\$289774,\$I\$1:\$I\$289774)))

Confirm Control+Shift+Enter

• Re: Multiple Criteria for Max

Actually did not time myself but there was a topic on MrExcel where some of the gurus recemmended to us IF() function instead of multiplication.

• Re: Multiple Criteria for Max

I do not have link but quoted Aladin Akyurek( I belive you know that name)
A function call means some overhead. So many thinks: the lesser the number of such calls the faster the calculation. But I opt for because I expect filtering to be faster than pairwise multiplications (+ INDEX).

Since you just have 3 IFs, I'd go for the first formula for reasons of efficiency and transparency.

• Re: Multiple Criteria for Max

No, not this one.
But I hope this clarifies some uncertainty

• Re: Multiple Criteria for Max

Quote from pike;681733

just doing a little more testing and it has half the evaluations in its processing

Glad to hear that.
So - is it time to change?

• Re: Multiple Criteria for Max

Discussion on going with other another sources "You noted in another thread how fast a ridiculously long megaformula actually calculated... number of calculations isn't always an indicator of speed... it's which functions are more efficient versus others...

The logic with the IF functions perhaps being better is that each IF check checks the logic, and only does the what_if_TRUE calculation if the result of the logical check is TRUE, and when the what_if_FALSE is a fixed result or another nested IF function, then it would be quicker to process each by skipping the ... whereas the multiplication of arrays, always calculates each of the conditions out all the time. "
So further testing shows the IF does calculates every value but is the IF arrayformula Boolean/binary sum quicker thanthe-non array multiplication .. Still testing

• Re: Multiple Criteria for Max

What kind of data do you test?

• Re: Multiple Criteria for Max

the speed test i use...http://msdn.microsoft.com/en-us/library/aa730921.aspx

index = 0.00163

and if array
=MAX(IF((\$C\$1:\$C\$14=M1)[COLOR="#FF0000"]*[/COLOR](\$D\$1:\$D\$14=N1)[COLOR="#FF0000"]*[/COLOR](\$E\$1:\$E\$14=O1),\$I\$1:\$I\$14))
or
=MAX(IF(\$C\$1:\$C\$14=M1,IF(\$D\$1:\$D\$14=N1,IF(\$E\$1:\$E\$14,\$I\$1:\$I\$14))))

0
.

0

0

1

5
3
bit of top gear suspence
If boolean logic faster at 0.00153

Thanks for the Tip Rob.. its the first array I've come across thats faster in calx time than standard formulas

• Re: Multiple Criteria for Max

but
=MAX(INDEX((\$C\$1:\$C\$1400=M1)*(\$D\$1:\$D\$1400=N1)*(\$E\$1:\$E\$1400=O1)*(\$I\$1:\$I\$1400),0))
a extra 1386 rows
0.00273
=MAX(IF(\$C\$1:\$C\$1400=M1,IF(\$D\$1:\$D\$1400=N1,IF(\$E\$1:\$E\$1400,\$I\$1:\$I\$1400))))
0.00292

so using a too many or large array formulas will slow calx's time down but small IF logic arrays could be quicker

• Re: Multiple Criteria for Max

I have not done any tests recently but sometimes when I run in the past I got the the split of seconds in test different everytime.
I keen to seen what woudl happen and 500000 rows.
I assume there is correlation as well to your PC(memory and processor) and the type of data.
Please share any other tests you going to do.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!