Array MIN function with multiple criteria

[powercell99]

I use an array MIN formula too give me the earliest date from a set of data meeting one criteria. The dates are formatted as dates. ex: {=MIN(IF($D$2:$D$2222=$D2,$M$<wbr>2:$M$2222 ))}
It works flawlessly and i understand what its doing. But when i try to use that method and add a 2nd criteria, it returns 0 as the earliest date.
ex: {=MIN(IF(AND($D$2:$D$2222=$D2, $G$2:$G$2222=$G2),$M$2:$M$2222))}

Logically, that should work if i understand what the Array MIN formula with 1 criteria is doing. But it doesn't work.

{=MIN(IF(AND($D$2:$D$2222=$D2, $G$2:$G$222=$G2),$M$2:$M$2222))}

I've read on some other pages to try:
{=MIN(IF($D$2:$D$2222=$D2,IF($G$2:$G$2222=<wbr>$G2, $M$2:$M$2222,"")))}

But that doesnt seem to work either, AND i cant understand the concept of what that is doing.

Any suggestions? What am i missing????
Any help would be greatly appreciated.

Larry

 

[shg]

Re: Array MIN function with multiple criteria - H E L P P L E A S E

AND returns a single value, not an array of values.

=MIN(IF(($D$2:$D$2222 = $D2) * ($G$2:$G$2222 = $G2), $M$2:$M$2222))

 

[powercell99]

Re: Array MIN function with multiple criteria - H E L P P L E A S E

Hi shg, Thanks very much for the fast reply.

I think its getting close, but that gives the same date for every row instead of the earliest date for records matching the 2 criteria.

here is a pivot snag showing each record has several request dates and several approved dates. I need the earliest request date and the earliest approved date for each record
(ex: M00M4Y6 earliest request date 5/1/15, earliest approved date 12/7/15) Any other suggestions? Thanks again for taking the time to help and insights.

M00MY6

Request

5/1/15

10/19/15

10/23/15

12/1/15

Approved

12/7/15

12/17/15

1/6/16

1/8/16

M00P7N

Request

5/14/15

8/31/15

9/2/15

9/16/15

9/18/15

9/22/15

10/16/15

Approved

10/28/15

11/2/15

11/3/15

11/4/15

M00U20

Request

1/8/16

6/18/15

7/2/15

7/6/15

1/19/16

3/3/16

3/4/16

Approved

3/14/16

3/17/16

3/18/16

<colgroup><col></colgroup><tbody>
</tbody>

 

[powercell99]

Re: Array MIN function with multiple criteria - H E L P P L E A S E

I've added a workbook as an example of how the formula is not working....

https://drive.google.com/open?id=0B3dH8W8Wx-Bacnk1Z0xCSWtuT3M

 

[shg]

The formula should be array entered in a single cell and then copied down, not array-entered across all rows.

But it's rediculously resource-intensive. You could instead sort by Record Num and Date Stamp, then select A:F and remove duplicates in columns A and B. What's left is the earliest date for each record and each type.

 

[shg]

E.g.,

	A​	B​	C​	D​
1​	Record Num​	Document Type​	Date Stamped​	Date Only​
2​	M004J5	Request	19 Dec 2014 11:09​	19 Dec 2014​
3​	M00MY6	Request	01 May 2015 13:03​	01 May 2015​
4​	M00MY6	Approved	07 Dec 2015 08:41​	07 Dec 2015​
5​	M00P7N	Request	14 May 2015 11:14​	14 May 2015​
6​	M00P7N	Approved	28 Oct 2015 11:26​	28 Oct 2015​
7​	M00U20	Request	18 Jun 2015 07:04​	18 Jun 2015​
8​	M00U20	Approved	14 Mar 2016 08:45​	14 Mar 2016​
9​	M00U8V	Request	18 Jun 2015 13:00​	18 Jun 2015​
10​	M00U8V	Approved	23 Oct 2015 08:48​	23 Oct 2015​
11​	M00UZA	Request	24 Jun 2015 05:58​	24 Jun 2015​