Sumproduct

L

litos_aldovea

I have 3 columns of data, NAME, DATE,VALUE

NAME Date Text
Carlos 1/1/4 10
Carlos 2/1/4 20
Carlos 5/6/4 30
Carlos 6/6/4 40
Peter 5/5/4 50
Peter 20/6/4 60

The forumla that I need to use is

Look for CARLOS in the database, and retrieve VALUE when NAME is CARLO
and DATE is the LATEST (for carlos of course).

In this example if I look for Carlos the data I want is 40
If I look for Peter the data I want should be 60

The formula I tried was

=sumproduct(NAMES=Carlos)*(DATES=MAX(DATES)),VALUE)

I think it is because it doesn't match the latest date for carlos. I
can't make it work. I guess my mistake is that DATES=MAX(DATES) is no
related to NAME=CARLOS. And Because the NAME and the MAX DATE doesn'
match I retrieve no value.

Any ideas will be very much appreciated.

Thanks and regards,
Carlos
 
J

Jerry W. Lewis

MAX(IF(NAMES="Carlos",DATES))
returns the latest Carlos date. It will require array entry despite the
use of SUMPRODUCT.

Jerry
 
Top