Find Max in date field by customer

Yup, works for me. neater than mine, too.
That's the whole prob with the MIN thing - blanks and text
and FALSE screw
it up!!

> Oops I missed your post before I posted...
> With a little tweaking I get this.
>
> =MIN(IF(CustRng<>B2,999999999,DateRng))
>
> Array entered - of course.
> Brilliant idea Laurent.
>
>
> Regards
> Robert McCurdy
>
> ----- Original Message -----
> From: "Laurent Wright" <ltwrightBLUEYONDER.CO.UK>
> To: <EXCEL-GPEACH.EASE.LSOFT.COM>
> Sent: Friday, June 30, 2006 9:37 AM
> Subject: Re: Find Max in date field by customer
>
>
>> You're doing nothing wrong and neither is Excel
>> It just so happens that when B2 = custrange returns
a false, that's a
>> zero,
>> when SUMPRODUCTed against the daterng, so the MIN
is reporting exactly
>> what
>> it should
>> You'll have to tweak it (probably with an IF) to
test for when
>> B2*custerange
>> = false
>> Something like (untested)should simply punt the 0
value up to 99999999
>> leaving the true MIN
>>
>>
=SUMPRODUCT(MIN(IF(CustRng=B2)=FALSE,999999999,(Custrng=B2))
*DateRng))
>>
>> -----Original Message-----
>> From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
>> On
>> Behalf Of Shawn Bumgarner
>> Sent: 29 June 2006 19:26
>> To: EXCEL-GPEACH.EASE.LSOFT.COM
>> Subject: Re: Find Max in date field by customer
>>
>> Question for anyone out there - I was trying to
tweak this formula to
>> give
>> me back the minimum date (substituting MIN for MAX
in the spot but it
>> did
>> not work, comes back as zero (0).  Is there
something I'm doing wrong?
>> Thanks.
>>
>>  =SUMPRODUCT(MAX((CustRng=B2)*DateRng))
>>
>>  becomes...
>>
>>  =SUMPRODUCT(MIN((CustRng=B2)*DateRng))
>>
>>  but did not work for me???
>>
>>
>> Robert McCurdy <robertwmORCON.NET.NZ> wrote:
>>  Yo Shawn, this one is an Array formula.
>>
>> =MAX(IF(CustRng=B2,DateRng))
>>
>> and this one is a normally entered formula (but
still an array 
>>
>> =SUMPRODUCT(MAX((CustRng=B2)*DateRng))
>>
>> Where B2 is the start of your customer range.
>>
>>
>> Regards
>> Robert McCurdy
>>
>> ----- Original Message -----
>> From: "Shawn Bumgarner"
>> To:
>> Sent: Tuesday, June 27, 2006 1:35 AM
>> Subject: Find Max in date field by customer
>>
>>
>>> Hi everyone, I have a contact log in an Excel
2000 worksheet that has
>>> two
>> relevant fields for my question, customer number
and date of contact. In
>> another sheet I have a unique list of all those
customers and I would
>> like
>> to have a formula to show me the date of last
contact for each customer.
>> I'm
>> thinking it would be a form of the sumproduct
formula with a nested
>> Max()
>> formula inside of it? Anyone with ideas please let
me know, thank you.
>>>
>>> Shawn
>>
>>
------------------------------------------------------------
--------------
>> The EXCEL-G list is hosted on a Windows NT(TM)
machine running L-Soft
>> international's LISTSERV(R) software. For
subscription/signoff info
>> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>> COPYRIGHT INFO:
>> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>>
>>
>>
>>
------------------------------------------------------------
--------------
>> The EXCEL-G list is hosted on a Windows NT(TM)
machine running L-Soft
>> international's LISTSERV(R) software.  For
subscription/signoff info
>> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>>                             COPYRIGHT INFO:
>> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>>
>>
------------------------------------------------------------
--------------
>> The EXCEL-G list is hosted on a Windows NT(TM)
machine running L-Soft
>> international's LISTSERV(R) software.  For
subscription/signoff info
>> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>>                             COPYRIGHT INFO:
>> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>>
>
>
------------------------------------------------------------
--------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
> international's LISTSERV(R) software.  For
subscription/signoff info
> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>
>

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G