Frequency table for comma-separated list in cells

awaw

New Member
Joined
Nov 3, 2011
Messages
2
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
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

Board Regular
Joined
Aug 16, 2005
Messages
70
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

New Member
Joined
Nov 3, 2011
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top