# Counting Duplicate Values

#### Knight of Nee

##### Board Regular
Hi

I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.

e.g

if in cell A1 i have = "apples"
and in cell A2 i have "apples"
and in cell A3 i have "Pear"

i want this to return the value 2 not 3, make sense?

how can this be done via a formulae or macro?

Thanks

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

##### MrExcel MVP
I believe I ever posted at this board a solution for this type of problems...

=SUMPRODUCT(--(COUNTIF(A1:A3,A1:A3)>1))

#### Knight of Nee

##### Board Regular
i have copied the formulae and it dosnt appear to work... are you sure it is correct or am i doing something wrong

in the example i want it to ignore all duplicates of apples once the first instance of apples has been counted

##### MrExcel MVP
Knight of Nee said:
i have copied the formulae and it dosnt appear to work... are you sure it is correct or am i doing something wrong

in the example i want it to ignore all duplicates of apples once the first instance of apples has been counted

It appears you want a unique/distinct item count...

=SUMPRODUCT((A1:A3<>"")/COUNTIF(A1:A3,A1:A3&""))

#### Knight of Nee

##### Board Regular
cheers mate works a treat

#### mohsinhabib

##### New Member
it is a very good solution and work great but i want some amendments...

i.e.
if I use filter then it should return values calculated base on filtered values just like SUBTOTAL() works....

please send me if you know the solution for this

#### Stormseed

##### Banned
hi mohsinhabib

welcome to MrExcel. Can you please let us know what you want to accomplish with a small example alongwith your expected results ?

Also, it would be great if you could start a new thread if thats feasible to you

##### MrExcel MVP
it is a very good solution and work great but i want some amendments...

i.e.
if I use filter then it should return values calculated base on filtered values just like SUBTOTAL() works....

please send me if you know the solution for this

Let O5:O12 be the range of interest from which you want to calculate a distinct/unique count under filter conditions...

Control+shift+enter, not just enter:
Code:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(O5,ROW(O5:O12)-ROW(O5),,1)),
IF(O5:O12<>"",MATCH("~"&O5:O12,O5:O12&"",0))),
ROW(O5:O12)-ROW(O5)+1),1))

#### Stormseed

##### Banned
Control+shift+enter, not just enter:
Code:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(O5,ROW(O5:O12)-ROW(O5),,1)),
IF(O5:O12<>"",MATCH("~"&O5:O12,O5:O12&"",0))),
ROW(O5:O12)-ROW(O5)+1),1))

When it comes to understanding of the problem, I guess I am an all time idiot

Still I have not understood the OPs question !

#### mohsinhabib

##### New Member
thanks <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 10"><meta name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cmohsin%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C08%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 10"><meta name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cmohsin%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C08%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]-->Aladin Akyurek for quick response, it works perfectly

Dear <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 10"><meta name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cmohsin%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C09%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]-->Stormseed,

heres what I wanted,

Suppose there are cells

<table x:str="" style="border-collapse: collapse;" width="307" border="0" cellpadding="0" cellspacing="0" height="180"><col style="width: 61pt;" width="81"> <col style="width: 84pt;" width="112"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 61pt;" width="81" height="17">
</td> <td class="xl24" id="_x0000_s2049" x:autofilter="all" x:autofilterrange="\$B\$1:\$C\$9" style="width: 84pt;" width="112">Department</td> <td class="xl24" id="_x0000_s2050" x:autofilter="all" style="width: 85pt;" width="113">Items</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Operation</td> <td>Car</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Operation</td> <td>Car</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Operation</td> <td>Truck</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Admin</td> <td>Car</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Admin</td> <td>Car</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Admin</td> <td>Truck</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>HR</td> <td>Car</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Account</td> <td>Car</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Total</td> <td class="xl24" x:num="" x:arrayrange="B10" x:fmla="=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B9)-ROW(B2),,1)),IF(B2:B9<>"",MATCH("~"&B2:B9,B2:B9&"",0))),ROW(B2:B9)-ROW(B2)+1),1))">4</td> <td class="xl24" x:num="" x:fmla="=SUBTOTAL(3,C2:C9)"></td> </tr> </tbody></table>
suppose i want to know how many departments have trucks, so i apply filter on Items column, but it was continuously giving me "4", whereas i wanted "total" to return total on the basis of filter i applied, i.e if after filtering department returns 2 department, then total should be able to count that.

Anyways Aladin Akyurek's method works, but if u have another way of doing it, then you are more than welcome. It will help me increasing my knowledge. and also thanks for replying. i hope this will continue if future as well

Replies
5
Views
254
Replies
2
Views
245
Replies
6
Views
190
Replies
4
Views
211
Replies
3
Views
449

1,187,178
Messages
5,962,048
Members
438,578
Latest member
MrJimC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back