|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#21
February 19th, 2006, 10:24 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
MR ALADIN! you have not replied me to my earlier post! again I am asking you to explain the worksheet function "COUNTDIFF"???!!!! -via135 Aladin Akyurek Wrote: 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"")*(C 2:C12"")*(D2  12""),MATCH(A2:A12&B2:B12&C2:C12&D2 12,A2:A12&B2:B12&C2 :C12&D212,0)),ROW(INDIRECT("1:30")))0,1))} 3] {=COUNTDIFF(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2 1 2,,"")}4] =SUMPRODUCT(--((MATCH(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2 12,A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2 12,0)=ROW(INDEX(A2:A12,0,0))-ROW(A2)+1))) [1] yields: 7, while [2] delivers: 5. [3] and [4] both yield: 8. Peo Sjoblom wrote: You can download a sample here http://www.nwexcelsolutions.com/Down...0a%20Twist.xls -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 
|
|
#22
February 19th, 2006, 11:10 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
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
|
|
#23
February 19th, 2006, 12:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
via135 wrote: 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.
|
|
#24
February 19th, 2006, 05:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
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 "via135" wrote in message ... i'm afraid Peo! that your sample download gives the result as "7" i/o "5"! while your earlier post throws the correct result of "5"!! maybe bcoz of extending the range upto row31 in your sample download??!! -via135 Peo Sjoblom Wrote: You can download a sample here http://www.nwexcelsolutions.com/Down...0a%20Twist.xls -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "via135" wrote in message ... sorry Peo! I'm getting #VALUE! error! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331
|
|
#25
February 19th, 2006, 05:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
Data not Date, gee!
-- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com Portland, Oregon "Peo Sjoblom" wrote in message ... 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 "via135" wrote in message ... i'm afraid Peo! that your sample download gives the result as "7" i/o "5"! while your earlier post throws the correct result of "5"!! maybe bcoz of extending the range upto row31 in your sample download??!! -via135 Peo Sjoblom Wrote: You can download a sample here http://www.nwexcelsolutions.com/Down...0a%20Twist.xls -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "via135" wrote in message ... sorry Peo! I'm getting #VALUE! error! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331
|
|
#26
February 19th, 2006, 06:37 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
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 Aladin Akyurek Wrote: via135 wrote: 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 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331
|
|
#27
February 19th, 2006, 06:42 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
thks Peo! i can understand ur clarification!! -via135 Peo Sjoblom Wrote: Data not Date, gee! -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com Portland, Oregon "Peo Sjoblom" wrote in message ... 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 "via135" wrote in message ... i'm afraid Peo! that your sample download gives the result as "7" i/o "5"! while your earlier post throws the correct result of "5"!! maybe bcoz of extending the range upto row31 in your sample download??!! -via135 Peo Sjoblom Wrote: You can download a sample here http://www.nwexcelsolutions.com/Down...0a%20Twist.xls -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "via135" wrote in message ... sorry Peo! I'm getting #VALUE! error! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331
|
|
#28
February 19th, 2006, 09:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values
via135 wrote: 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...
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| counting unique values in report footers | Sarah | Setting Up & Running Reports | 7 | February 9th, 2006 04:29 AM |
| Unique values | travis | Worksheet Functions | 2 | January 16th, 2006 12:22 AM |
| counting unique values and matching to quantities | [email protected] | Worksheet Functions | 2 | September 9th, 2005 11:25 PM |
| Unique Values vs Unique Records | Miaplacidus | Running & Setting Up Queries | 1 | September 17th, 2004 08:24 PM |
| Counting Unique Values | Emma Hope | Worksheet Functions | 4 | May 9th, 2004 11:40 PM |
Linear Mode
