Obtain Value from a Max Date Condition

[gcmontgomery30]

I need to find the value that is in an adjacent cell based on the max (most
recent) date of a certain product number that will show up multiple times in
a seperate worksheet in the workbook and will have emply rows that data will
be added to in the future. I have tried everything to make this work with no
luck. Please Help. Below is an example of what I need. If the product were
test, I need to return the value of 200.

Product Date Ordered Qty Received
test 08/02/08 100
help 08/02/08 50
test 08/05/08 50
test 08/08/08 200
help 08/08/08 100

I would greatly appreciate any help with the formula that will make this work.

 

[M Kan]

=SUMPRODUCT(--(Product=A9),--(Date_Ordered=MAX(B3:B7)),Qty_Received)

Where A9 is an input cell where you can enter "test"

 

[Don Guillett]

See if this works. Array enter by using ctrl+shift+enter

=MAX(IF(A2:A22="test",C2:C22))

 

[gcmontgomery30]

I can't seem to make this work. Does it matter that my data is in a seperate
sheet from where I am putting the formula. When I put in the formula below
based on my real worksheet, the value returned is 0 which is not correct.
Column A is the product, Column B is where the dates are and Column C is Qty
Received values.

=SUMPRODUCT(--('Data Log'!A5:A143="WS051"),--('Data Log'!B5:B143=MAX('Data
Log'!B5:B143)),'Data Log'!C5:C143)

 

[M Kan]

You might also want to check the condition WS051 to make sure this is how
it's actually reflected in your data set.

 

[gcmontgomery30]

Don, this didn't work either. Where does it reference the date ranges to get
the Max date from? This doesn't seem like it would be such a difficult
formula to figure out, but I just can't find anything that will make it work.
Any other advise?

Thanks,

 

[Don Guillett]

The sumproduct formula presented should work just fine. Perhaps your text
needs trimming.
=SUMPRODUCT(--(TRIM('Data Log'!A5:A143)="WS051"),--('Data
Log'!B5:B143=MAX('Data

 

[Don Guillett]

If in chronological order, as presented, you really shouldn't need to check
the date. As I said, TRIM.

 

[gcmontgomery30]

Don, I have tried this formula and it looks like it works for some of the
product numbers I put in but then for others it returns a value of 0 which is
not correct. I thought it wasn't working if there were multiple entries for
a certain product, but in one case it did return the correct value. Any idea
why it isn't working for each product? Thanks so much for all the help you
have provide.

 

[Don Guillett]

If you like, send your workbook to my address below and I'll take a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don, I have tried this formula and it looks like it works for some of the
product numbers I put in but then for others it returns a value of 0 which
is
not correct. I thought it wasn't working if there were multiple entries
for
a certain product, but in one case it did return the correct value. Any
idea
why it isn't working for each product? Thanks so much for all the help you
have provide.

 

[Teethless mama]

Try this:

=INDEX('Data Log'!C5:C143,MATCH(MAX(('Data Log'!A5:A143="WS051")*('Data
Log'!B5:B143)),'Data Log'!B5:B143,0))

ctrl+shift+enter, not just enter