Search code examples
excelcountcell

Excel Formula || How to count occurrences of a value in column

Need some help in figuring out an formula to count the number of times a value is listed in a column. I will try and explain the requirement below.

The below image show sample of data set.

Data Set

The requirement is to list out issues and actions per customer.
As you can see, even from values clustered in cell, we need to find out individual unique values and then map it against the adjacent column or columns.

Clustered data

Solution

  • It just need an extra sheet/table to execute..

    try : 

    A1 = a,b,c
A2 = b,c
A3 = c,b,a
A4 = c,a
A5 = b
B1 = ss
B2 = ss
B3 = dd
B4 = dd
B5 = ss
D1 = a
E1 = b
F1 = c
C7 = ss
C8 = dd

D2 =IF(FIND(D$1,$A2,1)>0,1,"") drag until F6

D7 =COUNTIFS($B$2:$B$6,$C7,D$2:D$6,1) drag until F8

    D7:F8 will be your desired results. Happy trying.