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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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&""))
Click to expand...
 
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.
 
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
 
Back
Top