Cómo utilizar la función SUMAR.SI en varias hojas
Hace unos días, un usuario del Foro de Ayuda Excel, comentaba que no era capaz de utilizar la función SUMAR.SI en varios rangos diferentes, ya que la función solamente permite un argumento para la ubicación de los datos. Planteo una posible solución que seguramente, no será la única.
El objetivo que persigue el usuario es el de resumir en una sola hoja las ventas semestrales de cada uno de los productos que comercializa. Los datos a resumir se encuentran en hojas mensuales, es decir, seis, en el mismo libro.
Como la función no cuenta con más de un argumento para especificar el rango donde se encuentran los datos a evaluar, tienes que agrupar cada uno de los rangos en uno solo… ¿y eso cómo se hace?
Agrupar varios rangos de celdas en uno solo
En alguna ocasión he hablado de los nombres de rango. Una de las utilidades de un nombre de rango es la de simplificar la referencia de un rango de celdas para que, a la hora de introducirlo en una fórmula, solo tengas que introducir el nombre. No es lo mismo introducir Mi_Rango que Mi_Libro!$A12:C55.
Para crear el nombre que haya referencia a varios rangos, se debe hacer mediante una Referencia matricial:
- Abre el Administrador de nombres que se encuentra en la ficha Fórmulas.
- Pulsa en el botón Nuevo para crear un nombre.
- En el cuadro de texto Nombre, introduce el nombre que desees (eso sí, siguiendo las normas de los nombres). En el cuadro desplegable Ambito, puedes seleccionar una de las hojas del libro o el libro entero. Te recomiendo seleccionar todo el libro para no tener problemas. Si quieres agregar un comentario para que te quede clara la referencia, puedes hacerlo en el cuadro Comentario.
- En el cuadro Hace referencia a, introduce la siguiente fórmula en este mismo formato:
={“hoja1”;”hoja2”;”hoja3”;….;”hoja_n”}
En el caso del usuario que hizo la consulta, esta fórmula sería:
={“Enero”;”Febrero”;”Marzo”;”Abril”;”Mayo”;”Junio”}
Al hacer eso, le estás diciendo a Excel que el nombre asignado hace referencia a las hojas completas que has especificado en la fórmula.
Una vez hecho esto, ya tienes la hoja preparada para introducir la fórmula necesaria para calcular las ventas semestrales.
Introducir la fórmula SUMAR.SI
Prepara la hoja que contendrá el resumen de la forma que te indico:
Sitúa las referencias que quieras sumar en la columna A.
A continuación, selecciona la celda B2 e introduce la siguiente fórmula teniendo en cuenta que MisHojas es el nombre que le he dado al conjunto de todas las hojas del semestre:
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & “!A:B”);A2;INDIRECTO(MisHojas & “!B:B”)))
¡Atención! No pulses Intro!! Para introducir esta fórmula debes pulsar Ctrl + Mayús + Intro. Se agregarán automáticamente un juego de llaves. Esto significa que se trata de una fórmula matricial.
Si no entiendes la fórmula anterior, te la explico poco a poco:
- Las funciones INDIRECTO devuelven el valor que se encuentra en la referencia que se indica. En este caso, la primera función devuelve el valor que se encuentra en el rango A:B del conjunto de rangos “MisHojas“. Como la fórmula introducida es matricial, la función INDIRECTO devuelve toda la matriz de valores.
- La función SUMAR.SI te la he explicado arriba. Realiza la suma de los valores que se encuentran en la columna B de todas las hojas del nombre MisHojas, si coincide con el valor introducido en la celda A2.
- Finalmente, la función SUMA, suma todos los valores que coinciden con el criterio de la función SUMAR.SI.
¿Te ha sido útil? Comparte y comenta!!
Gracias, Danilo.
Espero que te haya resultado útil.
Por favor, podrÃas indicarme una inquietud:
Condiciones del problema:
Si realizo una formulación entre 3 hojas, por ejemplo una suma.
En la Hoja1 y Hoja2 estan los datos y en la Hoja3 la fórmula suma.
Ahora saco una copia de la Hoja3 que contiene la fórmula y quiero que la copia de la hoja de fórmulas obtenga los nuevos resultados de otras dos hojas diferentes a las originales.
Cómo lo puedo hacer si la formulación siempre hace referencia a las hojas originales.
Todo ésto se debe realizar en el mismo libro de excel.
Agradezco tu gentil ayuda.
José Hidalgo
Saludos.
Saludos.
Por favor pueden decirme como sumar de varias hojas una serie de rangos, por ejemplo en 7 hojas tengo las ventas de todos mis productos, cada hoja es una sala de ventas y no se vende lo mismo en todas. Como puedo consolidar la información de la venta de cada producto sumando las ventas de cada sala? es con la funcion BUSCARV? o como?
Yo tambien quisiera saber eso
No logro que funcione en mi caso. Tengo X cantidad de hojas y de todas necesito sumar el rango de Q8 a Q15 los que sean >0. Si me pueden ayudar por favor.
Ayuda por favor…..
Ana, sube tu archivo al foro (www.ayudaexcel.com/foro). Te echaré una mano con tu consulta.
hola que tal tengo una duda, si yo quisiera que en el administrador de nombres fuera dinamico como tendría que hacer esa operacion? por ejemplo quiero sumar valores de enero a cierto mes dependiendo del valor que tome el ultimo mes con ayuda de una lista desplegable?
Hola, muchas gracias por tu explicación.
He usado su fórmula para mi caso concreto, pero no funciona dado que tengo dos criterios en vez de uno como usted.
Le pego mi fórmula:
=SUMA(SUMAR.SI(INDIRECTO(Anual&”!$A$9:$A$100″;Tabla2[[#Esta fila];[NOMBRE]];INDIRECTO(Anual&”!B$9:B$9″)))
Si supiera donde puede estar el erro se lo agradecería muchísimo.
Un saludo.
Esta formula es lo que necesito urgente, sin embargo no me sale….
Hector, si has copiado y pegado la fórmula desde la web, comprueba que las comillas pegadas son las correctas. En muchas ocasiones se pegan las comillas inclinadas y la fórmula no funciona.
En caso de que no se solucione, entra en el foro de la web y plantea ahí el caso. Cuando vea tu mensaje te responderé.
Gracias por el ejemplo, es muy util!!!
buen dia,
tu ejemplo esta bien ilustrado he podido aplicarlo, me habeis ahorrado un buen tiempo.
no soy tan experto pero con esta formula me siento super.
saludos y gracias
Hola Sergio, como haría si el número de hojas fuera variable, en el libro se pueden agregar o quitar hojas.
Pude armar una macro con una hoja “indice” donde pongo en columnas el resto de las otras hojas.
Muchas gracias
Hugo, para un número de hojas variable, tienes dos opciones:
– Recurrir a las macros.
– Crear un índice en una hoja con el nombre de todas las hojas del libro. Desde el administrador de nombres habría que hacer referencia a ese índice.
No se me ocurre mucho más. Entra en el foro de Ayuda Excel y plantea ahí la consulta. Seguro que otros usuarios te responderán rápido.
buenas tardes
sergio te agradezco mucha si me puedes ayudar con esto: estoy utilizando las formula sumar.si entre libros pero el libro donde extraigo la información debe estar abierto de lo contrario al actualizar el vinculo me arroja error #¡VALOR!, hay alguna manera de que funcione pero sin necesidad de tener este libro abierto.
gracias
Julián, la única forma que se me ocurre de recuperar los datos que pides es utilizando una macro. No obstante te invito a que hagas la consulta en el foro de la web. Entre todos te podremos ayudar mejor y más rápido.
buenas tardes quisiera saber si tambien existe la manera de utilizar contar,si,conjunto de varias hojas en excel me podrias ayudar porfavor.
Saludos
Carlos, la verdad es que no se me ha dado nunca esa necesidad, pero se puede hacer. Sólamente tendrías que utilizar referencias en 3D, del tipo:
=SUMA(Hoja1:Hoja2!B2:B4)
Este fórmula suma los valores del rango B2:B4 de la Hoja1 y la Hoja2.
Hola. Quisiera saber si se puede usar la formula sumar.si.conjunto en varias hojas. Como sería la formula?? Gracias
Marcelo, mira el comentario anterior.
Hola, una pregunta:
La siguiente fórmula me permite contar de una columna todos los “H”
=CONTAR.SI(H2:H36,”H”)
Pero yo deseo hacer una fórmula para que cuente los que escribieron menos de 5 pero no más de diez. Otra fórmula que cuente menos de 20 y más de diez.
Me puedes ayudar con esto?
Mil gracias
Martha
Martha, te sugiero que entres en el foro de Ayuda Excel donde podrás subir un archivo de ejemplo con lo que quieres conseguir. Es complicado ayudarte sin ver los datos.
He seguido los pasos pero no me sale 🙁
Levo el calculo de somisiones los cuales son en base a cobranza pero hay una política, de acuerdo al numero de días vencidos es el porcentaje de la comisionen que va del 100% al 0% y al final llevo una hoja con la suma de todo lo cobrado mensual, sin embargo no lo tengo desglozado cuanto fue por cada porcentaje, lei este articulo y me ayudaría mucho pero no me sale mi formula, podrías ayudarme, como puedo enviarte mi archivo para que me indiques en que la estoy regando, Gracias
Lizbet, si me quieres enviar el archivo, hazlo a través del foro de la web. Desde allí te ayudaremos rápidamente.
Hola Sergio,
Te planteo un problema a ver si me puedes ayudar. Tengo una tabla con dos columnas, nombre y coste. Quisiera sumar el coste de todas las personas que aparecen en otra tabla. Es decir, tengo una lista de nombres incluidos en una tabla llamada Mercado y que es variable. Lo he hecho con esta fórmula pero no he conseguido nada. {=SUMAR.SI($A$48:$A$80;Mercado[Mercado];Costes!E48:E80)}. La única forma viable que he visto por ahora es poner directamente en la condición cada una de las celdas y sumarlas todas;
=SUMAR.SI($A$48:$A$80;datos!B3;E48:E80)+SUMAR.SI($A$48:$A$80;datos!B5;E48:E80)+SUMAR.SI($A$48:$A$80;datos!B5;E48:E80)
Gracias.
Javier, para tu cuestión no es necesario que la fórmula sea matricial. Con un SUMAR.SI que apunte a la otra hoja, sería suficiente. Este ejemplo sería para cuando quieres sumar varias hojas diferentes.
Hola Sergio,
tengo un problema y es que la fórmula sólo me coge la primera hoja. No entiendo por qué ya que en principio lo he definido en el administrador de nombres como indicas. Sin embargo, en evaluar fórmula veo que solo me coge la primera hoja. ¿A qué puede ser debido?
¿Algún complemento quizás?
Gracias
Hola, Lorena!
Por favor, comprueba que los nombres de las hojas van entre comillas y que el separador de argumentos es el que utilizas habitualmente (, ó ;).
Comprueba también que al introducir la fórmula pulsas Ctrl + Mayús + Intro, ya que se trata de una fórmula matricial.
Te recomiendo que descargues el ejemplo y adaptes el nombre y la fórmula a tu libro.
Hola Sergio, quiero agradecerte por lo que has publicado porque nos ayuda mucho ya que habia perdido mucho tiempo buscando la solucion, sin embargo, la formula me da un error cuando trato de grabarla con Ctrl + Mayús + Intro ya que me dice que hay un problema con esta formula ya que me sombrea las comillas, pero las he escrito de diferente forma y no me deja meter la formula
Pudieras por favor ayudarme.
Saludos.
¡Hola, Mireya!
Si has seguido todos los pasos, sólo se me ocurre que el problema puede deberse a los separadores de argumentos. ¿Utilizas punto y coma o coma?
Te recomiendo que te descargues el archivo de ejemplo y observes el nombre definido y la fórmula que contiene.
Muchas gracias por este gran aporte, me fue de gran utilidad y mejor aún complementado con el ejemplo.
Gracias Sergio por compartir tus conocimientos es para mi de mucha ayuda y muy util
Hola Sergio,
Acabo de leer la publicación y me parece superutil. Quisiera plantearte la opción de hacer una formula similar pero con referencias a varias tablas en lugar de a varias hojas. ¿Sería posible?, he hecho pruebas y no me va.
Muchas gracias
Hola, Sergio! Cómo estás?
También he intentado aplicar la fórmula para buscar en 4 tablas que se encuentran en 4 hojas, creé entonces el nombre haciendo referencia a las cuatro tablas, pero la fórmula no va. Quisiera saber que estoy haciendo mal, si me puedes indicar por favor.
Quedo atenta, gracias de antemano.
Hola Daniela!
Para darte una respuesta apropiada tendrías que indicarme algunos detalles más como la fórmula exacta que estás utilizando, los nombres que has creado, etc.
Si no, no te podré echar una mano.
Gracias Sergio, me costó pero lo logré. (no se como…). Sirvió muchísimo tu post y te agradezco un montón.
Me quedaron algunas dudas de como funciona INDIRECTO, buscaré videos para seguir perfeccionandome.
Saludos.
Yan