Counting

[et]

I have problem in counting the total without the duplicate.

For example,

Fruit
-------
Apple
Apple
Orange
Banana
Kiwi
Pear
Kiwi
Banana

I want to know if there is a formula in excel that can count how many fruits
are in the column. It shoud exclude the duplicate items.

Anybody can help. Thanks

Eling

 

[Domenic]

Try the following...

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

OR

=SUM(IF(A1:A8<>"",1/COUNTIF(A1:A8,A1:A8)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[et]

Wonderful. It works. Thanks so much.

But I still don't understand how it works.

 

[Domenic]

Wonderful. It works. Thanks so much.

You're very welcome! Glad I could help!

But I still don't understand how it works.

Let's assume that A1:A5 contains the following values...

{5;"";8;5;2}

Using the following formula...

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

....returns 3, representing the number of unique items. The evaluation
breaks down as follows...

(A1:A5<>"") returns the following array of TRUE and FALSE...

{TRUE;FALSE;TRUE;TRUE;TRUE}

COUNTIF(A1:A5,A1:A5&"") returns the following array of numbers...

{2;1;1;2;1}

Lastly, the evaluation for (A1:A5<>"")/COUNTIF(A1:A5,A1:A5&""), before
summing, returns the following array...

{0.5;0;1;0.5;1}

....which, when summed, returns 3. The array is a result of the
following...

TRUE/2 ---> 0.5
FALSE/1---> 0
TRUE/1 ---> 1
TRUE/2 ---> 0.5
TRUE/1 ---> 1

Note that 1 and 0 are the numerical equivalents of TRUE/FALSE.

Hope this helps!

 

[et]

Hi Domenic,

Thankyou for taking time to explain the function. I get the idea now. They
are two arrays, the later one shows the number of appearance within the
range, no matter how a item show up, the total end result of that item will
become 1. How could you think this out. Amazing.

Are u Canadian ? I am going to Canada this Friday for the vacation of
Chinese new year.

Happy Chinese new year.

Eling

 

[Domenic]

How could you think this out. Amazing.

I don't know who came up with this formula but, yes, it's definitely
ingenious!

Are u Canadian ?

Yes I am.

I am going to Canada this Friday for the vacation of
Chinese new year.

Well, I hope you enjoy your vacation here in Canada, and have a Happy
New Year!

Cheers!

 

[et]

Thankyou.

I don't know who came up with this formula but, yes, it's definitely
ingenious!


Yes I am.


Well, I hope you enjoy your vacation here in Canada, and have a Happy
New Year!

Cheers!