En un rango de datos, necesitamos buscar un valor y sumar todos los datos de una condición mediante funciones, similar al empleo de las tablas dinámicas.
Emplearemos las siguientes funciones anidándolas:
- Función SUMA. Suma todos los números en un rango, en nuestro caso indicamos la columna.
- Función BUSCARV. Busca en la primera columna de una matriz y se mueve en la fila para devolver el valor de una celda.
La fúnción anidada se deberá insertar como fórmula matricial.
Disponemos de un rango con datos cruzados, las filas muestran el año y las columnas diferentes países y deseamos buscar por el año deseado y algunos de los países.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | España | México | Francia | Colombia | Alemania | Perú | |
| 2 | 2016 | 1 | 2 | 3 | 4 | 5 | 6 |
| 3 | 2015 | 2 | 4 | 6 | 8 | 10 | 12 |
| 4 | 2014 | 3 | 6 | 9 | 12 | 15 | 18 |
- IMPORTE EUROPA - 2016
Ir a: Celda B7
Escribir: 2016. Como el año que deseamos buscar para los valores de Europa.Ir a: Celda C7
Escribir:=SUMA(BUSCARV(B7;A1:G4;{2;4;6};0))Pulsar la tecla F2, para editar la fórmula
Pulsar la tecla Ctrl y mantenerla pulsada Pulsar la tecla Mayús y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SUMA(BUSCARV(B7;A1:G4;{2;4;6};0))}.Mostrará 9 como la suma en el año 2016 para España, 1; Francia, 3 y Alemania 5.
- IMPORTE AMÉRICA - 2014
Ir a: Celda B8
Escribir: 2014. Como el año que deseamos buscar para los valores de América.Ir a: Celda C8
Escribir:=SUMA(BUSCARV(B8;A1:G4;{3;5;7};0))Pulsar la tecla F2, para editar la fórmula
Pulsar la tecla Ctrl y mantenerla pulsada Pulsar la tecla Mayús y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SUMA(BUSCARV(B8;A1:G4;{3;5;7};0))}.Mostrará 36 como la suma en el año 2016 para México, 6; Colombia, 12 y Perú 18.
- IMPORTE TOTAL - 2015
Ir a: Celda B9
Escribir: 2015. Como el año que deseamos buscar para todos los valores.Ir a: Celda C9
Escribir:=SUMA(BUSCARV(B9;A1:G4;{2;3;4;5;6;7};0))Pulsar la tecla F2, para editar la fórmula
Pulsar la tecla Ctrl y mantenerla pulsada Pulsar la tecla Mayús y mantener las 2 teclas pulsadas Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SUMA(BUSCARV(B9;A1:G4;{2;3;4;5;6;7};0))}.Mostrará 42 como la suma en el año 2015 para España, 2; México, 4, Francia, 6, Colombia, 8, Alemania 10 y Perú 12.
Excel Avanzado
la planilla no esta disponible?
Hola Mauricio,
No incluimos la plantilla de ninguno de los trucos publicados, ya que la finalidad es que nosotros mismos podamos realizarlo.
Saludos.
Completisima la explicacion y tambien muy clara.-
Mauricio, la queres toda eh!
hola, como seria si quiero saber por ejemplo la suma de Mexico del año 2014 y 2016 solamente?
muchas gracias
Hola Gustavo,
Basándonos en el mismo ejemplo, insertamos la fórmula:
El primer valor corresponde a México 2016 y el segundo a México 2014.
Saludos.
Hola me sirve esto mismo para buscar y sumar por ejemplo las ventas de un ejecutivo en distintas fechas del mes , sin la de filtrar y sumar?
Hola Paola
Puedes aplicar la función SUMAR.SI o SUMAR.SI.CONJUNTO.
Saludos.
Hola,
tengo una consulta, y si en una de las celdas hubiera texto, por ejemplo quisiera agregarle el simbolo de la moneda, $, USD, EUR, o el texto que sea (por unidad, no se en realidad que se esta sumando en el ejemplo) como se haría para que sume numeros y agregue el texto en una misma celda?
Hola Belén,
Respecto a que está sumando el ejemplo, lee el apartado 4 de los diferentes casos y ahí lo indica.
Para asignar el tipo de moneda según el país se puede realizar con la función SI anidada, que evalúe el país y asigne su moneda.
Saludos.
Hola, buenos días, esta combinación es barbara, el tema que la necesito para que sume 2 columnas pero en la busqueda de otro libro, y se que suma en otro libro (que está cerrado), no funciona,
Consulta, conocen alguna función que haga lo mismo que este ejemplo pero de otro libro (si está cerrado mejor).
Muchísimas gracias!!!!!
Daniel.
Hola Daniel,
No existe ninguna función que permita realizar lo que necesitas. La única solución es recurrir a macros.
Saludos.
Muchisimas Gracias!!!
Hola!
Gracias por el excelente post. Sin embargo, tengo una pregunta.
Normalmente en el "indicador columnas", damos los números correspondientes a las columnas que queremos sumar
e.g. Importe Europa {2;4;6}
Existe alguna manera de obtener aquellos números {2;4;6} automáticamente y que sean válidos para la sintaxis completa?
Lo intenté con {COINDIDIR;COINCIDIR;COINCIDIR} pero no me funcionó.
Hola Alejandro,
Podríamos con combinaciones de funciones: INDICE y COINCIDIR, DESREF y COINCIDIR, SUMAPRODUCTO...
Si te sirve como idea: Alternativa BuscarV
Saludos
Excelente ejemplo!!! Pero me queda una duda:
Como puedo sumar valores que se encuentran en la misma columna, como la pregunta que hicieron más arriba de México, pero con la particularidad de que exista más de un valor para, por ejemplo, el año 2014. No tiene mucho sentido el ejemplo pero para el caso que lo quiero aplicar si lo tiene (personas que se atienden en un mismo lugar, misma ID)
Saludos,
Hola Bastina,
Prueba con la función SUMAR.SI y SUMAR.SI.CONJUNTO.
Saludos.
ESTIMADO CONSULTA,
PARA REALIZAR UNA BUSQUEDA Y AL VALOR ENCONTRADO SUMARLE UN VALOR COMO SE PUEDE HACER?
POR EJEMPLO ME PIDEN BUSCAR EN UNA PLANILLA DE REMUNERACIONES EL NOMBRE DE UNA PERSONA X, Y SABER CUANTO ES EL LIQUIDO QUE VA A RECIBIR PERO A ESO SUMARLE UN BONO? ALGUNA FUNCION QUE LO HAGA?
Hola Patricia,
La respuesta es sencilla:
= [Función BuscarV] + [Bono]
Saludos
Buenos días, disculpa pero la funcion no me funciona, tengo Excel 2013 y no se si es que la estoy copiando mal o sera que deshabilitaron la funcion, en espera de su pronta respuesta de verdad me interesa mucho esta combinacion de funciones.
Hola Daniel,
Si tienes Excel en español, todo es igual. Es importante insertar la función como matricial, como se indica en los puntos 3. Escribes la función sin pulsar Enter/ni clic en ninguna celda, pulsas Ctrl+Shift+Enter y listo.
Saludos.
Buen día,
Excelente explicación, pero si lo quiero hacer sumando el valor que tenga en varias filas de un mismo concepto como se debe hacer la formula
Hola Mónica,
Puedes aplicar la función SUMAR.SI o SUMAR.SI.CONJUNTO
Saludos.
Buenas,
No se si lo que voy a preguntar se puede hacer...
A ver yo tengo unos numero de clientes, los cual pongo en una columna el numero y en la otra el nombre. Estos clientes los introduzco varias veces con un importe en varias columnas, Quiero que en una celda busque al cliente x y sume todas los importes que hay en la columna b, columna c y columna j... Y así porque hay alguna columnas mas...
Esto lo puedo hacer haciendo un bucarv y que sume lo que le pido?
Juver
La llave de busqueda es el numero de documento y este se repite en varias filas con valores diferentes, y quisiera tener una lista de numeros de documentos y en frente la suma total de los valores por N° de documento.
Alguien me puede colaborar con esta formula?
Hola desde Colombia.
Podrias explicar la parte de la formula:
=BUSCARV(B7;A1:G4;{2;4;6};0)
Especificamente el "Indicador de columna o valor regresado" {2;4;6}
Hola Gabriel,
{2;4;6} Indica el número de las columnas 2= España(B); 4= Francia(D) y 6= Alemania(F); Son las columnas donde queremos sumar los valores.
Al aplicar como fórmula matricial (F2 y el proceso) Indicamos todas las filas de las columnas, que coincidan con el año de referencia.
Saludos.
Muchas gracias, por la aclaración.
Como hago para que repita la operación? Quiero tener un codigo en la primera columna y que me vaya sumando todos los gastos con ese codigo.
Osea quiero que sume todos los números de una misma columna que tengan cierto codigo en otra columna.
Hola Olinda,
Aplica la función SUMAR.SI.
Saludos.
Hola buenos dias, como hago para sumar cifras de indicadores repetidos, por ejemplo
enero l 3 l 4 l
enero l 1 l 2 l
febrero l 3 l 7 l
febrero l 2 l 6 l
que se sumen los enero y los febrero para que nada mas quede un indicador con el total
enero l 4 l 6 l
febrero l 5 l 13 l
Hola Efrain,
Según la estructura que propones es complejo, BUSCARV solo admite valores no repetidos. Existen métodos para obtener la segunda o tercera vez que se repita, pero la fórmula sería muy compleja.
En el ejemplo la estructura de los datos son cruzados.
Te recomiendo utilices tablas dinámicas.
Saludos.
Buen día
En el documento [Libro1.xlsx]Hoja1 encuentro que para el valor A tengo un número 70, Hoja2, para el valor A tengo un número 80 y para Hoja3 no existe valor A, en algunos casos.
¿Cómo hago para que en una celda de otro libro (Libro2 por ejemplo) busque el valor A en todas las hojas del Libro1 y haga una suma? Y que el resultado aparezca, independientemente de que no encuentre valor A en algunas hojas de ese documento.
Mi fórmula de BUSCARV es la siguiente:
=BUSCARV(celda de referencia;'dirección del archivo\[nombre del archivo]\hoja1'rango de busqueda;columna de busqueda;FALSO)
Esto solo me funciona para una hoja. Si sumo otra formula con hoja2 que encuentre el valor de referencia, realiza la suma. Si sumo otra formula con hoja3 que no encuentre el valor de referencia, sale error.
Ojala me puedan ayudar, saludos!
Hola Pablo,
Puedes aplicar la herramienta de consolidar datos, sin necesidad de funciones.
Saludos.
buenos dias quisiera extraer el numero comprobante de pago de una hoja excel donde tiene las mismas caracteristicas.. cual e la formula ????
n° cheque cod.cheque n° comprobante de pago
72560234 065 2065
Hola Carmela,
Para buscar un valor puedes aplicar la función BUSCARV. Revisa los argumentos de la función y aplica tus valores. No te puedo indicar la función escrita ya que necesitamos conocer el rango de los datos, la celda donde buscar el valor y la columna del comprobante.
Si tienes problemas indica con mas detalles como tienes los datos.
Saludos.
Hola buenas tardes. Queria saber si hay alguna forma de utilizar esto pero que sume las filas en lugar de las columnas? Te escribo un ejemplo para ver si me pueden ayudar.
1 2 3
A Mes Articulo Unidades vendidas
B Enero Pan 30
C Febrero Pan 30
D Marzo Pan 50
E
F PAN (celda)
Yo necesito en la celda "(celda)" la cantidad de unidades vendidas de pan en los 3 meses mencionados. Si tiro un buscarv normal +BUSCARV(F1; tabla de arriba;3;0), me tira el primer resultado que sale en la tabla, es decir, 30 unidades.
Hay alguna forma para que en la (celda) me tire directamente las 110 unidades de los 3 meses en cuestión? Probé utilizar la formula que explican acá, la cual es excelente, pero en este caso no me sirve.
Muchas gracias!
Hola Lautaro,
La función BUSCARV sólo busca en valores únicos.
Revisa el siguiente post, o utiliza tablas dinámicas.
Saludos.
Hola buen dia,
Tengo una planilla en una hoja con diferentes nombres en una columna y gastos producidos por ese nombre que se repite varias veces.
En otro hoja quiero que me busque ese nombre y me sume todos los gastos que figura.
Que formula puedo aplicar???
Gracias
Hola Damian,
Puedes aplicar la función SUMAR.SI.
Saludos.
Tengo una base de datos de clientes y deseo saber cuantas cotizaciones solicito en el intervalos de 2 fechas, como podría hacerlo
Hola Janeth,
Revisa el siguiente post.
Saludos.
Hola quiero saber como anidar la función BUSCARV, con la función SUMAR.SI.CONJUNTO
Hola Juan,
En breve publicaremos un post al respecto.
Saludos.
Hola. Tengo una tabla en la que cada registro tiene un campo puntos. Quiero buscar, con base en un id, todos los registros que coinciden y sumar los puntos de cada uno. Como podría hacerlo? Gracias.
Hola Arturo,
Revisa las funciones SUMAR.SI y SUMAR.SI.CONJUNTO.
Saludos.
Hola, se podría automatizar con un combobox el rango de columnas a consultar?
Gracias¡
Hola Luk,
Si se puede tanto para cuadro combinado de formulario como de Active X (Rango de entrada como $A:$A).
Saludos.
tengo la suma de una columna, pero no quiero tomar en cuenta los numeros que en otra columna marquen menos del 80%
Hola Carlos,
Prueba con la función SUMAR.SI.CONJUNTO y SUMAR.SI.
Saludos.
Estimado,
Consulta para el n° de columnas es posible ingresar el rango a sumar?
Ej: =SUMA(BUSCARV(B9;A1:G4;{2;3;4;5;6;7};0))
SUMA(BUSCARV(B9;A1:G4;{2:7};0))
Algo así.
Gracias.
Hola Raúl,
Con BUSCARV no se podría ya que el tercer argumento siempre hace mención a columnas y no a filas.
Saludos.
Amigo buen día, me ha sido de mucha ayuda su aporte, de ser posible podría indicarme como sumaria solo los negativos de las columnas o solo los positivos.
Gracias de antemano
Hola John,
Por este método sería muy complejo, intenta aplicar la función SUMAR.SI.CONJUNTO.
Saludos.
Hola: Dejo una consulta de excel que hace días estoy tratando de resolver y no he podido. En una columna tengo un listado de nombres en los que algunos se repiten. en otra columna tengo valores numéricos que corresponden a cada nombre. Lo que necesetio es una formula que realice la accion de buscar en cada repetición de un nombre y sumarle el valor que le corresponde en la columna de al lado. Ej: buscar todas las veces que aparece "A" y sumar el valor que le corresponde a "A" en cada caso.
Muchas gracias!!!!
HOLA
Como podria hacerle si quiero sumar de una columna los valores condicionados por otra y contar y descontar de otra, es decir,
E4: Tengo la opcion de cliente o inventario.
O4: Cantidad que me va descontando en la celda M4 de existencias
G4: Tengo producto
Entonces cuando si selecciono cliente se descuentan en M4
Pero quiero que cuando seleccione inventario se vuelvan a sumar a M4.
Ayuda 🙁
Hola Laura,
En la celda M4 puedes aplicar la función SI, para que aplique la condición cliente/inventario con la resta o suma (si se cumple o si no se cumple).
Saludos.
Excelente ayuda. Estaba buscando fórmula para sumar desde una matriz y la fórmula expuesta es justo lo que necesitaba.
Hola,
Yo estoy intentando sumar y ligar las cantidades y productos de mis pedidos de Producción con mi inventario solo si esté ha sido entregado. ¿Se puede usar una formula tipo así?
=SUMAR.SI(BUSCARV(B13,Maquila!$B$2:$B$500,4,FALSO),"Entregado",Maquila!$D$3:$D$500)
Hola Mariela,
La fórmula no es correcta BuscarV devuelve un valor y no indica un rango. Prueba con la función SUMAR.SI.CONJUNTO.
Saludos.
=SUMA(BUSCARV(A2;A2:D4;3;0))+SUMA(BUSCARV(A4;A2:D4;3;0))
si en la formula estan incluidas la sumatioria de 5 hojas , pero solo en una o dos se encuentra el valor buscado, como hacer para que arroje el resultado encontrado en una, o la sumatoria de dos hojas. para que no muestre el error #N/A
Hola Lupita,
Inserta cada función dentro de la función SI.ERROR.
Saludos.
Inserta cada función dentro de la función SI.ERROR. NO ME FUNCIONO, LO QUE INTENTO HACER ES SUMAR EN UNA HOJA UN CONCEPTO X, EN VARIAS HOJAS QUE EXISTE LA POSIBILIDAD DE QUE SE ENCUENTRE EN TODAS, O SOLO EN ALGUNAS.
=SUMA(BUSCARV(A2;A2:D4;3;0))+SUMA(BUSCARV(A4;A2:D4;3;0)), ESTA SI ME FUNCIONO, PERO SOLO EN LOS CASOS DONDE EL CONCEPTO SE ENCUENTRE EN TODAS LAS HOJAS. CUANDO EN UNA HOJA NO SE ENCUENTRA EL CONCEPTO, ME MARCA #N/A.
QUE CORRECCION TENGO QUE HACER. O QUE FORMULA APLICAR PARA ESTE CASO
Hola Lupita,
Puedes aplicar la fórmula: =SI.ERROR(SUMA(BUSCARV(Hoja3!A2;A2:D4;3;0));"") + SI.ERROR(SUMA(BUSCARV(Hoja4!A2;Hoja4!A2:D4;3;0));"")
Saludos.
Hola Jarol
Necesito buscar y sumar las coincidencias. Las formula que das la hace pero solo horizontal yo lo requiero vertical existe alguna que me puedas recomendar
Hola Jarol
Aplica la función BUSCARH.
Saludos.
si yo quisiera buscar un dato que se repite y sumar su valor de forma vertical como seria?
ejemplo
a1 1000
b1 1000
c1 1000
a1 50
a1 40
b1 60
Hola Jesús,
Aplica la función SUMAR.SI
Saludos.
Buenas, si tuviera una columnas de distintas variales pero algunas repetidas y cada una con diferente resultados y quisiera sumar los resultados con el texo "NO" que formula usar?
A - EVANGELISTA S.A SI SI
A - EVANGELISTA S.A NO NO
ARCEO RAMIRO ERN SI NO
ARCEO RAMIRO ERN SI SI
ARCEO RAMIRO ERN SI SI
ARCEO RAMIRO ERN SI SI
ARCEO RAMIRO ERN SI SI
BOLLAND Y CIA. S.A. SI SI
CLARIANT (ARGENT SI SI
CLEAR PETROLEUM SI NO
CLEAR PETROLEUM SI SI
Hola Maximiliano,
Puedes aplicar la función SUMAR.SI. Pero en el ejemplo no existen números que sumar.
Saludos.
Hola,
Muchas gracias por la explicación. Tenía una duda que no se cómo resolver. En el caso de que en mi tabla, por una razón concreta, tengo varias veces repetidos un valor, siguiendo tu ejemplo, digamos que en la columna A tengo varias celdas con el valor "2014" y yo lo que quiero es que me devuelva el valor del sumatorio de la columna E donde la columna A sea "2014". Cómo sería esa fórmula?
Muchas gracias
Hola Julian,
Aplica la función: =SUMAR.SI(A1:A10;2014;E1:E10)
Saludos.
Una consulta y si deseo que las columnas se resten utilizando la función Buscarv, como podría realizarlo. Gracias
Hola Thalia,
En Excel no existe una función para restar. Puedes aplicar la SUMA y cambiar a valores negativos los valores.
Saludos.