Count Function?

[booYEAH]

I have a column of say 250 entries. Of those 250 entries, a portion, say 100 are duplicates. How do I count specific entries, but not count the duplicates? Thanks.

 

[Domenic]

Try...

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

Hope this helps!

 

[August]

This is great for something I'm using
Ive tried breaking down the formula to understand it more, but I'm not really getting anywhere
Could explain the parts of the formula a little?

Many thanks

 

[Domenic]

This is great for something I'm using
Ive tried breaking down the formula to understand it more, but I'm not really getting anywhere
Could explain the parts of the formula a little?

Many thanks

Let's assume that A1:A8 contians the following values...

A
A
B

A
B

C

...and that we have the following formula...

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

(A1:A8<>"") evaluates to:

{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

COUNTIF(A1:A8,A1:A8&"") evaluates to:

{3;3;2;2;3;2;2;1}

Then, SUMPRODUCT divides the two evaluations...

{0.333333333;0.333333333;0.5;0;0.333333333;0.5;0;1}

...and sums the results, returning 3. Note that the numerical equivalent of TRUE/FALSE is 1/0, respectively.

Hope this helps!

 

[August]

Brilliant yet simple - How cool is that!!!!!!!
Yet depressing for those of us who would never have worked it out.
Back to the books I think

Thanks

 

[Pekkavee]

Cool

I came here from a new question.

http://www.mrexcel.com/board2/viewtopic.php?t=257393&start=0

These "bigger than life" answers should be stored in some MrExcel golden globe bank.

Pekka

 

[Pekkavee]

Here is the original link.
http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=grove

The original answer came from Aladin Akyurek.

He is the best.

MrExcel should have an own Golden Globe Bank for Aladin only.

Pekka