Frequency table for comma-separated list in cells

[awaw]

Hi there,

I have a data set containing the numbers 1-8 with each representing a particular variable. I would like to determine the frequency of each variable. I can do this if there is only one variable in each cell. However, some cells contain multiple variables separated by a comma.

For example, if I have a column of data that looks like this:

Z2 1,5,6
Z3 2,4
Z4 1,4
Z5 4,5

I want to generate a list like this:

1= 2
2= 1
3= 0
4= 3
5= 2
6= 0
7= 0
8= 0

I have tried using the formula =countif(z2:z5;1) for each variable, but it doesn't work when there are multiple variables in a single cell. Any ideas??

Thanks

 

[Magriza]

Hi and welcome to the board,

The easiest way would be to use Text to Columns to separate each number into a cell. You'd then be able to do a COUNTIF on the range to determin the number of times each number appears.

Go to Data and then click on Text to Columns and choose comma as the separator.

 

[JimmyG]

Assuming your search values 1-8 are in cells A2 to A9, use the following
CTRL SHIFT ENTER ("Array") formula in cell B2 and drag down to B9:


{=SUM(IF(ISERROR(SEARCH(A2,$Z$2:$Z$6)),,1))}

 

[awaw]

Thanks a lot for the replies. I will try both methods and see which works best for the data I'm working with. Thanks again for the help.