Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (no duplication)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (no duplication)
'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 --------------- Jacob Skaria "Elton Law" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (no duplication)
OOOH My God,
That's amazing. How can you do that? But ... thanks indeed. "Jacob Skaria" wrote: '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 --------------- Jacob Skaria "Elton Law" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (no duplication)
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (no duplication)
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/ex...561181033.aspx If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: OOOH My God, That's amazing. How can you do that? But ... thanks indeed. "Jacob Skaria" wrote: '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 --------------- Jacob Skaria "Elton Law" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (no duplication)
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 "Elton Law" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplication Macro | Excel Discussion (Misc queries) | |||
avoid duplication ..help please | Excel Worksheet Functions | |||
Duplication | Excel Discussion (Misc queries) | |||
Duplication | Excel Discussion (Misc queries) | |||
Preventing Duplication | Excel Worksheet Functions |