Finding MIN based on adjacent criteria

[mmchaley]

What I am attempting to do is find the MIN value in Column C where values in Column A are equal.

The data would look like this

A B C D (D:D is where the "MIN" Formula will be)
Scope1 NameA $100
Scope1 NameB $145
Scope1 NameC $115 $100 (I want the min value to show up here)
- (this would trigger a break between scopes, and provide a conditional format separator)
Scope2 NameE $450
Scope2 NameG $345
Scope2 NameX $415 $345
-

So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)

This is a template I want to set up and not a one time spreadsheet. For any given scope I will have between 1 and 15 Names. Also between projects the scopes will change as will the Names of the subcontractors supplying those scopes.


Thanks much

[DonkeyOte]

If the Data is sorted alphabetically by Scope as implied in your sample then one approach would be:

Please Login or Register  to view this content.

above assumes row 1 holds first data points

(the above is designed so as to avoid requirement for Array formula)

[mmchaley]

Thanks for the response - It does not give a result, I am not sure if it is my data format, what is required to be A to Z sorted, or something currently mysterious to me.

I was able to get some of the data into a spreadsheet and have placed your formula into the spreadsheet.

I placed notes in the spreadsheet to give a better indication as to what i am looking for.

Thanks for the help,

Mark

Formula Ignorance.xlsx

[DonkeyOte]

based on your file:

Please Login or Register  to view this content.

caveat: the above is based on data up to row 27, thereafter your layout appears to alter dramatically... not sure what the requirements are in these latter rows.

[mmchaley]

Thanks, I appreciate the assist.

After row 27, it is pretty much formatting in progress as I have not finished my data entry.

If you don't mind, what has me stumped in the formula is what does the "<>2" do? The rest of it somewhat makes sense to me.

Thanks for your time,

Mark

[DonkeyOte]

The formula basically just says if the row below has 2 blanks in A:C range then conduct a MIN test up to and including this row from the beginning of this section based on values in C... if the row below does not have 2 blanks then implication is such that subsequent row is either another complete transaction or blank etc and thus no MIN calc is required on the current row.