# 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?

Commented:
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

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

Commented:
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

Author Commented:
That is it exactly. Thank you for your assistance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.