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 inSheet1
.Column C
inSheet1
is the Count-formula. For the example I've added aCountif()
that I used until recently where a product now can have more then one Barcode.
CodePudding user response:
try:
=ARRAYFORMULA(IF(B2:B="",,MMULT(COUNTIF(Sheet2!A:A, SPLIT(B2:B10, ",")),
SEQUENCE(COLUMNS(SPLIT(B2:B10, ",")), 1, 1, 0))))