Try

=MAX(IF(B2:B366="Bob Jones",E2:E366))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Sue" wrote in message
...
Hi Biff,
Sorry I thought you were refering to the receiving column having to be
Date
formatted. But anyway both columns D and E are Date formatted.
Did the =ISNUMBER(E2) test and got True.
Did the =COUNTIF(B2:B366,"Bob Jones") and got 4 entries for that name
which
was correct. Did a test on other names and got 3 and 5 which was also
correct.
The names are copied and pasted all the way through the other workbook and
therefore should be identical.
Did your =SUMIF(B2:B366,"Name",E2:E366) and got 0/01/00. I think it just
uses the first date it comes across for that person which of course will
be a
0 or 0/01/00. I want it to look for a date greater than 0/01/00. Due to
the
post linking, where there is no date from the other workbook, it means
column
E defaults to 0 and with the column formated to Date, it becomes 0/01/00.
Could this be the reason and if so how do I alter the formula to look for
a
date greater than 0/01/00.
Thanks Sue


"Biff" wrote:

Hi!

0/01/00 is a zero formatted as a date. So that means the result of the
formula was 0.

Column D is already formated Date.

I thought the dates were in column E? Anyhow, are you certain they're
real
dates?

=ISNUMBER(E2)

Should return TRUE for real dates.

Are all the client names the same?

For example:

B2 = Jones

B50 = <spaceJones

B55 = Jones<space

You could try this:

=COUNTIF(B2:B366,"Jones")

Do you get the correct result?

=SUMIF(B2:B366,"Name",E2:E366)

This is a very basic formula and there's not too much that can cause a
problem. Either the dates aren't really dates and are just TEXT strings
or
the client name entries don't all match.

Biff

"Sue" wrote in message
...
Hi Biff,
Tried both suggestions but they both came back with 0/01/00 and I know
the
finish date for client B2 in column E is 25/03/05. Column D is
already
formated Date.
Can you help further?
Thanks Sue

"Biff" wrote:

Hi!

=sumproduct(--(B2:B366="name"),E2:E366)

But use this instead:

=SUMIF(B2:B366,"Name",E2:E366)

Format cell as DATE

Biff

"Sue" wrote in message
...
I have literally typed in
=sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366)
but I am getting #NAME? error. I want cells in column D to read
the
client
name in column B and go to column E and find the latest finish date
for
that
person. Where am I going wrong with the above formula? Clients
name
can
appear on up to 5 rows scattered intermittently down the
spreadsheet
but
only
one entry will have the finish date for that client and the rest
will
have
defaulted to 0 due to post linking from another workbook. Can
anyone
please
help. I have just this problem to go.
Thanks Sue.
B D
E
Client Names where latest finish date to be inserted
Finish
dates