Count Unique Values in a List

[Michael151]

Hello all,

Just need a little help with a formula to count the number of unique values within a list.

For example, I have this list:
Text1
Text2
Text3
Text4
Text4
Text5

The formula would need to return the result of 5. "Text4" would only be counted once since it's a duplicate.

Any help is most appreciated - thanks!

 

[crook_101]

Hi,

I think this was Aladin's formula from a while ago, it has proved useful

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Text1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Text2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Text3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Text4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Text4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Text5</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">5</TD><TD>Unique Count </TD></TR></TBODY></TABLE>

Sheet3​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A7</TH><TD style="TEXT-ALIGN: left">=SUM(IF(FREQUENCY(MATCH(A1:A6,A1:A6,0),MATCH(A1:A6,A1:A6,0))>0,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Adjust range to suit your needs!!


Regards,
Ian

 

[jeffreybrown]

Hello Ian,

Just so you know, this question is also posted at:

http://www.excelforum.com/excel-general/776018-count-unique-values-in-a-list.html#post2525430

 

[crook_101]

Thanks for the heads up Jeffrey

Michael, if you are cross posting, please see the forum rules and mention it in your post,.,.,.,.,.only triyng to help people....

 

[Michael151]

Thanks for the help guys!

Sorry, forgot to mention the other post. Will do in the future.

 

[jeffreybrown]

No problem Michael...glad it worked for you.

 

[crook_101]

No worries Michael, pleased you are sorted

 

[T. Valko]

Hello all,

Just need a little help with a formula to count the number of unique values within a list.

For example, I have this list:
Text1
Text2
Text3
Text4
Text4
Text5

The formula would need to return the result of 5. "Text4" would only be counted once since it's a duplicate.

Any help is most appreciated - thanks!

If you only have a "few" rows of data and no empty cells within the range...

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))