Fórmula genérica
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Resumo
Para contar valores de texto exclusivos em um intervalo com critérios, você pode usar uma fórmula de matriz baseada nas funções FREQUENCY e MATCH. No exemplo mostrado, a fórmula em G6 é:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
que retorna 3, já que três pessoas diferentes trabalharam no projeto Omega.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Esta é uma fórmula complexa que usa FREQUENCY para contar valores numéricos derivados com a função MATCH. Trabalhando de dentro para fora, a função MATCH é usada para obter a posição de cada valor que aparece nos dados:
MATCH(B5:B11,B5:B11,0)
O resultado de MATCH é uma matriz como esta:
(1;1;3;1;1;6;7)
Como MATCH sempre retorna a posição da primeira correspondência, os valores que aparecem mais de uma vez nos dados retornam a mesma posição. Por exemplo, como "Jim" aparece 4 vezes na lista, ele aparece nesta matriz 4 vezes como o número 1.
Fora da função MATCH, a função IF é usada para aplicar critérios, que neste caso envolve testar se o projeto é "ômega" (da célula G5):
IF(C5:C11=G5 // filter on "omega"
A função IF atua como um filtro, permitindo apenas a passagem dos valores de MATCH se estiverem associados a "omega". O resultado é uma matriz como esta:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
A matriz filtrada é entregue diretamente à função FREQUENCY como o argumento data_array . Em seguida, a função ROW é usada para construir uma lista sequencial de números para cada valor nos dados:
ROW(B3:B12)-ROW(B3)+1
Isso cria uma matriz como esta:
(1;2;3;4;5;6;7;8;9;10)
que se torna o argumento bins_array em FILTER. Neste ponto, temos:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQÜÊNCIA retorna uma matriz de números que indica uma contagem para cada valor na matriz de dados, organizada por bin. Quando um número já foi contado, FREQUENCY retornará zero. O resultado de FREQUENCY é uma matriz como esta:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Nota: FREQUENCY sempre retorna uma matriz com um item a mais do que bins_array .
Neste ponto, podemos reescrever a fórmula desta forma:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Verificamos se há valores maiores que zero, o que converte os números em VERDADEIRO ou FALSO:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Em seguida, usamos um duplo-negativo para forçar os valores lógicos a 1s e 0s:
=SUM((1;0;0;0;0;1;1;0))
Finalmente, a função SUM retorna 3 como resultado final.
Observação: esta é uma fórmula de matriz e deve ser inserida usando Control + Shift + Enter.
Manipulação de células vazias no intervalo
Se alguma célula do intervalo estiver vazia, você precisará ajustar a fórmula para evitar que células vazias sejam passadas para a função MATCH, o que gerará um erro. Você pode fazer isso adicionando outra função IF aninhada para verificar se há células em branco:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Com dois critérios
Se você tiver dois critérios, poderá estender a lógica da fórmula adicionando outro IF aninhado:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Onde c1 = critérios1, c2 = critérios2 e vals = a faixa de valores.
Com lógica booleana
Com a lógica booleana, você pode reduzir IFs aninhados:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Isso torna mais fácil adicionar e gerenciar critérios adicionais.
