Match & Countifs (probably ..)

[moocifer]

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

 

[moocifer]

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

[COLOR=#b22222]
[/COLOR][SIZE=4][COLOR=#008000]=SUMPRODUCT(--(lx02_lang[Material]=[@MATERIAL]),IFNA(SIGN(MATCH(lx02_lang[StorageBin],TBL_loc[BIN],0)),0))
[/COLOR][/SIZE]

.. however I'm not convinced it's the fastest or most elegant solution.

 

[moocifer]

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

[COLOR=#008000][SIZE=4]=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))[/SIZE][/COLOR]

.. to extract the required data, I cannot understand why the following doesn't also work as a counter:

[SIZE=4][COLOR=#800000]=COUNTIFS(lx02_lang[Material],[@MATERIAL],ifna(SIGN(MATCH(lx02_lang[StorageBin],TBL_loc[BIN],0)),0),1)[/COLOR][/SIZE]

Any comments/thoughts would be appreciated.