Ejemplos avanzados con SUMAPRODUCTO

sumaproducto excel

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))

sumaproducto excel mayúsculas

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)

buscar dos criterios excel

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))

sumar caracteres rango excel

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)

contar palabras excel

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 valores unicos sumaproducto excel

 

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<>””))

contar elementos duplicados excel sumaproducto

Contar palabras SUMAPRODUCTO
Título: Contar palabras SUMAPRODUCTO (0 click)
Leyenda:
Filename: contar-palabras-sumaproducto.xlsx
Size: 9 KB

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.

Respuestas

  1. Felicitaciones estimado, pero mi dificultad es como programo el conteo de un registro de notas por decir cuantos tienen de 18 a 20, de 14 a17, de 11 a13 y de 00 a 10 , en hoja distintas como resumen anual,favor si podria dar un alcance pero en excel,gracias

    1. Hola Luis!
      La función que necesitas es CONTAR.SI, la cual deberás aplicar a cada uno de los rangos de notas.

  2. Hola Sergio,
    Consulta:
    Como puedo hacer para utilizar la función de sumaproducto en una tabla que usa filtros?.
    Cuando tengo mi resumen al pie de pagina y aplico un filtro, la función de sumaproducto no se actualiza, es algo así como cuando uno usa la función sumar y si aplicas filtro esta no se actualiza y se tiene que usar la función de subtotales 9.
    Lo que usualmente hago es crear una columna extra con el producto de las 2 columnas, y aplicar el subtotal9 para que me de el sumaproducto cuando aplico filtros.
    Pero cuando manejo información muy pesada con muchos datos, el crear columnas y formulas , convierte a la hoja muy pesada y lenta con tantos cálculos al aplicar filtros.
    Encontre una formula que tambien uso para no tener la necesidad de crear la columna extra, pero me parece muy compleja, y tengo que recurrir siempre a ella en una plantilla porque es super extensa y que aveces ni entiendo como funciona.

    =SUMAPRODUCTO((AR10:AR50)*(SUBTOTALES(3;DESREF(AR10:AR50;FILA(AR10:AR50)-MIN(FILA(AR10:AR50));;1)))*AX10:AX50)

    La funcion de sumaproducto la uso mas que todo para resumenes de pie de pagina para sacar promedios ponderados, mientras voy aplicando filtros.

    La consulta es: Existe otra forma de calculo mas sencilla?

    Gracias
    Saludos,

    1. Hola Johann!
      Te diría que trates de sustituir la función SUBTOTALES por AGREGAR. Es más flexible y permite saltarse los datos ocultos en los filtros.

Los comentarios están cerrados.