Ejemplos avanzados con SUMAPRODUCTO

En el artículo anterior escribí sobre algunos conceptos básicos, sintaxis y ejemplos de la función SUMAPRODUCTO que, como pudiste comprobar, tiene más usos de los que pensamos…

Hoy sigo con algunos ejemplos más avanzados que se han planteado en el Foro de Ayuda Excel y que se han solucionado con la función SUMAPRODUCTO.

Hacer búsquedas sensibles a mayúsculas

BUSCARV tiene una gran limitación: no distingue entre mayúsculas y minúsculas. Si necesitas hacer una búsqueda en la que, por ejemplo 3u12 no sea el mismo valor que 3U12, la mejor alternativa es utilizar SUMAPRODUCTO.

Atención: SUMAPRODUCTO sólo devolverá valores numéricos. Si no es el caso que necesitas, puedes utilizar INDICE + COINCIDIR.

SUMAPRODUCTO multiplica los elementos de las matrices dadas y devuelve la suma de los productos

=SUMAPRODUCTO(IGUAL($A$2:$A$29;$F$3)*($B$2:$B$29))

IGUAL compara el valor de la celda F3 con cada uno de los elementos de la matriz A2:A29. Si coincide en mayúsculas y minúsculas, devuelve VERDADERO. Si no coincide, FALSO. Estos dos valores lógicos tienen asociados los números 1 y 0 respectivamente, así que, SUMAPRODUCTO multiplica los valores devueltos y muestra la suma.

Como los ceros no cuentan para esta operación, sólo se devuelven los valores asociados que coincidan con la celda F3.

Atención: Si el valor buscado tiene más de una coincidencia, el valor que devuelve la fórmula será la suma de los valores asociados.

Hacer búsquedas en tablas de doble entrada

En caso de que la búsqueda que necesites hacer se encuentre en una tabla de doble entrada, es decir que tengas que buscar en una fila y una columna, también puedes utilizar SUMAPRODUCTO. Ya te lo mostré en este artículo (solución 6).

=SUMAPRODUCTO(((B2:V2=Y5)*(A3:A23=Y6)*B3:V23)

Igual que en el ejemplo anterior, en caso de que exista más de una coincidencia en alguno de los criterios de fila o columna, la fórmula devolverá la suma de ambos valores asociados.

El archivo del ejemplo, donde muestro 8 maneras de buscar en tablas de doble entrada, forma parte del manual BUSCARV y otras funciones de búsqueda.

Contar caracteres

Cuando tenemos que contar el número de caracteres de un rango de celdas, la solución que se nos viene a la cabeza de forma inmediata sería la de sumar la longitud de cada celda de forma individual:

=LARGO(A2)+LARGO(A3)+LARGO(A4)

o

=SUMA(LARGO(A2);LARGO(A3):LARGO(A4))

Estas fórmulas funcionan perfectamente, pero si el rango contiene mil celdas… ¡la cosa cambia!

La solución adecuada sería la de utilizar SUMAPRODUCTO de esta forma:

=SUMAPRODUCTO(LARGO(A2:A19))

También puedes utilizar la función SUMA de forma matricial:

{=SUMA(LARGO(A2:A19))}

Recuerda pulsar Ctrl + Mayús + Intro para introducir la fórmula.

¿Quieres saber cuántos caracteres suman los artículos que contienen la palabra “boquilla”?

=SUMAPRODUCTO(LARGO(A2:A19)-LARGO(SUSTITUIR(A2:A19;”Boquilla”;””)))

En el rango hay 7 artículos y cada palabra contiene 8 caracteres: 7 *8 = 56.

Contar palabras

Si en vez de caracteres necesitas contar palabras completas en un rango de celdas, puedes utilizar una fórmula muy parecida a la anterior:

=SUMAPRODUCTO(LARGO(ESPACIOS(A2:A4))-LARGO(SUSTITUIR(A2:A4;” “;””))+1)

La función ESPACIOS se utiliza para eliminar los espacios al principio y al final de la celda en caso de que existan. Se trata de una mera comprobación.

También puedes utilizar SUMA en su forma matricial:

{=SUMA(LARGO(ESPACIOS(A2:A4))-LARGO(SUSTITUIR(A2:A4;” “;””))+1)}

Contar valores únicos

SUMAPRODUCTO se utiliza también para contar valores únicos. La siguiente fórmula cuenta las veces que se repite un valor y lo convierte en el divisor de la división 1/número de veces. Si el número se encuentra en el rango más de una vez, dará como resultado un número menor que 1, por lo que no se tiene en cuenta para la fórmula.

=SUMAPRODUCTO(1/CONTAR.SI(A2:A10;A2:A10))

Contar duplicados entre dos columnas

Imagina que tienes dos columnas con datos y quieres averiguar cuántos valores se encuentran en ambas. Puedes utilizar SUMAPRODUCTO combinada con CONTAR.SI de la siguiente manera:

=SUMAPRODUCTO((CONTAR.SI(A2:A12;B2:B12)>0)*(B2:B12<>””))

Esta misma fórmula también podría  usarse para hallar el número de elementos únicos:

=SUMAPRODUCTO((CONTAR.SI(A2:A12;B2:B12)=0)*(B2:B12<>””))

Resumen

Estos ejemplos son una muestra de todas las utilidades que tiene la función SUMAPRODUCTO. Las he extraido de consultas que durante los últimos años han estado planteando los usuarios del foro de Ayuda Excel. Para formar parte de la comunidad regístrate.