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! |
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! |
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! |
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! |
All times are GMT +1. The time now is 03:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com