Results 1 to 6 of 6

Thread: Sumifs/Index/Match/Indirect Combo Formula

  1. #1
    Neophyte MJGriggs's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Sumifs/Index/Match/Indirect Combo Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please see attached file with more notes - for example of much much larger data set that is very complicated in design. The idea is to create a monthly sum - for a dynamic set of 3 months - that will sum the correctly matched column for just the dates that fall in the month/year (driven from another sheet) and product matchup for that record.
    i.e for the row highlighted red; the lookup/match is to the BRIST worksheet (dynamic sheet name based on column I), for the months of, in this case, Oct/Nov/Dec, sum day values up by month the values where the column headers in Columns BY:CB match the value in Col H of the orig worksheet. Is this even possible? I've been trying various iterations of sumif/sumproduct/index/match/indirect... with no success. Am in a new job at a new company and this is my first project so any help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Neophyte MJGriggs's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016
    The closest I've come is:
    =SUMPRODUCT(INDEX(INDIRECT($I8&"!$BY:$CB"),MATCH(Sheet2!$K$5,INDIRECT($I8&"!$DA:$DA"),0),MATCH(Sheet2!$H8,INDIRECT($I8&"!$BY$3:$CB$3"),0)))
    but it is not summing, only giving the first occurrence.

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,280
    Articles
    0
    Excel Version
    Office 365 Subscription
    CROSS POST

    This question has been posted in at least one other forum.
    Do not post any further responses in this thread until a link has been provided to these cross posts.

    Whilst we would rather you didn't cross-post, we know that there may be a valid reason for doing so. All we ask is that you provide the links in your original post to your question in the other forum(s). Read this to understand why: https://www.excelguru.ca/content.php?184

    This forum is full of volunteers that give up their own time to help others, something that should be respected and not taken for granted. It’s never nice for someone to find out that a problem they have spent time solving for you has already been answered somewhere else without them knowing, and so we ask you to make it clear that you have also asked elsewhere.
    Please update your original post to include the link(s).
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Neophyte MJGriggs's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Again - apologies for the cross-threading. If I could delete the thread I would.

  5. #5
    Neophyte MJGriggs's Avatar
    Join Date
    Sep 2018
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Please do not respond to this thread at is was cross-posted on another forum. My apologies.

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,280
    Articles
    0
    Excel Version
    Office 365 Subscription
    You do not need to delete this thread or ask people not to respond. You just need to be honest and provide a link to the other forum where you've asked this question, please.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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