Fórmula genérica
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Resumen
Para contar filas que contienen valores específicos, puede utilizar una fórmula de matriz basada en las funciones MMULT, TRANSPOSE, COLUMN y SUM. En el ejemplo que se muestra, la fórmula en G5 es:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
donde los datos son el rango con nombre B4: B12.
Nota: esta es una fórmula de matriz y debe ingresarse con control shift enter.
Explicación
Trabajando desde adentro hacia afuera, los criterios lógicos usados en esta fórmula son:
--(data=90)
donde los datos son el rango con nombre B4: D12. Esto genera un resultado VERDADERO / FALSO para cada valor en los datos, y el doble negativo coacciona los valores VERDADERO FALSO a 1 y 0 para producir una 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)
Al igual que los datos originales, esta matriz tiene 9 filas por 3 columnas (9 x 3) y entra en la función MMULT como matriz1 .
Array2 se deriva con:
TRANSPOSE(COLUMN(data))
Esta es la parte complicada y divertida de esta fórmula. La función COLUMNA se usa simplemente por conveniencia como una forma de generar una matriz numérica del tamaño correcto. Para realizar la multiplicación de matrices con MMULT, el recuento de columnas en array1 (3) debe ser igual al recuento de filas en array2 .
COLUMN devuelve la matriz de 3 columnas (2, 3, 4) y TRANSPOSE cambia esta matriz a la matriz de 3 filas (2; 3; 4). Luego, MMULT se ejecuta y devuelve un resultado de matriz de 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Verificamos entradas distintas de cero con> 0 y nuevamente coaccionamos TRUE FALSE a 1 y 0 con un doble negativo para obtener una matriz final dentro de SUM:
=SUM((1;0;1;1;0;0;0;0;1))
En esta matriz final, un 1 representa una fila donde la prueba lógica (datos = 90) devolvió verdadero. El total devuelto por SUM es un recuento de todas las filas que contienen el número 90.
Literal contiene
Si necesita verificar valores de texto específicos, en otras palabras, verifique literalmente si las celdas contienen ciertos valores de texto, puede cambiar la lógica en la fórmula en esta página para usar la función ISNUMBER y SEARCH. Por ejemplo, para contar celdas / filas que contienen "manzana", puede usar:
=ISNUMBER(SEARCH("apple",data))
Detalles sobre cómo funciona esta fórmula aquí.
