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

O

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! : )
 
L

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.
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top