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

1. ## ? 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

2. ## 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. ## 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>

4. ## Re: ? function to count unique similar to CountA(

Some options,

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

=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
•