Report the first and last date where an item is listed

[itchy20]

I have a spreadsheet which is used for tracking work items for a team of people.
On one sheet I have a column of dates and rows of named 'tasks' against them.
I need to identify the first and last dates at which a 'task' is listed.

My spreadsheet is attached.

On sheet2 K2 I want to see the first / earliest date where 'Fish' occurs on sheet1
On sheet2 L2 I want to see the last / latest date where 'Fish' occurs on sheet1

Please help! I have tried using SUMPRODUCT MAX / MIN but I got myself in a muddle!!

[Special-K]

Fish occurs first on 11/06 and last occurrence is 07/07
There are two columns on Sheet2 labelled Available (16/06) and End No later than (30/06)
Do these columns need to be taken into account, ie are they restricting the original date range from 11/06-07/07 to 16/06-30/06?

[sweep]

Hi,

Do the following arrays work for you?

=MAX(IF(Sheet1!D2:U1024="Fish",Sheet1!B2:B1024,""))

=MIN(IF(Sheet1!D2:U1024="Fish",Sheet1!B2:B1024,""))

Confirm these with CTRL, SHIFT and ENTER.

[itchy20]

It does work, however I chnaged "Fish" to "A2" so that I could replicate down without needing to type the task name in each formula, then it stopped working.

[itchy20]

Special K - the available and end no later do not need to be considered - they are informational rather than restrictive

[hoyasaxa215]

Lock the cell reference ($A$2) and remember to hit ctrl+shift+enter for the array function to work.