Fórmula genérica
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Resumo
Para contar células que não contêm muitas strings diferentes, você pode usar uma fórmula bastante complexa baseada na função MMULT. No exemplo mostrado, a fórmula em F5 é:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
onde "dados" é o intervalo nomeado B5: B14 e "excluir" é o intervalo nomeado D5: D7.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter
Prefácio
Esta fórmula é complicada pelo requisito "contém". Se você só precisa de uma fórmula para contar células que não * são * iguais * a muitas coisas, pode usar uma fórmula mais direta baseada na função CORRESPONDÊNCIA. Além disso, se você tiver um número limitado de strings a serem excluídas, poderá usar a função COUNTIFS desta forma:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
No entanto, com essa abordagem, você deve inserir um novo par de argumentos de intervalo / critério para cada string a ser excluída. Em contraste, a fórmula explicada abaixo pode lidar com um grande número de strings a serem excluídas inseridas diretamente na planilha.
Finalmente, esta fórmula é complexa. Deixe-me saber se você tem uma fórmula mais simples para propor :)
Explicação
O núcleo desta fórmula é ISNUMBER e SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Aqui, transpomos os itens no intervalo nomeado "excluir" e, em seguida, alimentamos o resultado para PESQUISA como o "texto localizar", com "dados" como "dentro do texto". A função SEARCH retorna uma matriz 2d de valores TRUE e FALSE, 10 linhas por 3 colunas, como este:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Para cada valor em "dados", temos 3 resultados (um por string de pesquisa) que são #VALUE erros ou números. Os números representam a posição de uma string de texto encontrada e os erros representam as strings de texto não encontradas. A propósito, a função TRANSPOSE é necessária para gerar a matriz 10 x 3 de resultados completos.
Este array é alimentado em ISNUMBER para obter valores TRUE FALSE, que convertemos em 1s e 0s com um operador negativo duplo (-). O resultado é uma matriz como esta:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
que vai para a função MMULT como array1. Seguindo as regras de multiplicação de matrizes, o número de colunas na matriz1 deve ser igual ao número de linhas na matriz2. Para gerar array2 , usamos a função ROW como esta:
ROW(exclude)^0
Isso resulta em uma matriz de 1s, 3 linhas por 1 coluna:
(1;1;1)
que vai para MMULT como array2 . Após a multiplicação da matriz, temos uma matriz dimensionada para corresponder aos dados originais:
(2;1;0;0;1;1;0;0;0;2)
Nesta matriz, qualquer número diferente de zero representa um valor onde pelo menos uma das strings excluídas foi encontrada. Zeros indicam que nenhuma string excluída foi encontrada. Para forçar todos os valores diferentes de zero para 1, usamos maior que zero:
(2;1;0;0;1;1;0;0;0;2)>0
que cria ainda outra matriz ou valores TRUE e FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nosso objetivo final é contar apenas valores de texto onde nenhuma string excluída foi encontrada, portanto, precisamos reverter esses valores. Fazemos isso subtraindo o array de 1. Este é um exemplo de lógica booleana. A operação matemática força automaticamente os valores TRUE e FALSE para 1s e 0s, e finalmente temos uma matriz para retornar à função SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
A função SUM retorna um resultado final de 5.
