+ Reply to Thread
Results 1 to 8 of 8

Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteria

  1. #1
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteria

    I have an inventory spreadsheet. Each entry lists an item code, a lot number, and a quantity. I'm trying to sum up the quantity for each unique [Lot#]*[Item] combination, preferably using a dynamic array because the base data set is large and prone to changing frequently. I can extract totals for each item or for each unique lot#, but some lot#'s are shared by multiple items. I've tried a couple different approaches with SUMIF(), UNIQUE(), XMATCH(), XLOOKUP(), etc. and I'm so close to an answer but I've been looking at it for far too long now and my brain is turning to mush. Id' greatly appreciate any help you have to offer.

    Sample.png

    BONUS QUESTION:
    CAn you suggest a cleaner approach to populating the tables to the right? I have two different approaches there, one with clonky normal references and one with a separate XLOOKUP in each column. I know there's a better way
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    I cut your sample down so I could do manual checking.

    H2:
    =INDEX(GPTbl[[STCKCNTID]:[Item Type]],MATCH(UNIQUE(GPTbl[Item]&" "&GPTbl[LOT]),GPTbl[Item]&" "&GPTbl[LOT],0),{1,2,3})

    K2:
    =LET(a,MATCH(UNIQUE(GPTbl[Item]&GPTbl[LOT]),GPTbl[Item]&GPTbl[LOT],0),SUMIFS(GPTbl[Qty],GPTbl[Item],INDEX(GPTbl[Item],a),
    GPTbl[LOT],INDEX(GPTbl[LOT],a)))

    L2, similar to H2.

    see sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    The forum keeps crashing, so no file attached at the moment. Thsi was the last formula:

    =INDEX(GPTbl[[STCKCNTID]:[Item Type]],MATCH(UNIQUE(GPTbl[Item]&" "&GPTbl[LOT]),GPTbl[Item]&" "&GPTbl[LOT],0),{5,6})

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    Finally... it gets attached.

    Rabk, also please amend your profile to shaow your approximate location (nation) and your current Excel version... clearly O365 either for PC or for Mac.
    Attached Files Attached Files

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    an alternative

    Please Login or Register  to view this content.
    edit: I figure you would need to adjust everything with this approach so, below would supercede above suggestion:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 12-10-2021 at 07:32 AM.

  6. #6
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,008

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    One cell formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    Quote Originally Posted by DJunqueira View Post
    One cell formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That looks amazing, but it's summing up everything for each lot number and not separating out items that share the same lot.

    Quote Originally Posted by Glenn Kennedy

    Finally... it gets attached.

    Rabk, also please amend your profile to shaow your approximate location (nation) and your current Excel version... clearly O365 either for PC or for Mac.
    That's perfect. You rock, man. And I've amended my profile - thanks for the advice.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteri

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 08-17-2021, 03:06 PM
  2. [SOLVED] XLOOKUP to return cell based on multiple criteria in single column
    By mikehay08 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2021, 02:59 PM
  3. SUMIF - Based On Unique Values and Multiple Criteria
    By CreationUK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2019, 03:19 PM
  4. [SOLVED] Creating a dynamic array to display multiple specific entries based on criteria
    By Travisty in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-15-2017, 09:43 AM
  5. [SOLVED] Creating a dynamic array to search for multiple specific entries based on criteria
    By Travisty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2015, 05:23 AM
  6. [SOLVED] Creating a dynamic array to search for specific entries based on multiple criteria
    By Travisty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2015, 12:30 AM
  7. Macro to lookup based on multiple criteria in dynamic data
    By buffalo83 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-25-2013, 09:44 PM

Tags for this Thread

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