every day we record the product name, quantity and the date, how to make
lookup or vlookup (or any other function) find the first date and the last
date for specific product to calculate for how many days this product produced
example; we have products a,b and c we record the quantity produced every
day for 30 days (also record the date) the columns ( product / qty / date)
if the product b records only in days 5,6,8,15 and 20 of the month i need
formula to know the the first date is 5 and the last date is 20 the result
should be 15 days
thanks

Hi!

Try this:

Column A = product (A, B, C)
Column C = dates produced

This is an array formula. It MUST be entered using the key combination of
CTRL,SHIFT,ENTER:

This is for product "A":

=MAX(IF(A1:A20="A",C1:C20))-MIN(IF(A1:A20="A",C1:C20))

Adjust ranges to suit.

Biff

"adam" wrote in message
...
every day we record the product name, quantity and the date, how to make
lookup or vlookup (or any other function) find the first date and the last
date for specific product to calculate for how many days this product
produced
example; we have products a,b and c we record the quantity produced every
day for 30 days (also record the date) the columns ( product / qty / date)
if the product b records only in days 5,6,8,15 and 20 of the month i need
formula to know the the first date is 5 and the last date is 20 the result
should be 15 days
thanks

Hi
the formula working in the formula screen but the cell always 0

"Biff" wrote:

Hi!

Try this:

Column A = product (A, B, C)
Column C = dates produced

This is an array formula. It MUST be entered using the key combination of
CTRL,SHIFT,ENTER:

This is for product "A":

=MAX(IF(A1:A20="A",C1:C20))-MIN(IF(A1:A20="A",C1:C20))

Adjust ranges to suit.

Biff

"adam" wrote in message
...
every day we record the product name, quantity and the date, how to make
lookup or vlookup (or any other function) find the first date and the last
date for specific product to calculate for how many days this product
produced
example; we have products a,b and c we record the quantity produced every
day for 30 days (also record the date) the columns ( product / qty / date)
if the product b records only in days 5,6,8,15 and 20 of the month i need
formula to know the the first date is 5 and the last date is 20 the result
should be 15 days
thanks

Hello,

I suggest

=LOOKUP(2,1/("A"=$A$1:$A$25),$C$1:$C$25)-INDEX($C$1:$C$25,MATCH("A",$A$1:$A$25,))

(no array formula)

Regards,
Bernd

The formula is an array formula. It MUST be entered using the key
combination of CTRL,SHIFT,ENTER. Type the formula, then, instead of just
hitting ENTER like you normally would, hold down both the CTRL key and the
SHIFT key then hit ENTER. If done properly Excel will enclose the formula in
squiggly braces { }. You cannot just type these braces in, you MUST use the
key combo.

Biff

"adam" wrote in message
...
Hi
the formula working in the formula screen but the cell always 0

"Biff" wrote:

Hi!

Try this:

Column A = product (A, B, C)
Column C = dates produced

This is an array formula. It MUST be entered using the key combination of
CTRL,SHIFT,ENTER:

This is for product "A":

=MAX(IF(A1:A20="A",C1:C20))-MIN(IF(A1:A20="A",C1:C20))

Adjust ranges to suit.

Biff

"adam" wrote in message
...
every day we record the product name, quantity and the date, how to
make
lookup or vlookup (or any other function) find the first date and the
last
date for specific product to calculate for how many days this product
produced
example; we have products a,b and c we record the quantity produced
every
day for 30 days (also record the date) the columns ( product / qty /
date)
if the product b records only in days 5,6,8,15 and 20 of the month i
need
formula to know the the first date is 5 and the last date is 20 the
result
should be 15 days
thanks

Hi
what is 2,1/ meaning in this formula

" wrote:

Hello,

I suggest

=LOOKUP(2,1/("A"=$A$1:$A$25),$C$1:$C$25)-INDEX($C$1:$C$25,MATCH("A",$A$1:$A$25,))

(no array formula)

Regards,
Bernd

Hi Adam,

LOOKUP ignores error values and returns the last non-error value if the
search value was not found.

1/(comparison) gives 1,1,1,#DIV0!,1,1,#DIV0!,1,#DIV0!,1... and if you
search any value 1 in here (i.e. 2) then you will get the position of
the last 1 - exactly what we need. Go into the formula editor and
evaluate this partially by selecting (comparison) and pushing F9, then
1/(comparison) and F9 again, etc.

[Not my invention, just my favourite question if somebody claims to be
an Excel expert :-) ]

Regards,
Bernd