Calcule valores distintos com base em 4 condições (fórmula Excel)

Question

Eu tenho uma estrutura de tabela conforme abaixo:

    [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

A coluna [E] contém valores duplicados importantes. A tarefa é filtrar nas colunas [A], [B] e [C] (ignorar [D]) e encontrar valores distintos em cada conjunto de dados com base na coluna [E].

Etapa 1: identificar e contar ocorrências de combinações únicas de colunas [A], [B], [C] executando um COUNTIFS

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

Isso me mostra para:

linha 1 = 3

linha 2 = 3

linha 3 = 2

linha 4 = 3

linha 5 = 2

Etapa 2: para as combinações exclusivas identificadas na etapa 1, conte as combinações exclusivas dessas mais a coluna [E]

linha 1 = deveria ser 2

linha 2 = deveria ser 2

linha 3 = deve ser 1

linha 4 = deve ser 2

linha 5 = deve ser 1

Esses resultados ocorrem porque existem duas combinações exclusivas nas colunas A, B e C: {Dog, Big, 2018} e {Cat, Small, 2019}. {Dog, Big, 2018} está associado a dois valores únicos na coluna E e {Cat, Small, 2019} está associado a um valor único na coluna E.

Answer 1

Usando SUMPRODUCT com 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)))

O COUNTIFS divide a contagem total pela correspondência de cada linha e depois os soma. Então, por exemplo, a linha 1 termina com SUMPRODUCT({1,1,0,1,0}/{2,2,2,1,2})a qual então resolve para SUMPRODUCT({.5,.5,0,1,0})ou2

Answer 2

Classificado com a ajuda de:

Excel: conte instâncias únicas em duas colunas

e

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

RESPOSTA POTENCIAL (TRABALHANDO):

=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))