Counting Unique Values

[via135]

MR ALADIN!

you have not replied me to my earlier post!

again I am asking you to explain the worksheet function
"COUNTDIFF"???!!!!

-via135

The data in range A1:E12 including the range with concatenation and
headers:

{"Name1","Name2","Name3","Name4",0;
"xxx","yyy",10,"zzz","xxxyyy10zzz";
"xyz",0,20,"cascade","xyz20cascade";
"yzx","cab",10,"mno","yzxcab10mno";
"bac","def",30,0,"bacdef30";
"xyz","abc",20,"rst","xyzabc20rst";
"xyz","abc",10,"rst","xyzabc10rst";
"yzx","cab",10,"mno","yzxcab10mno";
0,0,0,0,"";
0,0,0,0,"";
0,0,0,0,"";
"wer","ewrt",879,"q","werewrt879q"}

The zeroes stand for empty cells.

1]

=SUMPRODUCT(--(E2:E12<>""),1/COUNTIF(E2:E12,E2:E12&""))

2]

{=SUM(IF(FREQUENCY(IF((A2:A12<>"")*(B2:B12<>"")*(C2:C12<>"")*(D212<>""),
MATCH(A2:A12&B2:B12&C2:C12&D212,A2:A12&B2:B12&C2:C12&D212,0)),
ROW(INDIRECT("1:30")))>0,1))}

3]

{=COUNTDIFF(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D212,,"")}

4]

=SUMPRODUCT(--((MATCH(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D212,
A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D212,0)
=ROW(INDEX(A2:A12,0,0))-ROW(A2)+1)))

[1] yields: 7, while [2] delivers: 5.

[3] and [4] both yield: 8.

You can download a sample here

http://www.nwexcelsolutions.com/Download/Unique values With a Twist.xls

 

[bplumhoff]

Hello,

Please look at http://xcell05.free.fr/ for COUNTDIFF.

It is not an Excel standard function but comes with the add-in
presented at that site.

HTH,
Bernd

 

[Aladin Akyurek]

MR ALADIN!

you have not replied me to my earlier post!

again I am asking you to explain the worksheet function
"COUNTDIFF"???!!!!

[...]

That's because Peo mentioned in his reply where you can get the add-in
that contains CountDiff.

Anyway, morefunc.xll is available at Longre's site: http://xcell05.free.fr/

It's free and comes with excellent functions.

 

[Peo Sjoblom]

My bad, stupid me I changed your original data and forgot to change it back,
if you replace the date in A2:A8 with your original data you'll get 5. When
I tested it I changed D4 from rst to "cascade", if you change back to "rst"
you'll get 6 (and if you remove line 12 you'll get 5)

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

Portland, Oregon

 

[Peo Sjoblom]

Data not Date, gee!

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

Portland, Oregon

 

[via135]

hi!

i'm not interested in add-ins bcoz add-ins always used to give problems
for the original!!!
anyway thks that atlast you have accepted that u r referring the
"COUNTDIFF" of the add-ins!!!

-via135

MR ALADIN!

you have not replied me to my earlier post!

again I am asking you to explain the worksheet function
"COUNTDIFF"???!!!!

[...]

That's because Peo mentioned in his reply where you can get the add-in
that contains CountDiff.

Anyway, morefunc.xll is available at Longre's site:
http://xcell05.free.fr/

It's free and comes with excellent functions.

 

[via135]

thks Peo!

i can understand ur clarification!!

-via135

 

[Aladin Akyurek]

hi!

i'm not interested in add-ins bcoz add-ins always used to give problems
for the original!!!
Fine.

anyway thks that atlast you have accepted that u r referring the
"COUNTDIFF" of the add-ins!!!

[...]

Re-read my original reply...