Home > Software engineering >  Countif() if any of the comma-separated values in Sheet1(A1) appears in Sheet2(A:A)
Countif() if any of the comma-separated values in Sheet1(A1) appears in Sheet2(A:A)

Time:07-18

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: enter image description here

CodePudding user response:

try:

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

enter image description here

  • Related