Veamos en el siguiente ejemplo dos formas de contar celda que contienen dos palabras, para ello emplearemos las funciones SUMAPRODUCTO, ESNUMERO, en un caso ENCONTRAR y en otro caso HALLAR.
Notemos que en esta ocasión estamos buscando solo una parte de todo el texto, es decir estamos buscando por ejemplo la palabra "Azul" dentro de las celdas B3:B10, si se requiriese de una coincidencia completa (de toda la celda) la fórmula sería mucho mas sencilla, ese fórmula la podemos encontrar en el artículo contar celdas incluyendo dos valores, en el cual se analiza la coincidencia con celdas completas.
Fórmula para contar celdas que contienen dos palabras
En la celda F5 encontramos:
=SUMAPRODUCTO(--((ESNUMERO(ENCONTRAR("Negro",B3:B10)) + ESNUMERO(ENCONTRAR("Azul",B3:B10)))>0))
Mientras que en la celda F8 hallamos:
=SUMAPRODUCTO(--((ESNUMERO(HALLAR("Negro",B3:B10)) + ESNUMERO(HALLAR("Azul",B3:B10)))>0))
Diferencia entre ENCONTRAR y HALLAR
Como podemos observar la diferencia entre las dos fórmulas es únicamente el uso de ENCONTRAR en el primer caso y HALLAR en el segundo caso, ENCONTRAR busca una coincidencia exacta, por lo que para encontrar un texto como "palabras" será distinto al texto "Palabras", mientras que para la función HALLAR los textos "palabras" y "Palabras" son lo mismo.
Evaluar HALLAR("Azul",B3:B10)
En ambas fórmulas tanto la función ENCONTRAR como la función HALLAR suelen ser empleadas para realizar una búsqueda, por ejemplo HALLAR("Azul",B3) de esta forma se busca el texto "Azul" en la celda B3, si existe entonces se muestra el número correspondiente a la posición en que el texto aparece, si el texto no existe aparecerá el error #¡VALOR! , en la fórmula mostrada, al aplicar estas funciones sobre un conjunto de celdas como en HALLAR("Azul",B3:B10) lo que se se obtiene es un conjunto de resultados, un resultado para cada celda evaluada.
En el caso de HALLAR("Azul",B3:B10) los resultados serán:
{ 1, #¡VALOR!, #¡VALOR!, #¡VALOR!, 12, #¡VALOR!, #¡VALOR! , 1}
En el caso de HALLAR("Negro",B3:B10) obtendremos:
{#¡VALOR!, 9, #¡VALOR!, 11, #¡VALOR!, 1 , #¡VALOR!, #¡VALOR!}
Evaluar ESNUMERO(HALLAR("Azul",B3:B10))
En esta parte de la fórmula se cambian los resultados por los valores VERDADERO si existe un número, y FALSO para todos los demás casos.
En el caso de ESNUMERO(HALLAR("Azul",B3:B10) ) los resultados serán:
{ VERDADERO, FALSO, FALSO, FALSO, VERDADERO, FALSO, FALSO, VERDADERO}
En el caso de ESNUMERO(HALLAR("Negro",B3:B10) ) obtendremos:
{FALSO, VERDADERO, FALSO, VERDADERO, FALSO, VERDADERO , FALSO, FALSO}
Evaluar el operador +
Con la fórmula: ESNUMERO(HALLAR("Azul",B3:B10) ) + ESNUMERO(HALLAR("Negro",B3:B10) )
El resultado obtenido con las dos listas anteriores al ser operados con el "+" se asume que los valores FALSO como el número "0" y los valores VERDADERO como el número "1", al sumar las dos listas anteriores en el mismo orden en que aparecen los valores obtendremos
{1, 1, 0, 1, 1, 1, 0, 1}
Tomemos en cuenta que los valores que podemos obtener son 0, 1, y 2 (Cero si ambos son FALSO, 1 si uno es VERDADERO y el otro valor FALSO, y 2 si ambos valores son FALSO).
Evaluar (resultado) > 0
Luego, con el resultado anterior se realiza una comparación de cada uno de los valores con el valor "0", si el valor es cero se obtendrá FALSO, y si es 1 o 2 se obtendrá VERDADERO, de esta forma la lista de valores se convertirá en:
{VERDADERO, VERDADERO, FALSO, VERDADERO, VERDADERO, VERDADERO, FALSO, VERDADERO}
Evaluar el operador -- (resultado)
El resultado obtenido en la parte anterior (la lista con los valores VERDADERO y FALSO) es cambiada por medio del operador "--" en ceros y unos, "0" si es FALSO y "1" si es VERDADERO, con la lista se convierte en:
{1, 1, 0, 1, 1, 1, 0, 1}
Naturalmente en este podríamos preguntarnos, "si ya teníamos este resultado con anterioridad ¿por que las dos últimas operaciones?", el motivo se origina por que la primera lista pudo tener algún valor con 2, con estas dos operaciones, cualquier valor de "1" o más se convertiría en "1" , de esta forma se podrá contar la cantidad de celdas que contienen alguna de las dos palabras empleadas, por que cada celda que forme parte del conteo tendrá el valor 1, solo restaría el paso final para hallar la cantidad de celdas.
Evaluar SUMAPRODUCTO
Finalmente lo que realiza SUMAPRODUCTO es sumar todos los valores de la lista {1, 1, 0, 1, 1, 1, 0, 1} por lo que se obtiene como resultado 6. Es decir 6 es la cantidad de celdas que contienen las dos palabras empleadas en la fórmula.