simple frequencies

[tom g]

I have to calculate simple frequencies of unique values in a column.

For example


USA
China
France
USA
Norway
UK
China
UK
China


I need to create a list with the frequency of how often each vaule
appears in that list.

i.e.
China 3
France 1
Norway 1
UK 2
USA 2

Thanks!

 

[Guest]

Try using a Pivot Table. See:

http://www.contextures.com/xlPivot01.html

for details

 

[Guest]

=COUNTIF(A:A,"USA")

Or

=COUNTIF(A:A,B1) where B1="USA"

 

[Pete_UK]

You could obtain a list of the unique values by highlighting the column
of data (including the header), then click Data | Filter | Advanced
Filter - click Unique values and Copy List Elsewhere (specify $C$1)
then click OK. Then you could use a formula like the one Toppers has
given (although using C instead of B).

Hope this helps.

Pete

 

[Ragdyer]

With your data list in A1 to A200, in B1 enter:

=A1

In B2, enter this *array* formula:

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",INDEX(IF(ISBLANK($
A$1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)))

And drag down to copy.

In C1, enter this formula:

=COUNTIF(A1:A200,B1)

And drag down to copy.

 

[Ragdyer]

Forgot to use absolute references in the Countif formula:

=COUNTIF(A$1:A$200,B1)