Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Counting not Sub-totals (office 97 and 2002)

    Hi There

    I have a spreadsheet which has 1200 names some of which some are repeated many times, I need to count the names but only one of each,
    I have tried subtotals and then count the names but I don't seem to get the correct results. is there a better or another way to do this.

    Any help gratefully received
    If you are a fool at forty, you will always be a fool

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting not Sub-totals (office 97 and 2002)

    Brad, Use Data | Advanced Filter | Unique Records to copy the unique items to another location, then use the COUNTA function to count them. See Microsoft's example.. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Counting not Sub-totals (office 97 and 2002)

    Another option is a macro that counts the unique items in a named range. I am attaching a sample workbook. You can modify the macros to do something based on whether you wish to continue or no. This example is based on a tip by J.G.Hussey, published in "Visual Basic Programmer's Journal"
    Just another option....

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

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

    Re: Counting not Sub-totals (office 97 and 2002)

    In addition to using Advanced Filter in combination with COUNTA, you could use:

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

    which is a famous array-formula that counts unique items in a range.

    Note. To enter an array-formula, you need to hit control+shift+enter at the same time, not just enter.

    Aladin
    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Counting not Sub-totals (office 97 and 2002)

    Hi SammyB

    That worked fine I could easily see some spelling discrepancies which were easy to correct

    Thanks again
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Counting not Sub-totals (office 97 and 2002)

    Hi Aladin

    Thanks very much for your suggestion, I tried them all with great success

    Thanks again
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Counting not Sub-totals (office 97 and 2002)

    Hi Reimer

    Brilliant suggestion I loved it

    Thanks
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you are a fool at forty, you will always be a fool

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting not Sub-totals (office 97 and 2002)

    How does the LEN(A2:A10) bit of your formula work?

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

    Re: Counting not Sub-totals (office 97 and 2002)

    Michael,

    {=SUM((1/COUNTIF(A2:A10,A2:A10)))}

    will error out when A2:A10 has cells that are empty or has formulas that compute "" as result.

    Try to apply it to a sample like

    {"a";"";"w";"q";"a";"a";"w";"";"q"}

    where "" stands for a "blank cell".

    Note. You can put in A9 something like =IF(1,"","M") to get a computed blank.

    The IF(LEN(A2:A10) bit in

    {=SUM(IF(LEN(A2:A10),1/COUNTIF(A2:A10,A2:A10)))}

    on the other hand will filter out cells that are empty as well as cells that house computed ""
    so that the essential part of the formula can compute the desired count.

    Note. We can't just substitute ISBLANK(A2:A10) for LEN(A2:A10) for ISBLANK, as expected, will not filter out cells that house a computed "".

    Aladin
    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting not Sub-totals (office 97 and 2002)

    Thanks Aladin
    I'd worked out it was to deal with blank cells, but I was looking at LEN(A2:A10) as a non-array formula, where it does not do anything useful;clearly it only works in an array formulas. Very ingenious.

Posting Permissions

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