Counting the number of different values in a list
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Counting the number of different values in a list

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Here's the problem:
    I have a long list of countries, many of which occur several times. I want a formula which will determine how many DIFFERENT coutries there are in the list.

    E.g:

    China
    Denmark
    Denmark
    Finland

    Total = 3

    It feels like it should be simple - but I can't do it! Thanks in advance...

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    538
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok. this is a long away..
    just incase it is urgent..its all i know off my head.

    select your fiels. then go to data/filter/advanced filter.. and filter by unique fields.
    then copy these fields to a new column and see how many rows there are.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-09 00:18, Saloomi wrote:
    Here's the problem:
    I have a long list of countries, many of which occur several times. I want a formula which will determine how many DIFFERENT coutries there are in the list.

    E.g:

    China
    Denmark
    Denmark
    Finland

    Total = 3

    It feels like it should be simple - but I can't do it! Thanks in advance...
    Array-enter:

    =SUM(IF(LEN(A2:A5),1/COUNTIF(A2:A5,A2:A5)))

    where A2:A5 houses the country data.

    In order to array-enter a formula you need to hit control+shift+enter, not just enter.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    538
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ALADIN...
    htats what i sort of meant...


  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Saloomi

    I would seriously consider a small Pivot Table instead.



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com