Array Formula for Max Value with Conditions

[PoggiPJ]

Originally I only needed the latest date in a list - simple, Max(A2:A7). Now I need to only consider end dates where the Extended Total is >0. I understand the basics of how array formulas work and have written some simple ones, but I am struggling with an array formula that will give me the max of col A when Col B > 0. In the following example rows 3 and 5 would be excluded and the result would be 5/31/2016. Would really appreciate some help.

Excel 2003

	A	B
Coverage Ends	Extended Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3/1/2013[/TD]
[TD="align: right"] 4,339.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5/15/2015[/TD]
[TD="align: right"] 39,992.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/31/2016[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5/31/2016[/TD]
[TD="align: right"] 5,422.00[/TD]

</tbody>

Sheet1​

 

[Robert Mika]

Originally I only needed the latest date in a list - simple, Max(A2:A7). Now I need to only consider end dates where the Extended Total is >0. I understand the basics of how array formulas work and have written some simple ones, but I am struggling with an array formula that will give me the max of col A when Col B > 0. In the following example rows 3 and 5 would be excluded and the result would be 5/31/2016. Would really appreciate some help.

Excel 2003

	A	B
Coverage Ends	Extended Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3/1/2013[/TD]
[TD="align: right"] 4,339.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5/15/2015[/TD]
[TD="align: right"] 39,992.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/31/2016[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5/31/2016[/TD]
[TD="align: right"] 5,422.00[/TD]

</tbody>

Sheet1​

Excel 2010
	A	B	C	D
1	Coverage Ends	Extended Total
2	3/1/2013	4,339.00		5/31/2016
3	12/31/2017
4	5/15/2015	39,992.00
5	7/31/2016
6	4/1/2013
7	5/31/2016	5,422.00
Sheet3

Cell Formulas
Range	Formula
D2	{=MAX(IF(B2:B7>0,A2:A7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[PoggiPJ]

Robert, thank you very much. I was definitely going down the wrong path with my thinking. That worked perfectly.

 

[Robert Mika]

Robert, thank you very much. I was definitely going down the wrong path with my thinking. That worked perfectly.

You are welcome.