=MIN Formula with condition

[fullspeed]

Hi, I am struggling with the condition that I want to put in the MIN formula in excel sheet where I want the formula to find minimum value within the same year on the next column.
Any help would be appreciated.
Here is the image of what I have now and desired results.
column A has years ranging from 2011 to 2014 and on column B, associated values for that row.
I am looking to write formula in column C to spit out the lowest value from column B for that year associated in column A.
I hope this makes sense.

QUESTION TO EXCEL FORUM.JPG

[daddylonglegs]

You can use an "array formula" like this in C2 for Desired results 2

=MIN(IF(A$2:A$10=A2,B$2:B$210))

confirm with CTRL+SHIFT+ENTER and copy down

....or a non-array option

=AGGREGATE(15,6,B$2:B$10/(A$2:A$10=A2),1)

If you only want to show the min value on the row(s) where it occurs (Desired results 1) try this version

=IF(COUNTIFS(A$2:A$10,A2,B$2:B$10,"<"&B2),"",B2)

[fullspeed]

Thank you Daddylonglegs.
It is though giving me 6 for all of the rows in column C.
I think i did the array formula correctly?
here is the screen shot

QUESTION FOR EXCEL FORUM 2.JPG

[fullspeed]

It looks like when I do the array, it won't let me change the cell reference in the formula - so for example, I want to change the cell reference to =MIN(IF(A$3:A$11=A4,B$3:B$11)) in cell C4, but it is not letting me.

[skywriter]

Hi I don't mean to take over for daddylonglegs, but this is a different kind of array formula. Only select and enter the formula into the first cell, hit Control + Shift + Enter and then you can copy paste to the other cells or just drag it down to copy it.

It looks like when I do the array, it won't let me change the cell reference in the formula - so for example, I want to change the cell reference to =MIN(IF(A$3:A$11=A4,B$3:B$11)) in cell C4, but it is not letting me.

[fullspeed]

Actually, =IF(COUNTIFS(A$2:A$10,A2,B$2:B$10,"<"&B2),"",B2) worked out perfectly. so I am just going to use this one. Thank you so much!!

[ChemistB]

When you do the array, only do it in C3. Then copy/drag it down to the other cells. If you have all the cells selected when you create the array, it creates a different sort of array.

[fullspeed]

ah I see!
entered the formula,
hit F2,
hit Control + Shift + Enter and
dragged it down

This worked! Thank you so much Skywriter.

[MarvinP]

Hi fullspeed,

I like Pivot Tables and they will give minimums per FY. So I've created a Pivot Table and shown minimums and then used it in a VLookup. See the attached. This is just another option for doing your problem.

[skywriter]

ah I see!
entered the formula,
hit F2,
hit Control + Shift + Enter and
dragged it down

This worked! Thank you so much Skywriter.

Thanks for the if countsifs formula, I've never seen that one before, I like it.