Lookup Oldest date from previously selected Group Data

[Jim May]

Sample Data (table)

A B C D
1 APM xxxxxxxxxxx 1/15/05 2.00
2 APM xxxxxxxxxxx 2/28/05 2.00
3 APM xxxxxxxxxxx 1/13/05 2.00
4 APM xxxxxxxxxxx 12/4/04 2.00
5 APM xxxxxxxxxxx 3/15/05 2.00
6 APM xxxxxxxxxxx 4/20/05 2.00
7 APM Total 12.00
8 GPS xxxxxxxxxxx 4/13/05 3.00
9 GPS xxxxxxxxxxx 4/10/05 3.00
10 GPS xxxxxxxxxxx 4/5/05 3.00
11 GPS xxxxxxxxxxx 12/15/04 3.00
12 GPS xxxxxxxxxxx 11/27/04 3.00
13 GPS xxxxxxxxxxx 3/10/05 3.00
14 GPS Total 18.00
Above on Sheet1

On my Sheet 2
Cell D4 = GPS
Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$D$13)
which displays 18
In Cell F5 i need to Bring back the oldest date in the same GPS group, the
answer 11/27/04,,, Can someone help me?

Tks in advance..

 

[Bob Phillips]

Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1:$C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

Hi!

Try this:

Array entered:

=MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13))

Format as DATE

Biff

 

[Jim May]

much appreciated Bob;
works great!!
Jim

Hi Jim,

=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1:$C$13))

as an aray formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim May]

Thanks Biff for the help!
Jim

Hi!

Try this:

Array entered:

=MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13))

Format as DATE

Biff

 

[Jim May]

Bob:
The CSE formula worked perfect in my (Small and simple) example;
but when I applied it against a huge set of data (1500 rows --for a fellow
employee)
it produced 01/00/00 (Date formatted) Value of 0.
This probably due to perhaps some "foreign-crap" within the Ranges
Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc.

Can I test (On my Sheet1) using a temporary helper column to determine the
culprit cell(s) causing my final CSE formula to produce the 0 value?
Thanks for your help!!
Jim

 

[Bob Phillips]

Hi Jim,

Why not just filter the date column for a 0 date?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim May]

In my sample range A1:A13 there was a single blank cell of Text
which was causing the problem - once I filled it in the original formula
worked.

I tried (to eliminate this possibility from happening again by trying the
following but it didn't seem to help/work...
=MIN(IF(AND(miss!$D$5:$D$1444<>"",miss!$D$5:$D$1444=$B$4),miss!$E$5:$E$1444)
) <<< where D column hold my VCode (one SMPC-Text)
and E:E holds the dates..

Tks Bob,
Jim May

 

[Bob Phillips]

Jim,

Is this what you want?

=MIN(IF((miss!$D$5:$D$1444=$B$4)*(miss!$E$5:$E$1444<>""),miss!$E$5:$E$1444))

--

HTH

RP
(remove nothere from the email address if mailing direct)