+ Reply to Thread
Results 1 to 24 of 24

index/match, Sumproduct and a dynamic list

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    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
    Attached Files Attached Files
    Last edited by pauldaddyadams; 05-22-2012 at 08:03 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    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:

    Please Login or Register  to view this content.
    Then in Summary A4 enter formula:

    Please Login or Register  to view this content.
    This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

    Then in B4:

    Please Login or Register  to view this content.
    copied down

    In C4:

    Please Login or Register  to view this content.
    copied down and across the columns.
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    Hi NBVC - Thank you for replying.

    The list on the "Summary" sheet only has 14 customers, instead of 20 - do you know why this would be?

    is there any way in which if the user selects "All" from the drop down box in cell A2 all the months combined could be displayed?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    Sorry try this formula instead:

    Please Login or Register  to view this content.
    CSE confirmed and copied down

  5. #5
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    Excellent - thanks NBVC!

    Do you know of a way I can include all the months together or is this not possible?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    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...

  7. #7
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    I need the option to be able to select an individual month or be able to select all months together (1/001.2013 to Z1/012.2013) is this possible?

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

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

    Try:

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    This worked great!! You have saved me hours of watching/reading tutorials on how to achieve this.

    If you get a chance are you able to help me in any way with the formula on the individual tab which pulls data from the "data" sheet also please?

    I have re-uploaded the file with the working formulas
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    try:

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

  11. #11
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    NBVC You have been sooo helpful today! Thank you!

    This has solved my post!

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    You are welcome. Glad I could help

  13. #13
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    Hi.

    This is very minor but is there any way in which this can be adapted:
    Please Login or Register  to view this content.
    so it sorts the numbers smallest to largest? At the moment its largest to smallest

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

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

    Please Login or Register  to view this content.

  15. #15
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: index/match, Sumproduct and a dynamic list

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

    A4, then copy down.

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  16. #16
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    Thanks, changing Max to min worked - I coldnt see the max function in the formula (its late and time for bed I think lol)

    Thanks Haseeb A also for the help

    One problem I am having is the time in which it takes to calculate - would using a non array formula be quicker or is there nothing in it?

    Any other way I can speed it up, I have A LOT of formulas??

  17. #17
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: index/match, Sumproduct and a dynamic list

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

    In Summary C4, then copy down & across.

    Please Login or Register  to view this content.

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

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

    Please Login or Register  to view this content.

  19. #19
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    I have used the dynamic sumif formula and its has drastically increased the calculation speed from 30mins to 2 mins!!

  20. #20
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

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

  21. #21
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    Hi,

    Can you help me with changing the formula on the "individual" sheet to a SUMIF please?

    Please Login or Register  to view this content.

  22. #22
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    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)

  23. #23
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: index/match, Sumproduct and a dynamic list

    Perfect - Thanks NBVC for all your help these last two days

  24. #24
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: index/match, Sumproduct and a dynamic list

    No problem. Glad to have helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1