Fórmula genérica
(=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N)))
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 K6 é:
(=SUM(--(MMULT(--((data)=2)))
onde os dados são o intervalo nomeado C5: I14.
Observe que esta é uma fórmula de matriz e deve ser inserida com a tecla shift do controle Enter
Explicação
Trabalhando de dentro para fora, os critérios lógicos usados nesta fórmula são:
(data)<70
onde os dados são o intervalo nomeado C5: I14. 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:
(0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0)
Como os dados originais, esta matriz tem 10 linhas por 7 colunas (10 x 7) e entra na função MMULT como matriz1 . O próximo argumento, array2 , é criado com:
TRANSPOSE(COLUMN(data)^0))
Aqui, a função COLUMN é usada como uma forma de gerar uma matriz numérica do tamanho certo, uma vez que a multiplicação da matriz requer que a contagem de colunas na matriz1 (7) seja igual à contagem de linhas na matriz2 .
A função COLUMN retorna a matriz de 7 colunas (3,4,5,6,7,8,9). Ao elevar este array a uma potência de zero, terminamos com um array 7 x 1 como (1,1,1,1,1,1,1), que TRANSPOSE muda para um array 1 x 7 como (1; 1 ; 1; 1; 1; 1; 1).
MMULT então executa e retorna um resultado de matriz 10 x 1 (2; 0; 0; 3; 0; 0; 0; 1; 0; 0), que é processado com a expressão lógica> = 2, resultando em uma matriz VERDADEIRA Valores FALSE:
(TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE).
Novamente forçamos TRUE FALSE a 1 e 0 com um duplo negativo para obter uma matriz final dentro de SUM:
=SUM((1;0;0;1;0;0;0;0;0;0))
O que retorna corretamente 2, o número de nomes com pelo menos 2 pontuações abaixo de 70.
