Index/Match find Max Date UNLESS matching range contains Nulll (blank)..

[Galena1]

Ok, I have a table with 3 columns: Parent ID, Record ID, and Approved On Date.

I want to find the MAX Date for Parent ID. For example 201999 MAX date is 3-Jun-2014.
However, if the Record ID contains blanks (nulls) in the Approved On field, then I want formula to return "", or blank.
Example: Parent ID 209264 has eight matching records. The MAX date however would be reported as null or blank, because
three of the Approved On dates are null or blank.

This is what I am using now, but it doesn't take into account the blank date condition.

=MAX(INDEX($L$10:$M$10240,MATCH(T10,$K$10:$K1000,2),2))

Thanks in advance for any help.

Parent ID 199581 201999 201999 201999 202496 202741 202741 204232 204599 204599 206745 206745 206994 207219 207219 209264 209264 209264 209264 209264 209264 209264 209264 <TBODY> </TBODY>

Record ID 205332 206951 206959 207556 213546 212785 212788 213022 209578 209826 216579 216590 211467 213041 213046 214584 214591 214595 214597 214608 214614 214615 214617 <TBODY> </TBODY>

Approved On 31-Jul-2014</SPAN> 28-May-2014</SPAN> 3-Jun-2014</SPAN> 3-Jun-2014</SPAN> (blank)</SPAN> 30-Jul-2014</SPAN> 30-Jul-2014</SPAN> (blank)</SPAN> (blank)</SPAN> 11-Jun-2014</SPAN> (blank)</SPAN> (blank)</SPAN> (blank)</SPAN> 25-Jul-2014</SPAN> (blank)</SPAN> 19-Jun-2014</SPAN> 19-Jun-2014</SPAN> 19-Jun-2014</SPAN> 19-Jun-2014</SPAN> 14-Jul-2014</SPAN> (blank)</SPAN> (blank)</SPAN> (blank)</SPAN> <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[skywriter]

Try this, you will have to change the cell references to reflect your spreadsheet.
It's an array formula, so make sure you confirm with control shift enter.

=IF(SUMPRODUCT((A2:A24=A27)*(C2:C24<>""))<>COUNTIF(A2:A24,A27),"",MAX(IF(A2:A24=A27,C2:C24)))

Excel 2010
	A	B	C
1	Parent ID	Record ID	Approved On
2	199581	205332	31-Jul-14
3	201999	206951	28-May-14
4	201999	206959	3-Jun-14
5	201999	207556	3-Jun-14
6	202496	213546
7	202741	212785	30-Jul-14
8	202741	212788	30-Jul-14
9	204232	213022
10	204599	209578
11	204599	209826	11-Jun-14
12	206745	216579
13	206745	216590
14	206994	211467
15	207219	213041	25-Jul-14
16	207219	213046
17	209264	214584	19-Jun-14
18	209264	214591	19-Jun-14
19	209264	214595	19-Jun-14
20	209264	214597	19-Jun-14
21	209264	214608	14-Jul-14
22	209264	214614
23	209264	214615
24	209264	214617
25
26	Lookup Parent ID
27	201999
28	3-Jun-14
Sheet1

Cell Formulas
Range	Formula
A28	{=IF(SUMPRODUCT((A2:A24=A27)*(C2:C24<>""))<>COUNTIF(A2:A24,A27),"",MAX(IF(A2:A24=A27,C2:C24)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Galena1]

Bruce, first thanks for the response. I tried it and it didn't work. It returns the Max Date. I need it to return nothing (e.g., null or blank "") if anyone of the date cells are (blank). What I'm trying to do is say that a Parent record is considered closed IF ALL of the associated Child records (Field name 'Record ID') contain an Approved date. In this instance, the Max Date = Closed Date. However, if any one of the associated Child Records doesn't contain an approved on date, then the Parent Record is still to be considered Open and therefore, has no Max Closed Date.

I'll keep playing with your formula to see if I can get it to work. Try Record # 209264 in Col A to test your formula. It should return null or blank.

Thanks again for all your help!

 

[skywriter]

Try Record # 209264 in Col A to test your formula. It should return null or blank.

Yeah I did that before I posted it the first time. I tested it extensively and it works fine. I took your data and pasted it into a spreadsheet and worked from there. Copy my data and paste it into the exact cells represented, put in the formula, hit Control Shift Enter and type in what you like, you will see it works.

Here is another screen shot. I simply typed in 209264 and as you can see it returns a blank cell.
You need to make sure that you are referencing the right cells in your data or paste a screen shot that like my data shows exactly what cells you data is in and I will do it for you.

Excel 2010

	A	B	C
1	Parent ID	Record ID	Approved On
2	199581	205332	31-Jul-14
3	201999	206951	28-May-14
4	201999	206959	3-Jun-14
5	201999	207556	3-Jun-14
6	202496	213546
7	202741	212785	30-Jul-14
8	202741	212788	30-Jul-14
9	204232	213022
10	204599	209578
11	204599	209826	11-Jun-14
12	206745	216579
13	206745	216590
14	206994	211467
15	207219	213041	25-Jul-14
16	207219	213046
17	209264	214584	19-Jun-14
18	209264	214591	19-Jun-14
19	209264	214595	19-Jun-14
20	209264	214597	19-Jun-14
21	209264	214608	14-Jul-14
22	209264	214614
23	209264	214615
24	209264	214617
25
26	Lookup Parent ID
27	209264
28

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

119​

Array Formulas Cell Formula A28 {=IF(SUMPRODUCT((A2:A24=A27)*(C2:C24<>""))<>COUNTIF(A2:A24,A27),"",MAX(IF(A2:A24=A27,C2:C24)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Galena1]

My bad Bruce! I had some junk in the null cells that was causing the problem. I was originally applying the formula directly against the pivot table, and then decided to copy/paste it out into another worksheet to test my suspicion. As soon as I cleaned the cells, everything fell into place. wow. Sorry about that, again my apologies. Again, thanks for all your help!

 

[RatExcel]

My approach - CTRL + SHIFT + ENTER required

=IF(SUMPRODUCT(--((($A$2:$A$24=A2)*$C$2:$C$24+($A$2:$A$24=A2)*ISBLANK($C$2:$C$24))=1))=0,MAX(($A$2:$A$24=A2)*$C$2:$C$24),"")

Sample worksheet can be found here: https://app.box.com/s/lacg89ocwkfq777mcsc6

 

[skywriter]

My bad Bruce! I had some junk in the null cells that was causing the problem. I was originally applying the formula directly against the pivot table, and then decided to copy/paste it out into another worksheet to test my suspicion. As soon as I cleaned the cells, everything fell into place. wow. Sorry about that, again my apologies. Again, thanks for all your help!

No worries, it was a good experience for me and I'm sure it's not the best solution but it's what I came up with.
I'm here to learn.

Good Luck!