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

L

LYLERR

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
 
L

LYLERR

Thanks Jennifer

it worked great

Lyle


jennifer said:
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&""))
 
J

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.
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top