How do I set up a formula to count only unique items in a column?

[Guest]

For Example: If I have a column with 8 items but two are duplicates. I want
the formula to return a value of only the unique items (6)

ABC
DEF
GHI
JKL
MNO
PQR
ABC
DEF

 

[Guest]

Thanks Jennifer

it worked great

Lyle

I take no credit; Not my creation:

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

 

[JDub]

Do you know how to display the unique value in a cell (ABC, DEF, GHI, JKL,
MNO or PQR) instead of count the unique items?

For example in a separate column, rows 1 - 6 will display ABC, DEF, GHI,
JKL, MNO and PQR but in rows 7 and 8, ABC and DEF not be duplicated...will be
blank.

 

[Ashish Mathur]

Hi,

You may use Advanced Filters. Select the range including the header row.
Now go to Data > Filter > Advanced Filter and select Copy to another
location. in the list range, specify the range of cells (incl. the header
row) and leave the criteria range blank. In the copy to box, select any
blank cell and check the box for unique records only.

Hope this helps./

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com