EXCEL: Contar filas con al menos n valores coincidentes

Para contar filas que contienen valores específicos, puede usar una fórmula de matriz basada en las funciones MMULT, TRANSPONER, COLUMNA y SUMA. En el ejemplo que se muestra, la fórmula en K6 es:

{=SUMA(--(MMULT(--((data)<70),TRANSPONER(COLUMNA(data)^0))>=2))}

donde data es el rango con nombre C5: I14.

Tenga en cuenta que esta es una fórmula de matriz y debe ingresarse con el control shift enter.

Cómo funciona esta fórmula

Trabajando de adentro hacia afuera, el criterio lógico utilizado en esta fórmula es:

(data)<70

donde data es el rango con nombre C5: I14. Esto genera un resultado VERDADERO / FALSO para cada valor en los datos, y el doble negativo obliga a los valores VERDADERO FALSO a 1 y 0 para producir una 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}

Al igual que los datos originales, esta matriz tiene 10 filas por 7 columnas (10 x 7) y entra en la función MMULT como matriz1. El siguiente argumento, array2 se crea con:

TRANSPONER(COLUMNA(data)^0))

Aquí, la función COLUMNA se usa como una forma de generar una matriz numérica del tamaño correcto, ya que la multiplicación de la matriz requiere que el recuento de columnas en la matriz1 (7) sea igual al recuento de filas en la matriz2.

La función COLUMNA devuelve la matriz de 7 columnas {3,4,5,6,7,8,9}. Al elevar esta matriz a una potencia de cero, terminamos con una matriz de 7 x 1 como {1,1,1,1,1,1,1}, que TRANSPONER cambia a una matriz de 1 x 7 como {1; 1 ; 1; 1; 1; 1; 1}.

MMULT luego se ejecuta y devuelve un resultado de matriz de 10 x 1 {2; 0; 0; 3; 0; 0; 0; 1; 0; 0}, que se procesa con la expresión lógica> = 2, lo que resulta en una matriz de VERDADERO Valores FALSO:

{VERDADERO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}.

Nuevamente forzamos a VERDADERO FALSO a 1 y 0 con un doble negativo para obtener una matriz final dentro de SUMA:

=SUMA({1;0;0;1;0;0;0;0;0;0})

Que devuelve correctamente 2, el número de nombres con al menos 2 puntajes por debajo de 70.