validar - extraer valores repetidos en excel

CONTAR con una hoja de cálculo mientras ignora los duplicados en una columna separada

Question

Me gustaría encontrar cómo usar la función COUNTIFS en excel para contar las filas en una hoja de cálculo sin tener en cuenta los valores duplicados en una columna separada. Aquí hay una muestra de los datos:

[ ]

Me gustaría contar el número de filas que son "Uno" en el estado y "Azul" en la categoría. Sin embargo, solo quiero contar los duplicados para estos valores respectivos en "ID" una vez.

Esto es lo que he intentado:

=SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)>0,1))

Mismo problema que el ejemplo anterior. - Esto funciona para darme el número de duplicados en la columna A, pero no puedo trabajar ninguna declaración IF de manera efectiva.

=IF(B2:B12="One",IF(C2:C12="Blue",SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)>0,1))))

Cuando agrego las declaraciones IF en, obtengo el mismo número que la fórmula anterior.

Para su placer de copiar / pegar:

ID Status Category
1423 One Blue
1423 One Blue
1423 One Red
5124 One Blue
5124 One Blue
2341 Two Blue
1111 One Red
1212 Two Red
1212 One Blue
1111 One Red
5124 One Red

El resultado ideal sería 3 .

Answer 1

Hay un método COUNTUNIQUE estándar que utiliza funciones SUMPRODUCT y COUNTIF ; (por ej., SUMPRODUCT(1/COUNTIF(A2:A12, A2:A12&"")) ). Esto se puede expandir para incluir condiciones cambiando a una función CONTAR.SI, pero se debe tener cuidado para garantizar que no #DIV/0! error puede ocurrir

En E4 como una fórmula estándar,

=SUMPRODUCT(((B2:B12="one")*(C2:C12="blue"))/(COUNTIFS(A2:A12, A2:A12, B2:B12, "one", C2:C12, "blue")+((B2:B12<>"one")+(C2:C12<>"blue"))))

El numerador de la operación de división proporciona la mitad del procesamiento condicional. El denominador COUNTIFS del operador de división debe ser el opuesto (distinto de cero) del numerador cuando el numerador es cero.