Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Count unique entries? (XL97, WinNT4)

    Does anyone know if there is a way of counting the *unique* entries in a column? Sort of the opposite of using a filter ...

    I have a number of spreadsheets giving company names and authorised signatories for that company, which means each company name appears as many times as there are individuals who can sign on its behalf, and I need to know how many companies are there.

    Any help would be greatly appreciated!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Say your company names are in A1:A100.

    The following formula, entered as an array formula (confirm with Ctrl+Shift+Enter), will return the number of unique company names in this range:

    =SUM(1/COUNTIF(A1:A100,A1:A100))

    See Using Array Formulas in Excel OFC-10 for more information about this type of formula.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Wow, talk about quick service! Many thanks, Hans, I'll try that.

    I did think it might be something to do with one of the 'count' functions (although I was looking under 'subtotal') but I wasn't able to track it down!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> I must be doing something wrong, Hans, 'cos it didn't work! I got the '#DIV/0!' error. I do just replace both instances of A1:A100 with the actual range, don't I?
    Beryl M


  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Beryl,

    It has to be entered as an Array formula. After you type the formula in the cell press Crtl and Shift while pressing enter key.
    Have a Great Holiday Season everyone!
    And, yes you replace the A1:A100,A1:A100 with the actual range.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Yes, replace the example ranges with your ranges but it is *vital* to enter it as an array fomula (as Hans pointed out).

    That means type the formula then press ctrl shift enter and not just enter. If you get this right then the cell will display your formula inside curly brackets.

    (not) stuck

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count unique entries? (XL97, WinNT4)

    If there are empty values in your range, the 1/COUNTIF(...) will result in division by 0.

    For ranges with empty values, use this slightly more complicated formula:

    =SUM(IF(COUNTIF(A1:A100,A1:A100)=0,0,1/COUNTIF(A1:A100,A1:A100)))

    entered as an array formula (Ctrl+Shift+Enter) and with all instances of A1:A100 replaced by your range.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> Sorry, Hans - it would help if I actually read it properly! <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    Works perfectly once I use Ctrl-Shift-Enter!

    Just out of curiosity, though - what exactly is it doing, because I can't work out WHY this works, although it unquestionably does!

    Many thanks <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Better:

    =SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100)))

    again entered using control+shift+enter.

    Aladin
    Microsoft MVP - Excel

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Hi Hans,

    I don't think your solution for ranges with empty cells:
    =SUM(IF(COUNTIF(A1:A100,A1:A100)=0,0,1/COUNTIF(A1:A100,A1:A100)))
    works correctly. If I put:
    =MOD(ROW(),10)
    in cell A1 and copy this down to row 10, your formula returns 100, even though there are only 10 non-zero values. Copying it down to row 20, returns 50, whilst copying it down to row 30, returns 33.3333, and so on. In each case, there's only 10 unique values. Aladin's formula:
    =SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100)))
    does seem to work correctly, though.

    Cheers

    PS: This probably invalidates my solution for Troy Wells (see <post#=207221>post 207221</post#>) too, since his formula and my modification of it were based on a similar approach to yours. Ho hum.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Hi Bery,

    I'm glad it works now. The link in my first reply explains about exotic uses of array formulas. I've tried to give a short explanation (not very successful, I fear) of this particular formula in <post#=200801>post 200801</post#>.

    Please note Aladin Akyurek's suggestion - it is better than mine.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Hi Macropod,

    My formula returns incorrect results if the range contains zero values (whether as a constant or as the result of a formula). This shouldn't be a problem for Beryl (who started this thread) since she is counting company names. But in general, Aladin Akyurek's formula is to be preferred - it is short, and more important, it's correct.

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Count unique entries? (XL97, WinNT4)

    Hi Hans, thanks for the comments - however, by the time Aladin posted his solution, I had already used yours in some three dozen or so spreadsheets, so since, as you commented, the empty values are not a problem for me, I think I'll stick with what I've got!

    Merry Christmas and many thanks for all your help!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


Posting Permissions

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