Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Counting unique entries

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

    Default

    My question is related to Excel 97 and is basically twofold.

    1) I want to count the number of unique occurences in a list. In the example below the count of both Column A & B would be 3.

    Column A
    Column B

    Item type
    Department

    A
    Accounts

    A
    Sales

    C
    Sales

    B
    Sales

    B
    Accounts

    B
    Admin

    C
    Admin

    A
    Sales



    2) Using the same example data, I want to count the number of departments that use the individual Item types.

    Required result being; Type A = 2 (Accounts & Sales)
    Type B = 3 (Accounts, Sales & Admin)
    Type C = 2 (Admin & Sales)

    I have given column B an Alpha entry as this is how I would like it idealy to be, but the departments could be referenced numerically if this would assist.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Test post!


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would advance filter and set the ranges..

    Now tick unique .. thats done ok and the worlds your oyster. This wil automatically show count in blue ie 10 of 15 records theese the count you requier (hope that right memory there) suggest to 3 filters 1 and release the B /// then c//

    1) I want to count the number of unique occurences in a list. In the example below the count of both Column A & B would be 3.

    Column A
    Column B

    Item type
    Department

    A
    Accounts

    A
    Sales

    C
    Sales

    B
    Sales

    B
    Accounts

    B
    Admin

    C
    Admin

    A
    Sales

    Agaim i suggest filter or advanced filter.

    HTH


    2) Using the same example data, I want to count the number of departments that use the individual Item types.

    Required result being; Type A = 2 (Accounts & Sales)
    Type B = 3 (Accounts, Sales & Admin)
    Type C = 2 (Admin & Sales)

    I have given column B an Alpha entry as this is how I would like it idealy to be, but the departments could be referenced numerically if this would assist.

    [/quote]
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    I'll assume that A1:B9 houses the sample data you provided:

    {"item type","department";
    "A","Accounts";
    "A","Sales";
    "C","Sales";
    "B","Sales";
    "B","Accounts";
    "B","Admin";
    "C","Admin";
    "A","Sales"}

    In C2 enter and copy down:

    =A2&"-@-"&B2

    Hide C if you so wish.

    In D2 enter: # Uniq Items
    In E2 array-enter:

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

    In D3 enter: # Uniq Deps
    In E3 array-enter:

    =SUM(IF(LEN(B2:B9),1/COUNTIF(B2:B9,B2:B9)))

    In D4 enter: A
    In D5 enter: B
    In D6 enter: C

    Note that D4:D6 houses a unique list of item types, which can be created with Advanced Filter.

    In E4 array-enter and copy down:

    =SUM(IF($A$2:$A$9=D4,1/COUNTIF($C$2:$C$9,$C$2:$C$9)))

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

    Aladin

Some videos you may like

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
  •