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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I believe I ever posted at this board a solution for this type of problems...

=SUMPRODUCT(--(COUNTIF(A1:A3,A1:A3)>1))
 
Upvote 0
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
 
Upvote 0
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&""))
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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))
 
Upvote 0
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 !
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top