Results 1 to 3 of 3

Thread: Match & Countifs (probably ..)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Match & Countifs (probably ..)

    I've been struggling with a formulae (having tried various connotations of COUNTIFS) to first:

    - count the number of matches per item (an item can occur multiple times in the Master list) on an extensive list but that is further restricted to only those whose characteristic of interest has to be present on another controlling list (so again it must be an array check rather than simply picking out simple singular matches).

    .. and then of course to list these "characteristics of interest" either using TEXTJOIN or across a row using AGGREGATE.

    Any tips / ideas please ??

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match & Countifs (probably ..)

    So I've finally managed to get something working for the first part:

    Code:
    
    =SUMPRODUCT(--(lx02_lang[Material]=[@MATERIAL]),IFNA(SIGN(MATCH(lx02_lang[StorageBin],TBL_loc[BIN],0)),0))
    
    .. however I'm not convinced it's the fastest or most elegant solution.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: Match & Countifs (probably ..)

    Taken a fresh look this morning, and whilst the above formula works fine along side:

    Code:
    =IF(COLUMNS($R58:R58)>[@LOCS],"",INDEX(lx02_lang[StorageBin],AGGREGATE(15,6,(ROW(lx02_lang[StorageBin])-ROW(lx02_lang[[#Headers],[StorageBin]]))/((lx02_lang[Material]=[@MATERIAL])*(MATCH(lx02_lang[StorageBin],TBL_bin[BIN],0)>0)),COLUMNS($R58:R58)),0))
    .. to extract the required data, I cannot understand why the following doesn't also work as a counter:

    Code:
    =COUNTIFS(lx02_lang[Material],[@MATERIAL],ifna(SIGN(MATCH(lx02_lang[StorageBin],TBL_loc[BIN],0)),0),1)
    Any comments/thoughts would be appreciated.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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