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.