Prev: Drop down list
Next: SUMPRODUCT with OR condition
From: Carine on 30 Mar 2010 10:11
Hi Mike,

Sorry to correct my mistake, the formula I used was
={IF(B2="BCD",AL7,MIN(IF($A$2:$A$5=A2,$C$2:$C$5)))}
I had used the array formula over it.

The reason why I was returned the date from BCD was becos' of my criteria
was set to "Name". Is there any way I can check the Name and also limit the
minimum date value to only those under Company "ABC"?

Thanks,
Carine

"Mike H" wrote:

> > Is there any way I can capture the minimum date for the only for Company
> > "ABC" and ignore the date for "BCD":
>
> You never read my response. The formula I gave you is an ARRAY formula and
> will return excactly what your looking for if you follow the instructions.
>
> Paste the formula in the formula bar then:
>
> This is an array formula which must be entered by pressing CTRL+Shift+Enter
> 'and not just Enter. If you do it correctly then Excel will put curly brackets
> 'around the formula {}. You can't type these yourself. If you edit the formula
> 'you must enter it again with CTRL+Shift+Enter.
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Carine" wrote:
>
> > Hi Mike,
> >
> > Thanks very much for your help.
> > Tried the formula out and it works well. However, I noticed that with the
> > mentioned formula, i have the following end results:
> >
> > Formula used: =IF(B2="BCD",AL7,MIN(IF($A$2:$A$5=A2,$C$2:$C$5)))
> >
> > Name Company Date End Result
> > Alex ABC 31-12-2009 31-12-2006
> > Alex ABC 31-12-2008 31-12-2006
> > Alex ABC 30-11-2009 31-12-2006
> > Alex BCD 31-12-2006 31-12-2006
> >
> >
> > Is there any way I can capture the minimum date for the only for Company
> > "ABC" and ignore the date for "BCD":
> > Name Company Date End Result
> > Alex ABC 31-12-2009 31-12-2008
> > Alex ABC 31-12-2008 31-12-2008
> > Alex ABC 30-11-2009 31-12-2008
> > Alex BCD 31-12-2006 31-12-2006
> >
> >
> > Thanks,
> > Carine
> >
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > I have assumed your data are in columns A, B & C.
> > > Put this ARRAY formula in D2 enter as an ARRAY (see below) and drag down
> > >
> > > =IF(B2="BCD",C2,MIN(IF($B$2:$B$10=B2,$C$2:$C$10)))
> > >
> > > This is an array formula which must be entered by pressing CTRL+Shift+Enter
> > > 'and not just Enter. If you do it correctly then Excel will put curly brackets
> > > 'around the formula {}. You can't type these yourself. If you edit the formula
> > > 'you must enter it again with CTRL+Shift+Enter.
> > > --
> > > Mike
> > >
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > > introduces the fewest assumptions while still sufficiently answering the
> > > question.
> > >
 | 
Pages: 1
Prev: Drop down list
Next: SUMPRODUCT with OR condition