Sumproduct

All,

I am a bit in the weeds as to exactly how SUMPRODUCT works...and I think that is why I am unable to get my new formulas to work. I inherited a SUMPRODUCT formula from some help here on the forum (thank you very much) and I ran with it. Now I am out of my depth.

Please see attached. The blue highlight cells in column M on Spring 2010 are my issue. I have been converting my spreadsheet to be based on named arrays rather then hardwired cell ranges and in doing so I have been able to make my way though quit well. Now however I need to search and rank other than by the row number and I am stumped.

I need to get all rows from the named array that have a certain value in a column, then find the lowest value in different column for that subset. At current I appear to be doing one, then scrapping it then doing the other. I thought I could rank by one and then the other, but I am missing something big-time. I have reason to believe it is my poor understanding of SUMPRODUCT that has gotten me here.

Here is my plain speak formula: find all rows in NAMED ARRAY that match SEARCH CRITERIA in COLUMN X...form this subset find the lowest value and COLUMN Y and report it.

You can see my actual attempts on the sheet. Any help would be appreciated…even if it is just a nudge in the right direction or a more concrete description of how SUMPRODUCT works

Thanks,
The Dropper