Percentage of values in list

  • Thread starter Thread starter BigAl.NZ
  • Start date Start date
B

BigAl.NZ

Hi Guys,

I am a excel n00b so, please be gentle!!

I have a caolumn with a list of values of cars like:

Ford
Chrysler
Ford
Toyota
Mazda
Toyota
Ford
Ford

I want to create a new sheet that lists all possible values and for
each value the number of times it appears in the list.

In this case it would be:

Ford 4
Toyota 2
Chrysler 1

etc etc etc

Could someone please help on how to do it?

The cell range is about C4-C26000 and has about 100 different possible
values so its quite large.

Thanks in advance,

-Al
 
Let list be in A1:A25
In B1 enter the text Ford
Enter other makes below that
In C1 type =COUNTIF(A1:A25,B1)
Copy this down the column by dragging its fill handle - Little solid square
in right bottom cornet when cell is selected
best wishes
 
To get percentage change C1 to =COUNTIF(......)/COUNTA(A1:A25) and format as
%
cheers
 
Hi Guys,

I am a excel n00b so, please be gentle!!

I have a caolumn with a list of values of cars like:

Ford
Chrysler
Ford
Toyota
Mazda
Toyota
Ford
Ford

I want to create a new sheet that lists all possible values and for
each value the number of times it appears in the list.

In this case it would be:

Ford 4
Toyota 2
Chrysler 1

etc etc etc

Could someone please help on how to do it?

The cell range is about C4-C26000 and has about 100 different possible
values so its quite large.

Thanks in advance,

-Al
Click to expand...

Hi Al

First, select column C, then goto Data > Filter > Advanced Filter >
Check "Unique records only > OK

Copy the filtered range to the desired sheet.

If you have the original data in sheet1 and use sheet2 to count number
of appearances, with "Toyota, Ford etc." in A2 and down, enter this
formula in B2:

=countif(Sheet1!$C$4:$C$26000,A2)

Copy the down as needed.

Regards,
Per
 
Let list be in A1:A25
In B1 enter the text Ford
Enter other makes below that
In C1 type =COUNTIF(A1:A25,B1)
Copy this down the column by dragging its fill handle -  Little solid square
in right bottom cornet when cell is selected
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email















- Show quoted text -
Click to expand...

There is probably over 100 different possible values, thats quite a
bit of typing.....
 
Back
Top