sumproduct for latest date

[Guest]

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

 

[Biff]

Hi!

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

But use this instead:

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

Format cell as DATE

Biff

 

[Guest]

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]

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 = <space>Jones

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

 

[Guest]

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

 

[Bob Phillips]

Try

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

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

 

[Guest]

Hi Bob,
You have cracked it. It works beautifully.
I substituted the name for the cell address as I won't know who is in that
name cell and it works great. Thanks so very much. I am too young to be
going grey yet but it was starting to happen.
Thanks again
Sue

 

[Guest]

Hi Biff,
Thank you for your input and assitance. I really appreciate it.
Doing a great job! Problem now solved.
Sue

 

[Bob Phillips]

Glad to help Sue. I didn't use the name as others had used "Name" and names
don't have quotes. Best to be cautious thought I.

Bob

 

[Biff]

Hi!

Glad you got it worked out!

I don't know why the SUMIF didn't work, it should have! I too, was going to
suggest MAX IF as a last resort but it's a bit of "overkill". But hey, if
it's working.......

Biff