index/match, Sumproduct and a dynamic list

Hi All

Can anyone help me with this, I need help with the following formulas....

1. I need a formula on the “summary” sheet (column A) which is returns unique customer numbers sorted smallest to largest. However I need it to be dynamic in that it looks up the last row automatically. Red highlighted cells
   
2. Once I have that list in place I need an index and match formula which would match the following on the summary sheet: customer number (column A), column number from the condition in cell A1, month number (cell A2) and the Forecast numbers (Row 3) with the corresponding data on the “Data” sheet. Green highlighted Cells. the added complication for this is that if "All" is selected in the month field (rather than an individual month) i would need all months returned if this is even possible?)
   
3. Formula 3 I think would be a sumproduct formula which is on the “individual sheet”. Here I need it to subtotal column L on the data sheet but I need it to match the conditions (customer, month and forecast) on the data sheet.

I am never the best at explaining what I need but I have uploaded a spreadsheet which shows what I need.

I know a pivot table can achieve this easier than formulas such as sumprouct and index/match however I want to avoid using them as this will form the database of my model

I know its cheeky asking for three complicated formulas but If you can help with any of the above formulas it would be appreciated!

Paul

Assuming you are using Excel 2007 as per your profile...

First create a Dynamic Named Range for your Data sheet.

While in Data sheet, go to Formulas, Define Name... enter name like: MyList and in the refers to field enter formula:

=Data!$A$2:INDEX(Data!$O:$O,MATCH(REPT("z",255),Data!$E:$E))

Then in Summary A4 enter formula:

=IFERROR(INDEX(INDEX(MyData,0,4),MATCH(LARGE(IF(ISNA(MATCH(INDEX(MyData,0,4),A$3:A3,0)),INDEX(MyData,0,4)),ROW($A2)-ROW($A$1)),INDEX(MyData,0,4),0),1),"")

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

Then in B4:

=IF(A4="","",VLOOKUP(A4,Data!$D:$E,2,0))

copied down

In C4:

=IF($A4="","",SUMPRODUCT((INDEX(MyData,0,4)=$A4)*(INDEX(MyData,0,6)=C$3)*(Data!$A$1:$O$1=$A$1)*(INDEX(MyData,0,11)=$A$2),MyData))

copied down and across the columns.

Sorry try this formula instead:

=IFERROR(INDEX(INDEX(MyData,0,4),MATCH(MAX(IF(ISNA(MATCH(INDEX(MyData,0,4),A$3:A3,0)),INDEX(MyData,0,4))),INDEX(MyData,0,4),0),1),"")

CSE confirmed and copied down

Not sure what you mean?

This part: (INDEX(MyData,0,11)=$A$2) includes the "Month" selection in A2... you can remove that part to get all the months...

Ok, I didn't know you had an "ALL" option in the dropdown...

Try:

=IF($A4="","",SUMPRODUCT((INDEX(MyData,0,4)=$A4)*(INDEX(MyData,0,6)=C$3)*(Data!$A$1:$O$1=$A$1)*(IF($A$2="ALL",INDEX(MyData,0,11)<>"",INDEX(MyData,0,11)=$A$2)),MyData))

try:

=SUMPRODUCT((INDEX(MyData,0,4)=$C$4)*(INDEX(MyData,0,7)=$C11)*(INDEX(MyData,0,11)=D$9),INDEX(MyData,0,12))

You are welcome. Glad I could help

Wouldn't it be just changing the MAX to MIN?

=IFERROR(INDEX(INDEX(MyData,0,4),MATCH(MIN(IF(ISNA(MATCH(INDEX(MyData,0,4),A$3:A3,0)),INDEX(MyData,0,4))),INDEX(MyData,0,4),0),1),"")

If customer number always are valid numbers, a non array,

A4, then copy down.

=IF(A3="","",IFERROR(SMALL(Data!D:D,1+COUNTIF(Data!D:D,"<="&A3)),""))

Since you are on Excel 2007, use SUMIFS instead of SUMPRODUCT.

In Summary C4, then copy down & across.

=IF($A4="","",SUMIFS(INDEX(Data!$A:$GZ,0,MATCH($A$1,Data!$A$1:$GZ$1,0)),Data!$D:$D,$A4,Data!$F:$F,C$3,Data!$K:$K,IF($A$2="All","*",$A$2)))

If you want to use your Dynamic Named Range with Haseeb's SUMIFS formula, then try:

=IF($A4="","",SUMIFS(INDEX(MyData,0,MATCH($A$1,Data!$A$1:$O$1,0)),INDEX(MyData,0,4),$A4,INDEX(MyData,0,6),C$3,INDEX(MyData,0,11),IF($A$2="ALL","*",$A$2)))

Yeah, those Sumproduct/Array formulas can be a B@$tch!

Do you mean?

=SUMIFS(INDEX(MyData, 0,12),INDEX(MyData,0,4),$C$4,INDEX(MyData,0,7),$C11,INDEX(MyData,0,11),D$9)

No problem. Glad to have helped.