How to count a value from a cell which contains multiple values?

Question

I have a table with cells which contain comma-separated multiple values as follows:

+----------+
| Column A |
|----------|
| a, b     |
|----------|
| a, b, d  |
|----------|
| b, c     |
|----------|
| c, d, e  |
+----------+

I would like to get a table which counts the number of a, b, ..., e as follows:

 +-----------------------+
 | Column A  | Column B  |
 |-----------------------|
 |     a     |     2     |
 |-----------------------|
 |     b     |     3     |
 |-----------------------|
 |     c     |     2     |
 |-----------------------|
 |     d     |     2     |
 |-----------------------|
 |     e     |     1     |
 +-----------------------+

Is there any Google Sheets or LibreOffice Calc spreadsheet formula to do this?

Answer 1

• C1: =UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN(" ,",1,A:A)," ,")))

• D1: =ARRAYFORMULA(IF(LEN(C:C),COUNTIF(A:A,"=*"&C:C&"*"),))