Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    ? function to count unique similar to CountA( )? (Excel 2003)

    I have a spreadsheet that is updated daily from an Access database. There is 1 summary sheet that lists some total numbers.
    1 of the total numbers list the number of lines ( =COUNTA('SpreadsheetTab'!A:A)-1 )
    (the -1 is so I don't include the header row in the count)

    What I want the other summary line to do is list the unique count of order numbers.

    Currently I have an Access query give me those results.
    What I would like to see if I can do is get this number simply within the spreadsheet.

    An example from today's report is I have 558 unique order numbers and 561 line items.

    Using my CountA example above, I do get the number of line items.

    Is there a similar function that will do the same, but only count unique values? The tab that is being looked at is sorted by the Order number, so any duplicates will be under each other.

    Thanks,

    John
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ? function to count unique similar to CountA( )? (Excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 06-Sep-05 14:40. added PS)</P>This is courtesy of Chip pearson at Counting Unique:
    =SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Ra nge1,0),""),IF(LEN(Range1)>0,MATCH(Range1,Range1,0 ),""))>0,1))

    it is an ARRAY formula and must be confirmed with ctrl-shift-enter.
    Steve
    Range1 is the range of interest. It can not be the entire column

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? function to count unique similar to CountA( )? (Excel 2003)

    Thank you for the link and example. After I supplied a limited range (but still exceeds what I should ever have there) instead of the entire column and starting from the 2nd row to skip the header row, this works great!

    Thanks again,
    John <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ? function to count unique similar to CountA(

    Some options,

    =SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))

    With the Morefunc addin

    =COUNTDIFF(A1:A30)

    For numeric only

    =SUM(IF(FREQUENCY(A:A,A:A)>0,1,0))

Posting Permissions

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