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

user_19571322 0
Viewed 26

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: https://docs.google.com/spreadsheets/d/1jYMc1Z_x7ZX4jUSo49Hto4rxV1MyPqfUsr9F8VvaKZ0/edit?usp=sharing

google-sheets-formula google-sheets
2 Answers

You can use the following

=ArrayFormula(SUM(COUNTIF(Sheet2!A:A,SPLIT(B2,","))))

enter image description here

user_15277800

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

user_56326290
Related