Fórmula genérica
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Resumo
Para contar células diferentes de muitas coisas, você pode usar uma fórmula baseada nas funções MATCH, ISNA e SUMPRODUCT. No exemplo mostrado, a fórmula na célula F5 é:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
onde "dados" é o intervalo nomeado B5: B16 e "excluir" é o intervalo nomeado D5: D7.
Explicação
Primeiro, um pouco de contexto. Normalmente, se você tem apenas algumas coisas que não deseja contar, pode usar COUNTIFS como este:
=COUNTIFS(range,"apple",range,"orange")
Mas isso não se ajusta muito bem se você tiver uma lista de muitas coisas, porque você terá que adicionar um par adicional de intervalo / critério para cada coisa que não deseja contar. Seria muito mais fácil construir uma lista e passar uma referência a essa lista como parte dos critérios. Isso é exatamente o que a fórmula nesta página faz.
Basicamente, esta fórmula usa a função MATCH para encontrar células diferentes de "a", "b" ou "c" com esta expressão:
MATCH(data,exclude,0)
Observe que o valor de pesquisa e a matriz de pesquisa são "invertidos" da configuração normal - fornecemos todos os valores do intervalo nomeado "dados" como valores de pesquisa e fornecemos todos os valores que desejamos excluir no intervalo nomeado "excluir". Como fornecemos a MATCH mais de um valor de pesquisa, obtemos mais de um resultado em uma matriz como esta:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Essencialmente, MATCH nos dá a posição dos valores correspondentes como um número e retorna # N / A para todos os outros valores.
Os resultados # N / A são os que nos interessam, pois representam valores diferentes de "a", "b" ou "c". Conseqüentemente, usamos ISNA para forçar esses valores a TRUE e os números a FALSE:
ISNA(MATCH(data,exclude,0)
Em seguida, usamos uma negativa dupla para forçar TRUE a 1 e FALSE a zero. A matriz resultante, dentro de SUMPRODUCT, tem a seguinte aparência:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Com apenas uma matriz para processar, SUMPRODUCT soma e retorna um resultado final, 4.
Nota: Usar SUMPRODUCT em vez de SUM evita a necessidade de usar control + shift + enter.
Contagem menos correspondência
Outra maneira de contar células diferentes de várias coisas é contar todos os valores e subtrair as correspondências. Você pode fazer isso com uma fórmula como esta:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Aqui, COUNTA retorna uma contagem de todas as células não vazias. A função CONT.SE, dado o intervalo nomeado "excluir", retornará três contagens, uma para cada item da lista. SUMPRODUCT soma o total, e esse número é subtraído da contagem de todas as células não vazias. O resultado final é o número de células que não são iguais aos valores em "excluir".
Literal contém lógica de tipo
A fórmula nesta página conta com a lógica "igual a". Se você precisar contar células que não contenham muitas strings, onde contém significa que uma string pode aparecer em qualquer lugar da célula, você precisará de uma fórmula mais complexa.
