USAR COMODINES EN LA FUNCIÓN SUMAPRODUCTO

Uno de los post más visitados de la web es el uso de la función SUMAPRODUCTO para contar valores, y como una alternativa a Contar.si.conjunto. Además de ser una fórmula muy interesante (naturaleza matricial), lo importante es que muestra el dato cuando hacemos referencias a libros cerrados, cosa que con funciones como Constar.si.Conjunto o Sumar.si.conjunto sí sucede.

El único «problemilla» que tiene esta función es que no admite comodines, ni el asterisco (*), ni el dólar ($), la interrogación (?)… Y esto puede ser un problema cuando trabajas con grandes cadenas de datos y deseas contar valores que pueden tener varios nombres, ejemplo “alimentación”, “aliment.”, “alimentos” para referirse al mismo concepto.

Pues bien, existe una solución a este problema (más bien 4), aunque podrían implementarse más fórmulas. La clave está en combinar diferentes funciones con SumaProducto para obtener lo que estamos buscando.

Veamos un pequeño ejemplo de una base de datos de trabajadores de unos grandes almacenes (respecto a crear nombres para hacer pruebas tenéis aquí un post interesante):

Y ahora imaginad que queremos contar el número de trabajadores en base a dos criterios: que trabajen en la sección “ALIMENTACIÓN” y que sepan hablar “CHINO”. Bien, para esto podremos utilizar la fórmula SumaProducto e introducirla de la siguiente forma:

=SUMAPRODUCTO(($C$2:$C$65000="ALIMENTACIÓN")*1;($F$2:$F$65000="CHINO")*1)

El resultado son 4 personas, pero fijaros que en la fórmula, el criterio es exacto, Alimentación y Chino, de forma que si en nuestra base de datos alguien introduce una abreviatura de por ejemplo Chino (CH), el resultado de la cuenta no sería el mismo, o ALIM de Alimentación. Pues bien, para prevenir estos problemas, os muestro 4 soluciones:

1. La primera consiste en combinar SumaProducto con la función Izquierda. De manera que podamos buscar coincidencias por las primeras letras y contar los resultados:

=SUMAPRODUCTO((IZQUIERDA($C$2:$C$65000;4)="ALIM")*1;(IZQUIERDA($F$2:$F$65000;2)="CH")*1)

El resultado será 4, (importante, en la función Izquierda, además del nombre del criterio hay que indicar el número de caracteres, ejemplo ALIM=4 o CH=2

2. La otra alternativa (y supongo que ya sabréis a la que me refiero) es utilizar Derecha en lugar de Izquierda, de forma que buscaremos coincidencias por las últimas letras o caracteres.

=SUMAPRODUCTO((DERECHA($C$2:$C$65000;9)="MENTACIÓN")*1;(DERECHA($F$2:$F$65000;3)="INO")*1)

Al igual que en la fórmula anterior es necesario indicar el número de caracteres.

3. La tercera alternativa sería combinar SumaProducto con la función Encontrar, pero en este caso será necesario introducir la función de forma matricial (Ctrol + Alt + Enter).

{=SUMAPRODUCTO(((SI.ERROR(ENCONTRAR("ALIM";$C$2:$C$65000);"0")))*1;(SI.ERROR(ENCONTRAR("CH";$F$2:$F$65000);"0")))*1}

Es necesario también hacer uso de la función Si.Error para eliminar los error y pasarlos a valor “0”.

4. Por último, podremos combinar la función SumaProducto con la función Hallar, y de la misma forma que el caso anterior, será necesario introducir el cálculo de forma matricial:

{=SUMAPRODUCTO(((SI.ERROR(HALLAR("ALIM";$C$2:$C$65000);"0")))*1;(SI.ERROR(HALLAR("CH";$F$2:$F$65000);"0")))*1}

Como podéis observar sí que existen alternativas a SumaProducto con uso de comodines, simplemente hacemos que algunas funciones trabajen como si fuesen un comodín. Aquí tenéis todas las fórmulas con los mismos resultados:

Descarga el archivo de ejemplo pulsando en: USAR COMODINES EN LA FUNCIÓN SUMAPRODUCTO