"Epinn" wrote in message
...

I made some modification to the formula and I got the correct result.

(1) I took out the extra closing bracket.

Actually, I made a msitake and omitted an early opening bracket. It should
havfe been

=MAX(IF(rng=cond1)*(rng2=cond2)*(rng3=cond3),rng4) )

as an arary formula, will now give the correct result


(2) I took out IF. This is based on what I have learned from SUMPRODUCT
and Boolean.


Not necessary (when we have correct syntax <G), but an alternative approach
that also works.


(3) I had to replace the comma with *. I don't understand why.
For SUMPRODUCT either * or comma would have worked in a case like this.
But for MAX and SUM I have to use * instead of comma to get the correct
result.
This is my discovery for today.


Because it ain't SUMPRODUCT. SP allows ranges delimited by comma and does
the implict product. MAX also allows ranges to also be delimited by comma,
but there is no implicit product, so it takes the makes of the resultant
array of the tests, and the final range, NOT the final range where the
resultant array is TRUE.


=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1: D10)) committed with
CSE.


Will be interesting to time this and see if it is quicker (I expect it to be
so).