Count all element occurences in list in cell(by list I mean a string with seperators) in google docs

Question

I have two tables

Table 1:

  | list_of_ids |
1 | 1; 2        |
2 | 3           |
3 | 4; 5; 1     |

Table 2:

  | id | count |
1 | 1  | 2     |
2 | 2  | 1     |
3 | 4  | 1     |
4 | 6  | 0     |

how do I automatically fill in count?

I currently have a very unsatisfying and hacky way of doing it like this:

• list_of_ids is formatted like ;id;id;id; instead of id; id; id
• count =COUNTIF(range, "*"&";"&id_cell&";"&"*")

is there a way to do it more properly, so to not force users to write ids in such a way?

Answer 1

try:

=INDEX(QUERY(FLATTEN(SPLIT(TEXTJOIN(";"; 1; A:A); ";")); 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''"))

---

or:

=ARRAYFORMULA(IF(C2:C="";;IFNA(VLOOKUP(C2:C; 
 QUERY(FLATTEN(SPLIT(TEXTJOIN(";"; 1; A:A); ";")); 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''"); 2; 0); 0)))