Oldest date ignoring defaults.

[Mizery]

I work with a lot of a large list of products received from a variety of countries and need to find the oldest instance of each individual product for each country. I can find the date individually not a problem, using the following formula:

{=MIN(IF((Lim!H2:H10000="CH"),Lim!B2:B10000))}

where 'lim' is the name of the sheet the data is pasted into,
H is the column that the country code is located in (in this instance 'CH')
and B is the column that that date is located in.

Unfortunately not all of the smaller countries are always present, which means the formula returns '00-Jan-00', and is therefore instantly the oldest date. I want to find the date for each country, but then only report the oldest of them all together.

Is there anyway of editing the formula to ignore any country that is not present (or give a 'N/A' result), or ignore the default date '00-jan-00'?

For instance, I need the oldest UK, DE and FR, then the oldest one out of BE, IE and CH. If BE is not present, it automatically sets at the default and becomes the oldest. I am looking to have the formula ignore this result.

I Hope I have made myself half clear. Thanks to anyone who gives this a shot.

[NBVC]

Try:

Please Login or Register  to view this content.

[Mizery]

Genius!!

Thanks very much, it works perfectly. That was ridiculously fast. Your forumla solved a few other problems that I was overcoming with bits and pieces, so thanks!