Fórmula genérica
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Resumen
Para contar celdas que no contienen muchas cadenas diferentes, puede usar una fórmula bastante compleja basada en la función MMULT. En el ejemplo que se muestra, la fórmula en F5 es:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
donde "datos" es el rango con nombre B5: B14 y "excluir" es el rango con nombre D5: D7.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter
Prefacio
Esta fórmula se complica por el requisito de "contiene". Si solo necesita una fórmula para contar celdas que no son * iguales * a muchas cosas, puede usar una fórmula más sencilla basada en la función COINCIDIR. Además, si tiene un número limitado de cadenas para excluir, puede usar la función CONTAR.SI de esta manera:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Sin embargo, con este enfoque, debe ingresar un nuevo par de argumentos de rango / criterio para cada cadena a excluir. Por el contrario, la fórmula que se explica a continuación puede manejar una gran cantidad de cadenas para excluir ingresadas directamente en la hoja de trabajo.
Finalmente, esta fórmula es compleja. Avísame si tienes una fórmula más sencilla para proponer :)
Explicación
El núcleo de esta fórmula es ISNUMBER y SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Aquí, transponemos los elementos en el rango nombrado "excluir", luego alimentamos el resultado a BUSCAR como el "texto de búsqueda", con "datos" como "dentro del texto". La función de BÚSQUEDA devuelve una matriz 2d de valores VERDADERO y FALSO, 10 filas por 3 columnas, así:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Para cada valor en "datos", tenemos 3 resultados (uno por cadena de búsqueda) que son errores de #VALUE o números. Los números representan la posición de una cadena de texto encontrada y los errores representan cadenas de texto no encontradas. Por cierto, se necesita la función TRANSPONER para generar la matriz de 10 x 3 de resultados completos.
Esta matriz se introduce en ISNUMBER para obtener valores VERDADEROS FALSOS, que convertimos a 1 y 0 con un operador doble negativo (-). El resultado es una matriz como esta:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
que entra en la función MMULT como array1. Siguiendo las reglas de la multiplicación de matrices, el número de columnas en matriz1 debe ser igual al número de filas en matriz2. Para generar array2 , usamos la función FILA como esta:
ROW(exclude)^0
Esto produce una matriz de 1, 3 filas por 1 columna:
(1;1;1)
que entra en MMULT como array2 . Después de la multiplicación de matrices, tenemos una matriz dimensionada para que coincida con los datos originales:
(2;1;0;0;1;1;0;0;0;2)
En esta matriz, cualquier número distinto de cero representa un valor en el que se ha encontrado al menos una de las cadenas excluidas. Los ceros indican que no se encontraron cadenas excluidas. Para forzar todos los valores distintos de cero a 1, usamos mayor que cero:
(2;1;0;0;1;1;0;0;0;2)>0
que crea otra matriz o valores VERDADERO y FALSO:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nuestro objetivo final es contar solo los valores de texto donde no se encontraron cadenas excluidas, por lo que debemos invertir estos valores. Hacemos esto restando la matriz de 1. Este es un ejemplo de lógica booleana. La operación matemática convierte automáticamente los valores VERDADERO y FALSO en 1 y 0, y finalmente tenemos una matriz para volver a la función SUMA:
=SUM((0;0;1;1;0;0;1;1;1;0))
La función SUMA devuelve un resultado final de 5.
