How to count comma separated values in excel

Question

I have a column in excel which stores single and comma separated values. I want co count the occurrence of each value in the column even when they are in comma separated cell.

E.g

Column A
----------
1. 1
2. 2
3. 2
4. 1 
5. 3
6. 1,2,3
7. 1,3
8. 1

I want to count number of "1" , "2", "3" in Column A irrespective of the format

When I used the Countif(A1:A8, "1") formula, it returned 3 where I was expecting 5 including the "1" in row 6 & 7.

How do I count the values in row 6 & 7 with other values?

BigBen BigBen · Accepted Answer · 2020-07-08T13:13:04

I'd use SUMPRODUCT here to add commas before and after the source data, and then look for ,1, using SEARCH:

=SUMPRODUCT(--ISNUMBER(SEARCH(",1,",","&A1:A8&",")))

How to count comma separated values in excel

1 Answers