FÓRMULAS DE EXCEL

Contar celdas que no contengan alguna palabra en Excel

Contar celdas que no contengan alguna palabra en Excel

Funciones utilizadas: SUMA, MMULT, ESNUMERO, HALLAR, TRANSPONER, FILA

Suponiendo que disponemos de una lista con distintas frases y palabras en el rango A1:A15, y que necesitamos obtener en una celda de resultado la cantidad de celdas del rango objetivo que no contienen alguna de las palabras contenidas en el rango C4:C6, podemos aplicar una fórmula que combina las funciones SUMA, MMULT, ESNUMERO, HALLAR, TRANSPONER y FILA para lograr obtener el resultado deseado. La sintaxis que debemos utilizar sobre la celda es la siguiente:

{=SUMA(1-(MMULT(--(ESNUMERO(HALLAR(TRANSPONER(C4:C6);A1:A15)));FILA(C4:C6)^0)>0))}

Donde C4:C6 corresponde al rango que contiene las palabras individuales a excluir, y A1:A15 corresponde al rango objetivo donde deben ser buscadas las palabras a excluir.

Notamos que la fórmula es matricial con lo cual debemos aceptar dicha fórmula con CTRL+SHIFT+ENTER.

La fórmula parte con la transposición de los valores a excluir mediante la función TRANSPONER, y se aplica una búsqueda con la función HALLAR sobre el resultado. Esta aplicación genera una matriz de valores que son luego filtrados por la función ESNUMERO y será considerada como «Matriz 1» para la función MMULT. Luego, mediante la función FILA generamos la «Matriz 2» necesaria para la función MMULT y forzamos los valores distintos de cero a sumarse como matriz con la función SUMA.

De esta forma, logramos obtener de forma rápida y simple la cantidad de celdas que no contienen alguna palabra de un rango específico en Excel.

Si necesitas obtener la cantidad de celdas que no sean IGUALES a un rango específico en vez de NO CONTENER dichas palabras, te recomiendo ver el siguiente video: Contar celdas distintas de una lista en Excel

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.