Using multiple criteria to return max date

=MAX(IF(AND(B\$1:B\$57=R2,N\$1:N\$57=1,L\$1:L\$57),""))

I would like to use criteria 1 B\$1:B\$57=R2, and criteria 2 N\$1:N\$57=1 to return greater date but only if both citeria match.  Otherwise, leave blank.  I  input the above formula as an array but it did not return what I thought it would.  if n = .50 then it gives the greater date of the remaining cells.  but I would like it to only return the greater date if  B and R match and all n cells pertaining where they match have a 1.

Any suggestions?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You can't use AND in formulas of this type because AND returns a single result rather than an array - either use * to simulate AND or use multiple IFs, i.e. either

=MAX(IF((B\$1:B\$57=R2)*(N\$1:N\$57=1),L\$1:L\$57))

....or...

=MAX(IF(B\$1:B\$57=R2,IF(N\$1:N\$57=1,L\$1:L\$57)))

both confirmed with CTRL+SHIFT+ENTER

regards, barry
0
Mechanical EngineerCommented:
If you use Excel 2010 or later, the AGGREGATE function became available. You do not need to array-enter the following:
=AGGREGATE(14,6,L\$1:L\$57/((B\$1:B\$57=R2)*(N\$1:N\$57=1)),1)
0
Author Commented:
Ok sorry.   I am new to max if and so tried on my own first

It returns the max date as needed  but it does not make it blank if one of the three cells is .5  am trying to get max date if all cells  n:n for product equal 1 but if there are three cells for apple but two are 1 and one is .5 then no date should return but if all three are 1 then return max date for apple
Thank you for your patience and time
0
Mechanical EngineerCommented:
From your last description, I assume the following:
1. Dates are in column L
2. Column B contains a mix of values. Lets suppose that cell R2 contains "apple" and that "apple appears only in B1:B5.
3. Check N1:N5 and make sure that the value in all those cells is 1
4.  If the column N check is passed, then return the max date from L1:L5
5.  If even one of those cells in column N has a value not equal to 1, then return an empty string (looks like a blank)
6.  In the actual problem, the cells in column B that contain "apple" may not be contiguous--but similar logic should prevail.

The following array-entered formula finds the max date according to the above logic:
=IF(COUNTIFS(B\$1:B\$57,R2,N\$1:N\$57,"<>1")>0,"",IFERROR(1/(1/MAX(IF(B\$1:B\$57=R2,L\$1:L\$57))),""))

I've attached a sample workbook that shows the above formula so you can test it.

If I am wrong in my assumptions as stated above, could you please clarify (and better yet, post a sample workbook with the expected result of the formula)?
MaxDateQ28265467x.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
That is it exactly. Thank you for your assistance
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.