Wishing there was a 'maxif/minif' - your help appreciated!

[olivialott]

Hi!

I am trying to find earliest and latest contract expiry dates for a
large number of companies, each with many contracts!

Right now my data is in the format:

Company ID / Contract Expiry (with the data in separate columns, of
course)

There can be up to 10 contracts per company.
E.g.
Company X / 3 January 2009
Company X / 5 December 2011
Company X / 10 November 2012

I need to find the earliest and latest expiry dates, so the output
would look like
Company X / 3 January 2009 / 10 November 2012

I really want some kind of MAXIF formula! Is there an easy way to do
this, or will I have to crack out the VBA?

Thank you for your help! : )

 

[Luke M]

Enter these formulas as arrays (Ctrl+Shift+Enter to confirm, should see curly
brackets around formula if done correct)
=MIN(IF(A2:A100="Company X",B2:B100)
=MAX(IF(A2:A100="Company X",B2:B100)

Adjust range sizes as appropriate.

 

[Dave Curtis]

Hi,

Assuming your companies are in column A and dates are in column B, then

=MIN((A1:A100="Company X")*(B1:B100))

entered as an array formula with Control-Shift-Enter should do the trick.
Replace MIN with MAX for the maximum. Format your result cells as a date it
comes up with a number.

You could also refernce "Company X" by a cell reference.

Dave

 

[Dave Curtis]

Hi,
Sorry, I just realised that MIN behaves differently in array formulas.
The MAX formula should work, though the MIN should be replaced by

=MIN(IF(A1:A100="Company X",B1:B100,"") array entered.

Dave

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Value Filter, Earliest/Latest Date
No formulas
http://www.mediafire.com/file/gzgy2mmg3ut/03_13_09.xlsx