simple frequencies
 

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!

simple frequencies
 

Try using a Pivot Table. See:

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

for details
--
Gary's Student


"tom g" wrote:

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!

simple frequencies
 

=COUNTIF(A:A,"USA")

Or

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

"tom g" wrote:

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!

simple frequencies
 

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

tom g wrote:
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!

simple frequencies
 

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.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"tom g" wrote in message
ups.com...
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!

simple frequencies
 

Forgot to use absolute references in the Countif formula:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
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.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"tom g" wrote in message
ups.com...
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!