Difference between these array formulae

[millz]

Hi guys, I am not very well versed with formulae, and especially not for array formulae, would like to know the difference between these two examples.

Found some help through Googling, and made these up. Basically, to mimic SUMIFS/COUNTIFS formulae, except for MAX instead.

I am comparing 3 columns, and return a 4th column if all matched, then get the max of the returned value.

Formula 1:

Please Login or Register  to view this content.

Formula 2:

Please Login or Register  to view this content.

The first formula used the AND function to perform 3 conditions at once, but it doesn't work.
The second formula is a nested IF's, and it works fine.
To me, both formulae should be logically working the same, but why wouldn't the first one work? Could anyone please enlighten? Thanks!

[:) Sixthsense :)]

In Array formula's * replaces the AND() function


So your initial (Array) formula should be like this...

=MAX(IF(($N$2:$N$10000="AB")*($D$2:$D$10000=D2)*($E$2:$E$10000=E2),$O$2:$O$10000))


Your second formula will work faster than the first one. Because the nedsted If() will exit soon and will not compare all the conditions like the first one.

=MAX(IF($N$2:$N$10000="AB",IF($D$2:$D$10000=D2,IF($E$2:$E$10000=E2,$O$2:$O$10000))))


Also you can go for Sumproduct() approach to avoid Ctl+Shif+Enter keyboard entry

=SUMPRODUCT(MAX(($N$2:$N$10000="AB")*($D$2:$D$10000=D2)*($E$2:$E$10000=E2),$O$2:$O$10000))


If you are using Excel >=2010 then you can try =AGGREGATE() function which avoids Ctl+Shif+Enter keyboard entry


=AGGREGATE(14,6,($N$2:$N$10000="AB")*($D$2:$D$10000=D2)*($E$2:$E$10000=E2)*$O$2:$O$10000,1)

[FDibbins]

Im not sure the 1st array is permitted, but here is a 3rd version...
=MAX(IF(($N$2:$N$10000="AB")*($D$2:$D$10000=D2)*($E$2:$E$10000=E2),$O$2:$O$10000))
Also array ebtered

[millz]

In Array formula's * replaces the AND() function

That's nice to know. I probably read that somewhere else before but obviously it didn't get into my long-term memory.

Thank you, both of you, for the examples.