Precisa de contagem exclusiva de texto de várias colunas por categoria

Question

Meus dados se parecem com algo (suponha que comece em A1):

 Category,Label1,Label2 
 Dogs,blank,doe
 Dogs,zoo,blank 
 Cats,zoo,yup 
 Cats,doe,hgg 
 Cats,blank,doe 

Qual deve ser o resultado:

 Dogs, 2
 Cats, 4

Meus dados reais têm centenas de linhas e cinco colunas de marcadores.

Estou procurando uma solução que use sumproduct ou uma fórmula de matriz e possa manipular espaços em branco e matrizes de tamanhos diferentes. Eu gostaria de evitar o VBA, se possível.

Acabei com essa fórmula, mas ainda não a resposta certa:

=SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))

Eu tentei concatenar (A2: A6 & B2: C6) no parâmetro countif range, mas o countif não aceitou isso. Eu tentei sumproduct mas não gostou que eu estava usando matrizes de tamanhos diferentes (1 coluna vs 2 coluna) e muito mais. Passei a maior parte de dois dias pesquisando e tentando resolver isso.

Aguardamos a sua ajuda e experiência.

    

Answer 1

Eu usaria o suplemento de consulta de energia para isso. Ele tem um comando Group By, que inclui uma operação Count Distinct Rows. Está documentado aqui (embora eles ainda não tenham pego essa operação):

link

Para preparar seus dados no Power Query (antes de usar o comando Group By), eu selecionaria a coluna Category e usaria o comando Transform / Unpivot Columns / Unpivot Other Columns. Isso transformará as colunas Label1 e Label2 em colunas Attribute e Value. Eu removeria a coluna Atributo - que deixa apenas a coluna Valor contendo doe, zoológico etc.

O passo final é o Grupo Por que irá contar corretamente os valores distintos / únicos, por ex. Gatos = 4