Hi Nitya,

Am Mon, 11 Apr 2016 22:25:55 -0700 (PDT) schrieb Nitya Satheesh:

What I was looking for is the entire species set. In my above dataset, my first date is 1/1/2014 where i have got my first species and the last date would be 21/1/2014 where i have got species 5. so my time period would be 21 days. i just need a period in which i get all my species. i have 5 species in the above list, so the minimum time period in which i have got all my 5 species.

if you have a new Excel version try:
=MINIFS(A:A,B:B,"species 5")-MINIFS(A:A,B:B,"species 1")+1
With older versions try:
=INDEX(A:A,SMALL(IF(B1:B1000="species 5",ROW(1:1000)),1))-INDEX(A:A,SMALL(IF(B1:B1000="species 1",ROW(1:1000)),1))+1
and enter the last formula with CRTL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional