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.
--
gcmontgomery30

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

Where A9 is an input cell where you can enter "test"
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gcmontgomery30" wrote:

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.
--
gcmontgomery30

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in message
...
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.
--
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)
--
gcmontgomery30


"M Kan" wrote:

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

Where A9 is an input cell where you can enter "test"
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gcmontgomery30" wrote:

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.
--
gcmontgomery30

You might also want to check the condition WS051 to make sure this is how
it's actually reflected in your data set.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gcmontgomery30" wrote:

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)
--
gcmontgomery30


"M Kan" wrote:

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

Where A9 is an input cell where you can enter "test"
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gcmontgomery30" wrote:

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.
--
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,
--
gcmontgomery30


"Don Guillett" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in message
...
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.
--
gcmontgomery30

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
Log'!B5:B143)),'Data Log'!C5:C143)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in message
...
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)
--
gcmontgomery30


"M Kan" wrote:

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

Where A9 is an input cell where you can enter "test"
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gcmontgomery30" wrote:

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.
--
gcmontgomery30

If in chronological order, as presented, you really shouldn't need to check
the date. As I said, TRIM.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in message
...
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,
--
gcmontgomery30


"Don Guillett" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in
message
...
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.
--
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.
--
gcmontgomery30


"Don Guillett" wrote:


If in chronological order, as presented, you really shouldn't need to check
the date. As I said, TRIM.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in message
...
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,
--
gcmontgomery30


"Don Guillett" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in
message
...
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.
--
gcmontgomery30

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in message
...
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.
--
gcmontgomery30


"Don Guillett" wrote:


If in chronological order, as presented, you really shouldn't need to
check
the date. As I said, TRIM.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in
message
...
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,
--
gcmontgomery30


"Don Guillett" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gcmontgomery30" wrote in
message
...
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.
--
gcmontgomery30

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


"gcmontgomery30" wrote:

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.
--
gcmontgomery30