EXCEL: Cuente las celdas que no son iguales a muchas cosas

Para contar celdas que no son iguales a muchas de las cosas, puede usar una fórmula basada en las funciones COINCIDIR, ESNOD y SUMAPRODUCTO. En el ejemplo que se muestra, la fórmula en la celda F5 es:

=SUMAPRODUCTO(--(ESNOD(COINCIDIR(data,exclude,0))))

donde "datos" es el rango con nombre B5: B16 y "excluir" es el rango con nombre D5: D7.

Cómo funciona esta fórmula

Primero, un pequeño contexto. Normalmente, si tiene solo un par de cosas que no desea contar, puede usar CONTAR.SI.CONJUNTO de esta manera:

=CONTAR.SI.CONJUNTO(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 quiera 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 en esta página.

En el fondo, esta fórmula usa la función COINCIDIR para encontrar celdas que no sean iguales a "a", "b" o "c" con esta expresión:

COINCIDIR(data,exclude,0)

Tenga en cuenta que el valor de búsqueda y la matriz de búsqueda están "invertidos" de la configuración normal: proporcionamos todos los valores del "dato" de rango con nombre 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 COINCIDIR 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}

Esencialmente, 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 no iguales a "a", "b" o "c". En consecuencia, usamos ESNOD para forzar estos valores a VERDADERO, y los números a FALSO:

ESNOD(COINCIDIR(data,exclude,0)

Luego usamos un doble negativo para obligar a VERDADERO a 1 y FALSO a cero. La matriz resultante, dentro de SUMAPRODUCTO se ve así:

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

Con solo una matriz para procesar, SUMAPRODUCTO suma y devuelve un resultado final, 4.

Nota: Usar SUMAAPRODUCTO en lugar de SUMA evita la necesidad de usar control + shift + enter.

Cuenta menos partido

Otra forma de contar celdas que no son iguales a cualquiera de varias cosas es contar todos los valores y restar coincidencias. Puedes hacer esto con una fórmula como esta:

=CONTARA(range)-SUMAPRODUCTO(CONTAR.SI(range,exclude))

Aquí, CONTARA devuelve un recuento de todas las celdas no vacías. La función CONTAR.SI, dado el rango nombrado "excluir", devolverá tres recuentos, uno para cada elemento de la lista. SUMAPRODUCTO suma el total, y este número se resta del recuento de todas las celdas no 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 en 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.