Devolver la suma de dos o más columnas con BUSCARV

Si alguna vez has deseado que la función BUSCARV pueda devolver la suma de dos o más columnas relacionadas, este truco te llevará allí.

Objetivo

Antes de entrar en detalles, dejemos claro nuestro objetivo. Tenemos algunas transacciones que se exportaron desde nuestro sistema de contabilidad como se muestra a continuación.

Nos gustaría escribir una fórmula que busque cualquier TransID determinado y devuelva la suma de tres columnas: Monto, Envío e Impuesto. Esto se ilustra en la captura de pantalla siguiente, donde ingresamos un TransID como 10578 y Excel calcula el Total (1076,36 = 1006 + 10 + 60,36).

Dado que se trata esencialmente de una tarea de búsqueda, nuestro primer instinto es utilizar BUSCARV. Sin embargo, sabemos que BUSCARV solo puede devolver un valor relacionado, no la suma de varios valores relacionados.

Una solución común es agregar una columna auxiliar a los datos que suman las tres columnas y luego usar una BUSCARV para devolver el valor de la nueva columna auxiliar. Sin embargo, siempre que sea posible en la práctica, preferimos trabajar con los datos tal como vienen para que el libro sea fácil de actualizar en períodos futuros.

Otra solución común es escribir una fórmula que sume tres funciones BUSCARV, como la siguiente.

=BUSCARV(...) + BUSCARV(...) + BUSCARV(...)

Sin embargo, una fórmula de este tipo puede resultar difícil de actualizar y mantener a lo largo del tiempo, ya que cualquier cambio en los argumentos debe realizarse tres veces en lugar de una. La buena noticia es que podemos envolver una función SUMAPRODUCTO alrededor de una única función BUSCARV. Empecemos.

SUMAPRODUCTO

Cuando SUMPRODUCTO tiene un solo argumento, se comporta de manera muy similar a la función SUMA porque simplemente devuelve la suma de los valores. Cuando SUMPRODUCT tiene múltiples argumentos, devuelve la suma del producto de sus argumentos. Para lograr nuestro objetivo, usaremos SUMPRODUCT para devolver la suma de los valores en un solo argumento.

Básicamente, envolveremos una función SUMPRODUCTO alrededor de una función BUSCARV que devuelve una matriz de valores, específicamente, los valores relacionados de varias columnas. SUMPRODUCT sumará los valores de la matriz devuelta por la función BUSCARV. ¿Cómo convencemos a la función BUSCARV para que devuelva una matriz de valores? Eso nos lleva a nuestra siguiente discusión.

Nota técnica: necesitamos usar SUMPRODUCTO en lugar de SUM porque la función SUMPRODUCT está diseñada para trabajar con matrices y, por lo tanto, devolverá la suma de la matriz devuelta por la función BUSCARV. La función SUMA devolvería únicamente la suma del primer elemento de la matriz. Sin embargo, puede usar la función SUMA y luego ingresarla en matriz con Control+Shift+Enter si lo prefiere.

Matriz de valores

Podemos pedirle a la función BUSCARV que devuelva una matriz de valores encerrando el tercer argumento entre llaves {}. Esto hace que la función BUSCARV en realidad devuelva una matriz que contiene varios elementos. Por ejemplo, si quisiéramos que la función BUSCARV devuelva los valores de la tercera, cuarta y quinta columnas a una matriz con tres elementos, podríamos usar la siguiente fórmula.

=BUSCARV(C6,B12:F18,{3,4,5},0)

Dónde:

• C6 es el valor de búsqueda
• B12:F18 es el rango de búsqueda
• {3,4,5} son los números de columna que tienen los valores que queremos que se devuelvan, encerrados entre llaves para crear la matriz de valores.
• 0 le dice a BUSCARV que use lógica de coincidencia exacta

Esta función BUSCARV en realidad devuelve una matriz de valores, y los elementos de la matriz son 1006, 10 y 60,36. Si lo ingresa en una sola celda y presiona Enter, Excel mostrará solo el primer elemento. Pero no queremos mostrar el primer elemento, queremos calcular la suma de todos los elementos de la matriz. Entonces, juntemos todo y llevémoslo a casa.

Nota técnica: puede confirmar que BUSCARV devuelve una matriz de valores ingresando la fórmula en tres celdas a la vez (Ctrl+Mayúsculas+Entrar). Excel mostrará los tres elementos de la matriz en las tres celdas.

SUMAPRODUCTO y BUSCARV

Ahora que entendemos que el uso de llaves hace que BUSCARV devuelva una matriz de valores, sumemos los valores en la matriz devuelta con la función SUMAPRODUCTO.

Aquí hay una captura de pantalla de nuestro libro de trabajo.

La fórmula en C7 es la siguiente.

=SUMAPRODUCTO(BUSCARV(C6,B12:F18,{3,4,5},0))

La función SUMPRODUCTO devuelve la suma de los elementos de la matriz devueltos por la función BUSCARV. Esto es esencialmente lo mismo que SUMPRODUCT({1006, 10, 60.36}), que devuelve 1076.36.

Cuando pulsamos Enter confirmamos que funciona como se esperaba…¡sí!

Nota: no es necesario introducir una matriz en esta fórmula usando Control-Shift-Enter.

Y eso amigo mío es una forma de devolver la suma de dos o más columnas con BUSCARV. Si tiene otro enfoque, compártalo publicando un comentario a continuación… ¡gracias!

Recursos adicionales

• Archivo de Excel de muestra
• BUSCARV publicaciones