Fórmula genérica
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Resumen
Para contar celdas que no sean iguales a ninguna de las muchas cosas, puede usar una fórmula basada en las funciones COINCIDIR, ISNA y SUMPRODUCTO. En el ejemplo que se muestra, la fórmula en la celda F5 es:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
donde "datos" es el rango con nombre B5: B16 y "excluir" es el rango con nombre D5: D7.
Explicación
Primero, un poco de contexto. Normalmente, si solo tiene un par de cosas que no desea contar, puede usar COUNTIFS de esta manera:
=COUNTIFS(range,"apple",range,"orange")
Pero esto no se escala muy bien si tiene una lista de muchas cosas, porque tendrá que agregar un par de rango / criterio adicional para cada cosa que no desee contar. Sería mucho más fácil crear una lista y pasar una referencia a esta lista como parte de los criterios. Eso es exactamente lo que hace la fórmula de esta página.
En esencia, esta fórmula usa la función COINCIDIR para encontrar celdas que no sean iguales a "a", "b" o "c" con esta expresión:
MATCH(data,exclude,0)
Tenga en cuenta que el valor de búsqueda y la matriz de búsqueda están "revertidos" de la configuración normal: proporcionamos todos los valores del rango con nombre "datos" como valores de búsqueda y damos todos los valores que queremos excluir en el rango con nombre "excluir". Debido a que le damos a MATCH más de un valor de búsqueda, obtenemos más de un resultado en una matriz como esta:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Básicamente, COINCIDIR nos da la posición de los valores coincidentes como un número y devuelve # N / A para todos los demás valores.
Los resultados # N / A son los que nos interesan, ya que representan valores que no son iguales a "a", "b" o "c". En consecuencia, usamos ISNA para forzar estos valores a VERDADERO y los números a FALSO:
ISNA(MATCH(data,exclude,0)
Luego usamos un doble negativo para coaccionar VERDADERO a 1 y FALSO a cero. La matriz resultante, dentro de SUMPRODUCT, se ve así:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Con solo una matriz para procesar, SUMPRODUCT suma y devuelve un resultado final, 4.
Nota: El uso de SUMPRODUCT en lugar de SUM evita la necesidad de usar control + shift + enter.
Cuenta menos coincidencia
Otra forma de contar las celdas que no son iguales a ninguna de varias cosas es contar todos los valores y restar coincidencias. Puedes hacer esto con una fórmula como esta:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Aquí, COUNTA devuelve un recuento de todas las celdas no vacías. La función CONTAR.SI, dado el rango denominado "excluir", devolverá tres recuentos, uno para cada elemento de la lista. SUMPRODUCT suma el total y este número se resta del recuento de todas las celdas que no están vacías. El resultado final es el número de celdas que no son iguales a los valores en "excluir".
Literal contiene lógica de tipo
La fórmula de esta página cuenta con lógica "igual a". Si necesita contar celdas que no contienen muchas cadenas, donde contiene significa que una cadena puede aparecer en cualquier lugar de una celda, necesitará una fórmula más compleja.
