find minimum of range based on multiple criteria

I have about 6 columns of data, but only 3 are important in what I'm trying to find. Here is an example of my spreadsheet:

Score FY Awarded
131 2001 1
145 2001 1
120 2001 0
256 2002 0
344 2002 1
138 2002 1
111 2002 0

Rows 5 through 49 have these data.

etc... up until FY 2006. The data are assorted by FY ascending. 1 in the Award column means it was awarded, 0 means it wasn't. I want to find a formula, if possible, that will find the minimum score that was awarded in each FY. I was able to do it with this: =DMIN($F$5:$K$49,"Score",F1:K2) where the criteria are "=2001" or whichever FY I am interested in and ">0" for the FY column. But, this is messy because I have to either set up several database headers, or manually go in and change whichever FY I am searching for. Clearly I can do this all manually, but I want to find a formula where all I have to do is change the FY in the formula to get my result. I've also been trying things like:

=MINA(IF((AND($H$6:$K$49=2001,$K$6:$K$49>1)),$F$6:$F$49))


Thoughts? Is what I'm trying to do even possible? Or should I just go in and do it all manually...

Thanks in advance

=MIN(IF(($H$6:$K$49=2001)*($K$6:$K$49=1),$F$6:$F$49))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Weissme" <[email protected]> wrote in
message news:[email protected]...
>
> I have about 6 columns of data, but only 3 are important in what I'm
> trying to find. Here is an example of my spreadsheet:
>
> Score FY Awarded
> 131 2001 1
> 145 2001 1
> 120 2001 0
> 256 2002 0
> 344 2002 1
> 138 2002 1
> 111 2002 0
>
> Rows 5 through 49 have these data.
>
> etc... up until FY 2006. The data are assorted by FY ascending. 1 in
> the Award column means it was awarded, 0 means it wasn't. I want to
> find a formula, if possible, that will find the minimum score that was
> awarded in each FY. I was able to do it with this:
> =DMIN($F$5:$K$49,"Score",F1:K2) where the criteria are "=2001" or
> whichever FY I am interested in and ">0" for the FY column. But, this
> is messy because I have to either set up several database headers, or
> manually go in and change whichever FY I am searching for. Clearly I
> can do this all manually, but I want to find a formula where all I have
> to do is change the FY in the formula to get my result. I've also been
> trying things like:
>
> =MINA(IF((AND($H$6:$K$49=2001,$K$6:$K$49>1)),$F$6:$F$49))
>
>
> Thoughts? Is what I'm trying to do even possible? Or should I just go
> in and do it all manually...
>
> Thanks in advance
>
>
> --
> Weissme
> ------------------------------------------------------------------------
> Weissme's Profile:
http://www.excelforum.com/member.php...o&userid=34044
> View this thread: http://www.excelforum.com/showthread...hreadid=544071
>

Hi, Thanks! I will try that, but I also found that this works:

{=MIN(IF($K$6:$K$49=1,IF($H$6:$H$49=O10,$F$6:$F$49)))}

Most exciting triumph of my day I think.

Thanks again!

Mine has one les IF <vbg>

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Weissme" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, Thanks! I will try that, but I also found that this works:
>
> {=MIN(IF($K$6:$K$49=1,IF($H$6:$H$49=O10,$F$6:$F$49)))}
>
> Most exciting triumph of my day I think.
>
> Thanks again!
>
>
> --
> Weissme
> ------------------------------------------------------------------------
> Weissme's Profile:
http://www.excelforum.com/member.php...o&userid=34044
> View this thread: http://www.excelforum.com/showthread...hreadid=544071
>