Fórmula genérica
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Resumo
Para contar linhas que contêm valores específicos, você pode usar uma fórmula de matriz com base nas funções MMULT, TRANSPOSE, COLUMN e SUM. No exemplo mostrado, a fórmula em G5 é:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
onde os dados são o intervalo nomeado B4: B12.
Observação: esta é uma fórmula de matriz e deve ser inserida com a tecla Ctrl Shift Enter.
Explicação
Trabalhando de dentro para fora, os critérios lógicos usados nesta fórmula são:
--(data=90)
onde os dados são o intervalo nomeado B4: D12. Isso gera um resultado VERDADEIRO / FALSO para cada valor nos dados, e o duplo negativo força os valores VERDADEIRO / FALSO para 1 e 0 para produzir uma matriz como esta:
(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)
Como os dados originais, este array tem 9 linhas por 3 colunas (9 x 3) e vai para a função MMULT como array1 .
Array2 é derivado de:
TRANSPOSE(COLUMN(data))
Esta é a parte divertida e complicada desta fórmula. A função COLUMN é usada simplesmente por conveniência, como uma forma de gerar uma matriz numérica do tamanho certo. Para realizar a multiplicação da matriz com MMULT, a contagem de colunas em array1 (3) deve ser igual à contagem de linhas em array2 .
COLUMN retorna a matriz de 3 colunas (2,3,4) e TRANSPOSE altera esta matriz para a matriz de 3 linhas (2; 3; 4). MMULT então é executado e retorna um resultado de matriz 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Verificamos se há entradas diferentes de zero com> 0 e novamente forçamos TRUE FALSE a 1 e 0 com um duplo negativo para obter uma matriz final dentro de SUM:
=SUM((1;0;1;1;0;0;0;0;1))
Nesta matriz final, 1 representa uma linha onde o teste lógico (dados = 90) retornou verdadeiro. O total retornado por SUM é uma contagem de todas as linhas que contêm o número 90.
Literal contém
Se você precisar verificar valores de texto específicos, em outras palavras, literalmente verificar se as células contêm determinados valores de texto, você pode alterar a lógica na fórmula nesta página para usar as funções ISNUMBER e SEARCH. Por exemplo, para contar células / linhas que contêm "maçã", você pode usar:
=ISNUMBER(SEARCH("apple",data))
Detalhes sobre como essa fórmula funciona aqui.
