Counting Duplicate Values

Knight of Nee

Board Regular
Joined
Aug 4, 2003
Messages
124
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Aug 4, 2003
Messages
124
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Aug 4, 2003
Messages
124

ADVERTISEMENT

cheers mate works a treat
 

mohsinhabib

New Member
Joined
Mar 18, 2009
Messages
2
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
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

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 :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Sep 18, 2006
Messages
3,274
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 :devilish:

Still I have not understood the OPs question !
 

mohsinhabib

New Member
Joined
Mar 18, 2009
Messages
2
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,054
Members
425,451
Latest member
JohnBrooksBiddle

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top