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,Range1,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),MATCH(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 <#[email protected]>,
"markx" <[email protected]> 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,Range1,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 <[email protected]>,
Domenic <[email protected]> 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),MATCH(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,Range1,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!
>
>
>
>
>