|
Counting Unique Values
Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
One way.
enter =count(E2:E1002) in cell E1 select the range in question go to ....data....filter....advanced filter....copy to another location select E2 tick unique records only............. no need for a criteria range Greetings from New Zealand Bill K "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
Bob, I check this news group frequently as a means to learn stuff.
Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
I would personally use the variant
=SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
Thanks Peo,
When I only enter = 1/COUNTIF(A1:A1000,A1:A1000&"") the answer is 0.5 I suppose it is showing me the most frequent occuring in the array?? Thanks again, excellent formula Bill Kuunders "Peo Sjoblom" wrote in message ... I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
Actually, the 0.5 is the first value in the array, meaning that whatever is
in A1 occurs twice in the range select B1:B10, with B1 as the active cell click in the formula bar and put that formula, now enter it with ctrl + shift & enter, now if you sum B1:B10 you'll get the total of uniques, that is how array formulas can work -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Thanks Peo, When I only enter = 1/COUNTIF(A1:A1000,A1:A1000&"") the answer is 0.5 I suppose it is showing me the most frequent occuring in the array?? Thanks again, excellent formula Bill Kuunders "Peo Sjoblom" wrote in message ... I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
From the OPs post there was a full set of data, so in his case there was no
need to handle blanks. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333 } still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
Bill,
I had a go at explaining it a while back in http://tinyurl.com/dhbxe. This explanation is based upon the version that caters for blanks, but the fundamental principle is the same. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
Thank You Both
Peo and Bob I appreciate your time and effort very much. Bill Kuunders "Bob Phillips" wrote in message ... Bill, I had a go at explaining it a while back in http://tinyurl.com/dhbxe. This explanation is based upon the version that caters for blanks, but the fundamental principle is the same. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
hi Peo! for a single column of data the formula is ok! can i use the same formula for data with more than one column with duplicates? for example col"a" col"b" col"c" col"d" xxx yyy 10 zzz xyz abc 20 rst yzx cab 10 mno bac def 30 xyz xyz abc 20 rst xyz abc 10 rst yzx cab 10 mno -now i want to count the number of records excluding the duplicates! in the above example 2nd & 5th, 3rd & 6th are duplicates. if i make a count of total records without repetition, i must a get an answer of 5 ie.(7-2) Peo Sjoblom Wrote: I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message ... =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 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 |
Counting Unique Values
1] If you download & install the morefunc.xll add-in:
=COUNTDIFF(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,,"" ) which needs to be confirmed with control+shift+enter, not just with enter. 2] With built-in functions: =SUMPRODUCT(--((MATCH(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,A1:A7& "#"&B1:B7&"#"&C1:C7&"#"&D1:D7,0)=ROW(INDEX(A1:A7,0 ,0))-ROW(A1)+1))) via135 wrote: hi Peo! for a single column of data the formula is ok! can i use the same formula for data with more than one column with duplicates? for example col"a" col"b" col"c" col"d" xxx yyy 10 zzz xyz abc 20 rst yzx cab 10 mno bac def 30 xyz xyz abc 20 rst xyz abc 10 rst yzx cab 10 mno -now i want to count the number of records excluding the duplicates! in the above example 2nd & 5th, 3rd & 6th are duplicates. if i make a count of total records without repetition, i must a get an answer of 5 ie.(7-2) Peo Sjoblom Wrote: I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.3333333333333 33;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message . .. =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
hi Mr Aladin! as far as i know i never heard abt the worksheet function "COUNTDIFF" in XL! COULD YOU PL KINDLY EXPLAIN ME ABT THAT???!!!! -via135 Aladin Akyurek Wrote: 1] If you download & install the morefunc.xll add-in: =COUNTDIFF(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,,"" ) which needs to be confirmed with control+shift+enter, not just with enter. 2] With built-in functions: =SUMPRODUCT(--((MATCH(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,A1:A7& "#"&B1:B7&"#"&C1:C7&"#"&D1:D7,0)=ROW(INDEX(A1:A7,0 ,0))-ROW(A1)+1))) via135 wrote: hi Peo! for a single column of data the formula is ok! can i use the same formula for data with more than one column with duplicates? for example col"a" col"b" col"c" col"d" xxx yyy 10 zzz xyz abc 20 rst yzx cab 10 mno bac def 30 xyz xyz abc 20 rst xyz abc 10 rst yzx cab 10 mno -now i want to count the number of records excluding the duplicates! in the above example 2nd & 5th, 3rd & 6th are duplicates. if i make a count of total records without repetition, i must a get an answer of 5 ie.(7-2) Peo Sjoblom Wrote: I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.3333333333333 33;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message . .. =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 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 |
Counting Unique Values
It's from an add-in that can be downloaded here
http://xcell05.free.fr/ name is Morefunc OTOH you can easily get this using the subtotal function and advanced filter, assume that your sample data included headers are in A1:D8, in let's say E1 put =SUBTOTAL(3,A2:A8) will return 7, not select the table (click one cell and then press ctrl and * do datafilteradvanced filter, select unique entries only, click OK, check the subtotal formula which now has changed to 5. to take off the filter datafiltershow all another way, in column E in cell E2 put =A2&B2&C2&D2 copy down to E8, then use the original formula on this help column =SUMPRODUCT(--(E2:E8<""),1/COUNTIF(E2:E8,E2:E8&"")) returns 5 HTH -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "via135" wrote in message ... hi Mr Aladin! as far as i know i never heard abt the worksheet function "COUNTDIFF" in XL! COULD YOU PL KINDLY EXPLAIN ME ABT THAT???!!!! -via135 Aladin Akyurek Wrote: 1] If you download & install the morefunc.xll add-in: =COUNTDIFF(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,,"" ) which needs to be confirmed with control+shift+enter, not just with enter. 2] With built-in functions: =SUMPRODUCT(--((MATCH(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,A1:A7& "#"&B1:B7&"#"&C1:C7&"#"&D1:D7,0)=ROW(INDEX(A1:A7,0 ,0))-ROW(A1)+1))) via135 wrote: hi Peo! for a single column of data the formula is ok! can i use the same formula for data with more than one column with duplicates? for example col"a" col"b" col"c" col"d" xxx yyy 10 zzz xyz abc 20 rst yzx cab 10 mno bac def 30 xyz xyz abc 20 rst xyz abc 10 rst yzx cab 10 mno -now i want to count the number of records excluding the duplicates! in the above example 2nd & 5th, 3rd & 6th are duplicates. if i make a count of total records without repetition, i must a get an answer of 5 ie.(7-2) Peo Sjoblom Wrote: I would personally use the variant =SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&"")) otherwise you'll get DIV/0 errors if there are blank cells, it works as follows the 1/countif part returns an array of numbers, if there is one value unique it will return 1, if there are 2 values that are the same it will return 2 times 0.5 (1/2 = 0.5), if 3 it will return 0.333333, 4 0.25 and so on assume we have this in A1:A10 1 2 3 4 65 6 1 2 3 4 it would be 6 unique values, the 1/countif returns {0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5} sumproduct will sum them to return 6, if we change the last number 4 to 1 so there would be 3 1 {0.333333333333333;0.5;0.5;1;1;1;0.3333333333333 33;0.5;0.5;0.333333333333333} still returns the total of 6 I believe former MVP Dave Hager was the originator of it although it has been converted from =SUM(1/COUNTIF)) to sumproduct thus it can be entered normally -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Bill Kuunders" wrote in message ... Bob, I check this news group frequently as a means to learn stuff. Could you please explain why and how your formula works? Thank You -- Greetings from New Zealand Bill K "Bob Phillips" wrote in message . .. =SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "RJL0323" wrote in message ... Hello All, I have a question related to counting unique values in a column of data. I will try to illustrate my question. I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements, but the function to calculate the number of unique values has really got me stumped. Does anyone have any ideas? Thanks in advance!! RJ -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 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 |
Counting Unique Values
thks Peo! is there not any other way without the helper column? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
Try this variant
=SUM(IF(FREQUENCY(IF((A2:A8<"")*(B2:B8<"")*(C2:C 8<"")*(D2:D8<""),MATCH(A2:A8&B2:B8&C2:C8&D2:D8,A 2:A8&B2:B8&C2:C8&D2:D8,0)),ROW(INDIRECT("1:"&ROWS( A2:A8))))0,1)) entered with ctrl + shift & enter Note that I used the same ranges in earlier samples thus your data (excluding headers are in A2:D8) also note that if this range is large the formula might choke excel and make it very slow the last ROWS part is just to make it flexible, if you know that you always will have for instance 30 rows (A2:A31) you could use =SUM(IF(FREQUENCY(IF((A2:A31<"")*(B2:B31<"")*(C2 :C31<"")*(D2:D31<""),MATCH(A2:A31&B2:B31&C2:C31& D2:D31,A2:A31&B2:B31&C2:C31&D2:D31,0)),ROW(INDIREC T("1:30")))0,1)) -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "via135" wrote in message ... thks Peo! is there not any other way without the helper column? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=513331 |
Counting Unique Values
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 |
Counting Unique Values
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 |
Counting Unique Values
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:D12<""), MATCH(A2:A12&B2:B12&C2:C12&D2:D12,A2:A12&B2:B12&C2 :C12&D2:D12,0)), ROW(INDIRECT("1:30")))0,1))} 3] {=COUNTDIFF(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2:D1 2,,"")} 4] =SUMPRODUCT(--((MATCH(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2:D12, A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2:D12,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 |
Counting Unique Values
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 |
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:D12<""), MATCH(A2:A12&B2:B12&C2:C12&D2:D12,A2:A12&B2:B12&C2 :C12&D2:D12,0)), ROW(INDIRECT("1:30")))0,1))} 3] {=COUNTDIFF(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2:D1 2,,"")} 4] =SUMPRODUCT(--((MATCH(A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2:D12, A2:A12&"#"&B2:B12&"#"&C2:C12&"#"&D2:D12,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 |
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 |
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. |
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 |
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 |
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 |
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 |
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... |
| All times are GMT +1. The time now is 04:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com