Fórmula genérica
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Resumo
Para contar valores numéricos exclusivos em um intervalo, você pode usar uma fórmula baseada nas funções FREQUENCY, SUM e IF. No exemplo mostrado, os números dos funcionários aparecem no intervalo B5: B14. A fórmula em G6 é:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
que retorna 2, uma vez que há 2 IDs de funcionário exclusivos no prédio A.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, a menos que você esteja usando o Excel 365.
Explicação
Observação: antes do Excel 365, o Excel não tinha uma função dedicada para contar valores exclusivos. Esta fórmula mostra uma maneira de contar valores exclusivos, desde que sejam numéricos. Se você tiver valores de texto ou uma combinação de texto e números, precisará usar uma fórmula mais complicada.
A função FREQÜÊNCIA do Excel retorna uma distribuição de freqüência, que é uma tabela de resumo que contém a freqüência dos valores numéricos, organizados em "bins". Nós o usamos aqui como uma forma indireta de contar valores numéricos únicos. Para aplicar os critérios, usamos a função IF.
Trabalhando de dentro para fora, primeiro filtramos os valores com a função IF:
IF(C5:C14="A",B5:B14) // filter on building A
O resultado dessa operação é uma matriz como esta:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Observe que todos os ids no edifício B agora são FALSE. Esta matriz é entregue diretamente à função FREQUENCY como data_array . Para o bins_array , fornecemos os próprios ids:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Com esta configuração, FREQUENCY retorna a matriz abaixo:
(4;0;0;0;2;0;0;0;0;0;0)
O resultado é um pouco enigmático, mas o significado é 905 aparece quatro vezes e 773 aparece duas vezes. Os valores FALSE são ignorados automaticamente.
FREQÜÊNCIA tem um recurso especial que retorna automaticamente zero para quaisquer números que já tenham aparecido na matriz de dados, razão pela qual os valores são zero quando um número é encontrado. Esse é o recurso que permite que essa abordagem funcione.
Em seguida, cada um desses valores é testado para ser maior que zero:
(4;0;0;0;2;0;0;0;0;0;0)>0
O resultado é uma matriz como esta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Cada TRUE na lista representa um número único na lista, e só precisamos somar os valores TRUE com SUM. No entanto, SUM não soma valores lógicos em uma matriz, então precisamos primeiro forçar os valores em 1 ou zero. Isso é feito com o duplo negativo (-). O resultado é uma matriz de apenas 1 ou 0:
(1;0;0;0;1;0;0;0;0;0;0)
Finalmente, SUM soma esses valores e retorna o total, que neste caso é 2.
Critérios múltiplos
Você pode estender a fórmula para lidar com vários critérios como este:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
