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:

  1. Abre el Administrador de nombres que se encuentra en la ficha Fórmulas.
  2. Pulsa en el botón Nuevo para crear un nombre.
  3. 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.
  4. 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!!

sumar_si_en_varias_hojas
Título: sumar_si_en_varias_hojas (1008 clics)
Tamaño: 23 KB

 

 

Cómo llegar a fin de mes con menos estrés gracias a Excel

Antes del día 15 puedes tener todos los objetivos mensuales completados. Y los que no, dejarlos programados para que se cumplan sin requerir de tu tiempo.

He preparado 7 de mis mejores trucos explicándote cómo lo hago. Deja tu correo abajo y te enviaré el primero de ellos.

41 comentarios en “Cómo utilizar la función SUMAR.SI en varias hojas”

  1. José Hidalgo
    Gracias, es excelente.

    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

  2. José, bastaría con modificar el nombre que has definido desde el Administrador de nombres para que haga referencia a las nuevas hojas.

    Saludos.

  3. Abraham, lo que planteas es una opción mucho más sencilla, pero no es aplicable a versiones antiguas de Excel. Simplemente quería demostrar que antes de que Microsoft publicara funciones "modernas" (si se pueden llamar así), se podían hacer este tipo de acciones.

    Saludos.

  4. 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?

  5. 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?

  6. 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.

    1. 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é.

  7. juan de dios briones

    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

  8. 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

    1. 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.

  9. 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

    1. 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.

  10. buenas tardes quisiera saber si tambien existe la manera de utilizar contar,si,conjunto de varias hojas en excel me podrias ayudar porfavor.

    Saludos

    1. 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.

  11. Hola. Quisiera saber si se puede usar la formula sumar.si.conjunto en varias hojas. Como sería la formula?? Gracias

  12. 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

  13. 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

  14. 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.

    1. 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.

  15. 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

    1. 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.

  16. 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.

    1. ¡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.

  17. 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

    1. Daniela Pérez

      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.

      1. 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.

  18. 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

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Sergio

Sergio

Experto formador en Excel y Power BI con más de dos décadas de experiencia. Capacito a profesionales para optimizar su trabajo y ser más eficientes. Con un enfoque práctico y cercano, mi objetivo es ayudarte a dominar estas herramientas esenciales. Descubre mis formaciones.MVP de Microsoft 5 años consecutivos.

Cómo llegar a fin de mes con menos estrés gracias a Excel

Antes del día 15 puedes tener todos los objetivos mensuales completados. Y los que no, dejarlos programados para que se cumplan sin requerir de tu tiempo.

He preparado 7 de mis mejores trucos explicándote cómo lo hago. Deja tu correo abajo y te enviaré el primero de ellos.

Buscar

Últimos posts

¿De qué hablo aquí?

Cómo llegar a fin de mes con menos estrés gracias a Excel

Antes del día 15 puedes tener todos los objetivos mensuales completados. Y los que no, dejarlos programados para que se cumplan sin requerir de tu tiempo.

He preparado 7 de mis mejores trucos explicándote cómo lo hago. Deja tu correo abajo y te enviaré el primero de ellos.