How to count non-duplicate entries in a colume/row range?

[Shakil]

I have spreadsheets with duplicate data entries. Is there a function,
or can I built a function to count non-duplicate entries in a range?

For example, if a column has the following entries:

Column A
ABD
ABC
ADF
AED
ABC
AED

The function/formula should give a total count of 4 (there are total
of four unique entries).

Thanks.

 

[Dave Peterson]

one way:

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

 

[Alan Beban]

I have spreadsheets with duplicate data entries. Is there a function,
or can I built a function to count non-duplicate entries in a range?

For example, if a column has the following entries:

Column A
ABD
ABC
ADF
AED
ABC
AED

The function/formula should give a total count of 4 (there are total
of four unique entries).

Thanks.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=COUNTA(ArrayUniques(A:A)) if you *don't* consider ABD and, e.g., Abd as
duplicates; otherwise,
=COUNTA(ArrayUniques(A:A,FALSE))

Alan Beban

 

[Herbert Seidenberg]

one way:

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

another way:
=SUMPRODUCT(1/COUNTIF(A1:A99,A1:A99))

Dave: Can you give us a link to your operators <>"" and &""

 

[Dave Peterson]

<> just means "not equal to"
so
<>""
means that you're checking for non-blanks.

& is the concatenate operand.
&""
appends a null string to each cell's value.

The difference is how the two formulas treat blank cells.

Put some test data in a1:a99. Then edit|clear contents of one of the cells.

You'll see the difference.

 

[Shakil]

another way:
=SUMPRODUCT(1/COUNTIF(A1:A99,A1:A99))

Dave: Can you give us a link to your operators <>"" and &""

----------------------------------------------------------
=SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))

I tried the above formula and it worked. Thanks Dave. I do not know
what you mean by giving a link to operators. The data in my
spreadsheets is numbers and/or test, and sometimes alphanumeric. I do
have one followup questions.

If I used this formula on a selected range and then use AUTOFILTER
function, how can I modify it to count non-duplicates in the visible
cells only?

 

[Shakil]

The formula worked =SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))
But when I replaced A1:A99 with A:A to select coumn A as data range, I
get #NUM! error. Can you help?

Thanks.

 

[Dave Peterson]

First, the easy question: Herbert asked about links for those expressions--so
that response was meant for him.

Second is the difficult question (it's way beyond me!). But I googled and found
a post by Peo Sjoblom who posted a reply from Daniel Maher:

http://groups.google.com/[email protected]

Good luck.

 

[Dave Peterson]

=sumproduct() and explicitly entered array formulas (ctrl-shift-entered instead
of just enter) can't use all the rows.

You could use A2:a65536 or a1:a65535

or just use a number that will always be bigger than your last row. You have
3000 rows, then use A1:A10000.