Greetings!
I have the situation where I need to have a count of unique numbers in a range.
need to exclude any duplicates from the count.
The data is in column B.
1000111
1000212
1000111
1000212
1000045
etc
Thanks,
Brad
Count of Unique Numbers in a range
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count of Unique Numbers in a range
If there are no blank cells in between, you can use an array formula like this (confirm with Ctrl+Shift+Enter, and adjust the range B1:B5 as needed):
=SUM(1/COUNTIF(B1:B5,B1:B5))
=SUM(1/COUNTIF(B1:B5,B1:B5))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Count of Unique Numbers in a range
Will do....
What if there are alpha characters? 3 letters at the beginning?
Thanks,
Brad
What if there are alpha characters? 3 letters at the beginning?
Thanks,
Brad
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count of Unique Numbers in a range
The formula will work for numbers and for text values.
Or do you mean that that you want to count the number of unique 3 letter start sequences in a series of values like
ABC123
ABC456
DEF789
GHI012
GHI345
Or do you mean that that you want to count the number of unique 3 letter start sequences in a series of values like
ABC123
ABC456
DEF789
GHI012
GHI345
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Count of Unique Numbers in a range
sorry for the delay.... well, when I placed the formula, i get a division error. I was thinking it was because of the text I have in the fields.
Brad
Brad
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count of Unique Numbers in a range
Do you have blank cells in the range? That would cause the formula to return #DIV/0.
To exclude blank cells, use the following, still as an array formula (confirm with Ctrl+Shift+Enter):
=SUM(IF(LEN(B1:B5),1/COUNTIF(B1:B5,B1:B5)))
To exclude blank cells, use the following, still as an array formula (confirm with Ctrl+Shift+Enter):
=SUM(IF(LEN(B1:B5),1/COUNTIF(B1:B5,B1:B5)))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Count of Unique Numbers in a range
well, I could not get the above to work, however I did find an example that does do what I am looking for. It was on the Microsoft site.
http://office.microsoft.com/en-us/excel ... 81033.aspx" onclick="window.open(this.href);return false;
Had to cut/paste fix the range, then press f2, then ctrl/shift/enter
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
thanks!
Brad
http://office.microsoft.com/en-us/excel ... 81033.aspx" onclick="window.open(this.href);return false;
Had to cut/paste fix the range, then press f2, then ctrl/shift/enter
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
thanks!
Brad
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count of Unique Numbers in a range
I have attached a small sample workbook. Range A2:A10 contains a mixture of numbers, text, dates and logical values. The formula I proposed and the formula you got from Microsoft return the same count of unique values. I wonder why one works for you and the other doesn't...
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Count of Unique Numbers in a range
Just my 2 cents:
1. A formula to count unique values: =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
2. A formula to count unique strings: {=SUM(1/COUNTIF($A$1:$A$10,$A$1:$A$10))}
Please note: 2. is an array formula and must be entered using CTRL+SHIFT+ENTER
1. A formula to count unique values: =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
2. A formula to count unique strings: {=SUM(1/COUNTIF($A$1:$A$10,$A$1:$A$10))}
Please note: 2. is an array formula and must be entered using CTRL+SHIFT+ENTER
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count of Unique Numbers in a range
Rudi, your second formula is the same as the one from my original reply (with a different range). Brad mentioned that it doesn't do what he wants...
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Count of Unique Numbers in a range
I understand. My intentions were to show the difference between two formulas to do the task that Brad was requesting. One that only works with numbers, and one that is more flexible to count any entry in a range.
PS: I like choices and its in my nature to compare and analyse. :)
PS: I like choices and its in my nature to compare and analyse. :)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.