|
|
Count but ignore duplicate data
Hi Expert, I need your help on how to count data but to ignore duplicates.
Below count results should be = 3 (ignore duplicates)
Inv No.
9123
9123
9125
9128
9128
Thanks for your support,
|
|
0
|
|
|
|
Reply
|
 Utf
|
4/20/2010 1:06:01 PM |
|
Try
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))
--
Jacob (MVP - Excel)
"Rechie" wrote:
> Hi Expert, I need your help on how to count data but to ignore duplicates.
>
> Below count results should be = 3 (ignore duplicates)
> Inv No.
> 9123
> 9123
> 9125
> 9128
> 9128
>
> Thanks for your support,
|
|
|
1
|
|
|
|
Reply
|
Utf
|
4/20/2010 1:12:01 PM
|
|
Hi,
One way
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))
Note that Bernd P has done an analysis of different methods of doing this
dependent on dataset size. look here.
http://www.sulprobil.com/html/count_unique.html
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Rechie" wrote:
> Hi Expert, I need your help on how to count data but to ignore duplicates.
>
> Below count results should be = 3 (ignore duplicates)
> Inv No.
> 9123
> 9123
> 9125
> 9128
> 9128
>
> Thanks for your support,
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/20/2010 1:15:01 PM
|
|
Thanks Guys,
It is working well.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/20/2010 1:29:02 PM
|
|
Glad to help but don't forget to check out the link I gave you because the
sumproduct method becomes very slow for larger datasets and the website has
other better methods
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Rechie" wrote:
>
> Thanks Guys,
>
> It is working well.
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/20/2010 1:54:06 PM
|
|
|
4 Replies
1444 Views
(page loaded in 0.064 seconds)
Similiar Articles: Count but ignore duplicate data - microsoft.public.excel.misc ...Hi Expert, I need your help on how to count data but to ignore duplicates. Below count results should be = 3 (ignore duplicates) Inv No. 9123 ... Don't Count Duplicates - microsoft.public.excel.misc ...Glad to help but don't forget to check out the link I gave you because the ... Don't Count Duplicates - microsoft.public.excel.misc ... Count but ignore duplicate data ... How to count duplicates... - microsoft.public.mac.office.excel ...Count but ignore duplicate data - microsoft.public.excel.misc ... Hi Expert, I need your help on how to count data but to ignore duplicates. Below count results should be ... Prohibit duplicate data in a cell - microsoft.public.excel ...Count but ignore duplicate data - microsoft.public.excel.misc ... ignoring text data in a cell when summing values in the same cell ... Count but ignore duplicate data ... Sum ignoring hidden duplicates - microsoft.public.access.reports ...Criteria start in E2 and down In F2: =SUM(N ... Count but ignore duplicate data - microsoft.public.excel.misc ... ... Insert copied cells above hidden Named Range ... How can I count dates if few duplicates in a column - microsoft ...Count but ignore duplicate data - microsoft.public.excel.misc ... You may want to ignore nulls or not. -- KARL DEWEY ... How can I count dates if few duplicates in a column ... No duplicates in 2 columns - microsoft.public.access.queries ...Count but ignore duplicate data - microsoft.public.excel.misc ... No duplicates in 2 columns - microsoft.public.access.queries ... You may want to ignore nulls or not. Help! how to cross - data with excel?? - microsoft.public.excel ...Count but ignore duplicate data - microsoft.public.excel.misc ... Hi Expert, I need your help on how to count data but to ignore duplicates. ... Help! how to cross - data ... Sumproduct for partial name? - microsoft.public.excel.misc ...Count but ignore duplicate data - microsoft.public.excel.misc ... The SUMPRODUCT part of the formula counts all occations of "S" that have a ... ... All data is duplicate ... Query that will look for duplicate in two tables. - microsoft ...Find, hide, or eliminate duplicate data - Access - Office.com Where duplicates exist in two tables, show only one ... This causes the query to ignore the duplicate records ... Count but ignore duplicate data - microsoft.public.excel.misc ...Hi Expert, I need your help on how to count data but to ignore duplicates. Below count results should be = 3 (ignore duplicates) Inv No. 9123 ... Count but ignore duplicate data - ExcelBanterExcel Discussion (Misc queries) ... Hi Expert, I need your help on how to count data but to ignore duplicates. Below ... Try =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10 ... Count but ignore duplicate data - MS Office forums. More than ...Hi Expert, I need your help on how to count data but to ignore duplicates. Below count results should be = 3 (ignore duplicates) Inv No. 9123 9123 Excel - Count First Entries (ignore Duplicates) Against Multiple ...I have a table of data which has a column that contains a list of entries, some of which are legitimate duplicate entries (col A). In a second column I have the date ... Excel - Count Non Duplicates - Hi I am looking... - Free Excel HelpI am looking for a formula that can count and ignore duplicates detail tab: ... and i wanted to do a formula that counts if there is duplicate data. And if there is, count how ... 7/26/2012 9:33:15 PM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
66 followers. 