Calcule valores distintos según 4 condiciones (fórmula de Excel)

Question

Tengo una estructura de tabla como se muestra a continuación:

    [A]    [B]    [C]    [D]    [E]
[1] Dog    Big    2018   02     123
[2] Dog    Big    2018   02     123
[3] Cat    Small  2019   05     123
[4] Dog    Big    2018   02     124
[5] Cat    Small  2019   05     123

La columna [E] contiene valores duplicados importantes. La tarea consiste en filtrar por las columnas [A], [B] y [C] (ignorar [D]) y encontrar valores distintos en cada conjunto de datos según la columna [E].

Paso 1: identifique y cuente las apariciones de combinaciones únicas de columnas [A], [B], [C] haciendo un CONTAR.SI

=COUNTIFS(
 A:A,A1,
 B:B,B1,
 C:C,C1,
)

Esto me muestra para:

fila 1 = 3

fila 2 = 3

fila 3 = 2

fila 4 = 3

fila 5 = 2

Paso 2: para las combinaciones únicas identificadas en el paso 1, cuente las combinaciones únicas de aquellas más la columna [E]

fila 1 = debería ser 2

fila 2 = debería ser 2

fila 3 = debería ser 1

fila 4 = debería ser 2

fila 5 = debería ser 1

Estos resultados se deben a que hay dos combinaciones únicas en las columnas A, B y C: {Dog, Big, 2018} y {Cat, Small, 2019}. {Dog, Big, 2018} está asociado con dos valores únicos en la columna E y {Cat, Small, 2019} está asociado con un valor único en la columna E.

Answer 1

Usando SUMPRODUCT con COUNTIFS:

=SUMPRODUCT(($A$1:$A$5=A1)*($B$1:$B$5=B1)*($C$1:$C$5=C1)/(COUNTIFS($A$1:$A$5,A1,$B$1:$B$5,B1,$C$1:$C$5,C1,$E$1:$E$5,$E$1:$E$5)+($A$1:$A$5<>A1)+($B$1:$B$5<>B1)+($C$1:$C$5<>C1)))

COUNTIFS divide el recuento total por si cada fila coincide y luego los suma. Entonces, por ejemplo, la fila 1 termina con SUMPRODUCT({1,1,0,1,0}/{2,2,2,1,2})lo que luego se resuelve en SUMPRODUCT({.5,.5,0,1,0})o2

Answer 2

Ordenado con la ayuda de:

Excel: cuente instancias únicas en dos columnas

y

https://professor-excel.com/number-unique-records/

RESPUESTA POTENCIAL (TRABAJANDO):

=SUM(IF(FREQUENCY(IF($A$1:$A$5=A1,IF($B$1:$B$5=B1,IF($C$1:$C$5=C1,MATCH($E$1:$E$5,$E$1:$E$5,0)))),ROW($E$1:$E$5)-MIN(ROW($E$1:$E$5))+1),1))