Counting Only Once If The Entry Appears Multiple Times

Tue, 17 Jun 2008 13:15:08 Z

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.

Counting Only Once If The Entry Appears Multiple Times

Tue, 17 Jun 2008 15:39:26 Z

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.

Counting Only Once If The Entry Appears Multiple Times

Tue, 17 Jun 2008 16:07:31 Z

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.

Counting Only Once If The Entry Appears Multiple Times

Tue, 17 Jun 2008 18:40:17 Z

No need for apology.

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

Counting Only Once If The Entry Appears Multiple Times

Wed, 18 Jun 2008 20:24:51 Z

Thanks