I'm trying to create a formula to count the occurrences of Barcodes in Sheet2 which is comma-separated in Sheet1, where I'd like to summarize the occurrences.

• Sheet1 is a product-list. A product can have multiple Barcodes (Column B).

• Sheet2 is a input of Barcodes which occurrences I'd like to count in Sheet1.

• Column C in Sheet1 is the Count-formula. For the example I've added a Countif() that I used until recently where a product now can have more then one Barcode.

Example:

CodePudding user response：

try:

=ARRAYFORMULA(IF(B2:B="",,MMULT(COUNTIF(Sheet2!A:A, SPLIT(B2:B10, ",")), 
 SEQUENCE(COLUMNS(SPLIT(B2:B10, ",")), 1, 1, 0))))