Sumproduct

[The Dropper]

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

[DonkeyOte]

If I've understood... would it not perhaps be easier to use a MIN array ?

M182:
=MIN(IF(INDEX(WEEK04C,0,14)=C182&501,INDEX(WEEK04C,0,6)))
confirmed with CTRL + SHIFT + ENTER

performance wise there's little difference between a SUMPRODUCT and an Array (they're processed in the same way) ... though the latter requires CTRL + SHIFT + ENTER entry in this instance the syntax is more logical.

[The Dropper]

Mr/Mrs Donkey,

Thank you very much. This is the sort fo help I need; I will give it a try. Also what does 'confirmed with CTRL + SHIFT + ENTER' actually do and why is it necessary? why couldn't I just put the brackets around the formula manually?


Thank Again,
The Dropper

[DonkeyOte]

The use of CTRL + SHIFT + ENTER makes the formula an "Array" formula... for more info. see:

http://www.cpearson.com/excel/ArrayFormulas.aspx

There is also a link to Colin Legg's Array tutorial in my signature links.

Arrays are very powerful - they are also quite expensive performance wise (like SUMPRODUCT) so best to avoid en masse whenever and wherever possible - ie use in moderation.