Home |
Search |
Today's Posts |
#1
|
|||
|
|||
count duplicate (or, inversely, unique) entries, but based on a condition
Hello everybody,
I would like to count duplicate entries on my excel sheet, but only if a certain value is existing in a parallel column, same row. For exemple, there could be 200 duplicate entries in the whole column A, but only 50 of them would be in relation to the Product MMM (specified in column B). Other duplicates would concern Products JJJ or PPP. Do you know any method of counting duplicates (other than manual:-) based on a condition that could deal with my problem? Thanks a lot for your help on this, Mark P.S: If there would be no condition needed (simple counting of duplicates), we could use the following ARRAY formula (found on Chip Pearson's page (http://cpearson.com/excel/duplicat.htm) counting unique entries: =SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,Ra nge1,0),""), IF(LEN(Range1)0,MATCH(Range1,Range1,0),""))0,1)) and then substract it from the total count. However, including condition in there (related to another column) is too hard for me! |
#2
|
|||
|
|||
Try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF((LEN(A1:A10)0)*(B1:B10=C1),M ATCH(A1:A10,A1:A10,0)," "),IF((LEN(A1:A10)0)*(B1:B10=C1),MATCH(A1:A10,A1: A10,0),""))0,1)) ....where C1 contains the 'product' of interest. Hope this helps! In article , "markx" wrote: Hello everybody, I would like to count duplicate entries on my excel sheet, but only if a certain value is existing in a parallel column, same row. For exemple, there could be 200 duplicate entries in the whole column A, but only 50 of them would be in relation to the Product MMM (specified in column B). Other duplicates would concern Products JJJ or PPP. Do you know any method of counting duplicates (other than manual:-) based on a condition that could deal with my problem? Thanks a lot for your help on this, Mark P.S: If there would be no condition needed (simple counting of duplicates), we could use the following ARRAY formula (found on Chip Pearson's page (http://cpearson.com/excel/duplicat.htm) counting unique entries: =SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,Ra nge1,0),""), IF(LEN(Range1)0,MATCH(Range1,Range1,0),""))0,1)) and then substract it from the total count. However, including condition in there (related to another column) is too hard for me! |
#3
|
|||
|
|||
Actually, the following should suffice...
=SUM(IF(FREQUENCY(IF(B1:B10=C1,MATCH(A1:A10,A1:A10 ,0),""),IF(B1:B10=C1,MA TCH(A1:A10,A1:A10,0),""))0,1)) Hope this helps! In article , Domenic wrote: Try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF((LEN(A1:A10)0)*(B1:B10=C1),M ATCH(A1:A10,A1:A10,0)," "),IF((LEN(A1:A10)0)*(B1:B10=C1),MATCH(A1:A10,A1: A10,0),""))0,1)) ...where C1 contains the 'product' of interest. Hope this helps! |
#4
|
|||
|
|||
Have you tried SUMPRODUCT? If the first column is A1:A200, and the second is
B1:B200, then in c1 enter: =SUMPRODUCT(--(A1:A200=B1:B200)) wazooli "markx" wrote: Hello everybody, I would like to count duplicate entries on my excel sheet, but only if a certain value is existing in a parallel column, same row. For exemple, there could be 200 duplicate entries in the whole column A, but only 50 of them would be in relation to the Product MMM (specified in column B). Other duplicates would concern Products JJJ or PPP. Do you know any method of counting duplicates (other than manual:-) based on a condition that could deal with my problem? Thanks a lot for your help on this, Mark P.S: If there would be no condition needed (simple counting of duplicates), we could use the following ARRAY formula (found on Chip Pearson's page (http://cpearson.com/excel/duplicat.htm) counting unique entries: =SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,Ra nge1,0),""), IF(LEN(Range1)0,MATCH(Range1,Range1,0),""))0,1)) and then substract it from the total count. However, including condition in there (related to another column) is too hard for me! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Pivot Table Unique Count | Excel Worksheet Functions | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
count data but avoid double entries | Excel Worksheet Functions | |||
get count of row without duplicate entries | Excel Worksheet Functions |