Countif (no duplication)

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

Elton Law

Hi Expert,
Wanna count a column but there may be some duplication.
Want to count the number of persons.
If all count, should be 11.
But want to count without duplication, can it be made? In this case, should
be 7.
(Janie, Jenny are duplicated)


Say Column A ...
Janie
Jenny
Alan
Patrick
Elton
Janie
Janie
Jenny
Alan
Elmer
Tinny
 
'If there are no blank cells
=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))

'and if there are blanks in the range
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

If this post helps click Yes
 
Hi Elton; let me try explaining this.

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

--First part of the formula (A1:A20<>"") is to check whether each cell is
blank or not which returns an array of TRUE and FALSE. All blanks will return
FALSE and non-blanks will return TRUE. Here TRUE equates to 1 and FALSE
equates to 0.

--Second part of the formula is to COUNT the number of each entry in the
range. =COUNTIF(A1:A20,A1&""). So this will return the count of A1 in that
range; non-duplicates will return 1, duplicates will return the number of
times it has been duplicated,blank entries will return the number of blank
cells. So =COUNTIF(A1:A20,A1:A20&"") will return an array of this count; the
array size is exactly same as the array returned from the first part.

--Third part of the formula is to divide the 'first part' which is TRUE
/FALSE or 1 or 0 with the second part (which is the count of each entry). So
non duplicates will equate to (1 divided by 1) which returns 1 itself.
Duplicates for example if the count for an entry is 2 ; 1 divided by 2 will
return 0.5. If there are 3 instances of the entry 1 divided by 3 will return
3.333.. etc;for each entry. So the sum of duplicates also will return 1. For
blanks the division happens FALSE/the count of blanks which equate to 0.

--The final SUMPRODUCT adds up all the array values returned to return the
count. Here duplicates (fractions whch add up to 1) and non-duplicates (1's),
blanks (0's) are added to return the distinct count.

PS: Another way of counting unique values among duplicates is explained in
the below link which uses the function FREQUENCY()

http://office.microsoft.com/en-us/excel/HP030561181033.aspx


If this post helps click Yes
 
Hi,

Follow these steps:

1. Type a heading for the range on sheet1, say Alias
2. Select the range of data (including the heading given in 1 above) on
sheet1 and assign it a name, say dummy1;
3. Select the range again and press Ctrl+L;
4. Select any blank cell;
5. Save the file on the desktop and name it try.xls
6. Go to Data > Import External Data > New Database query
7. Select Excel files
8. In the folder hierarchy on the right, select Desktop and click on the
try.xls on the left had panel
9. Click on OK
10. Click on dummy1 and then press the greater then symbol to get the Alias
column on the right hand side
11. Click on Next
12. Click on Next 2 times and on the last screen select "View Data or Edit
Query in Microsoft Query"
13. In the MS Query box, click on the SQL button

SELECT Count(*) FROM (SELECT DISTINCT ucase([Alias]) FROM dummy1)

14. It will say that SQL query cannot be depicted graphically - click on OK
15. Go to File > Return data to MS office Excel
16. In the Properties box, select the cell where you want the output.
17. The count of names will appear as desired;

Now you may add or edit names in dummy1. All you have to do is right click
anywhere in the output and click on Refresh.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top