Counting Only Once If The Entry Appears Multiple Times

Question

Hi,

 

Here's the situation.  I have a column in an Excel 2003 document with multiple number entries.  Each number entry appears more than once.  I want to count the number of unique entries in this column, meaning that if an entry appears more than once in the column, I only want to count it once.  Does anyone know how to do this because I really have no idea.

 

Thanks.

Answer 1

Hi,

Assuming values in column A enter this in column B and copy down.

=COUNTIF($A$2:A2,A2)=1

This will give you a  column of TRUE / FALSE, where the count of TRUE's is the unique count.

Answer 2

There are several ways to accomplish this. You could use a pivot table...

You could also use a formula on the worksheet...

 

Formula: =SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""),IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

 

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

Formula: =SUMPRODUCT((A1:A10<>"")/(COUNTIF(A1:A10,A1:A10)+(A1:A10="")))

 

or with VBA...

 

your list range = where your list is located

destination range = the below will copy the unique entries from the list to a new (and could be temporary) location

(I would have the destination be a named range - lets call it "dest")

 

Code Snippet

Range("your list range").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "dest"), Unique:=True

 

 

 

Then use the following to get a count of the items in the new list...

 

Code Snippet

Range("wherever you want it").FormulaR1C1 = "=SUM(dest)"

 

 

 

 

 

sorry Andy, I got a phone call after I started typing, didn't see that you replied when i got off.

Answer 3

No need for apology.

There is always more than 1 way to skin this cat called Excel

Answer 4

Thanks