36

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 (5641 clics)
Tamaño: 23 KB

 

 

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 36 comentarios
Danilo

Excelente ejemplo.

Responder
Sergio

Gracias, Danilo.

Espero que te haya resultado útil.

Responder
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

Responder
Abraham Padrón

bueno, también puedes utilizar la función de suma conjunto

Responder
Sergio

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

Saludos.

Responder
Sergio

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.

Responder
Ivot

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?

Responder
ANA MONTENEGRO

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.

Responder
ANA MONTENEGRO

Ayuda por favor…..

Responder
    Sergio

    Ana, sube tu archivo al foro (www.ayudaexcel.com/foro). Te echaré una mano con tu consulta.

    Responder
so080988

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?

Responder
Javier

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.

Responder
hector

Esta formula es lo que necesito urgente, sin embargo no me sale….

Responder
    Sergio

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

    Responder
Sarah

Gracias por el ejemplo, es muy util!!!

Responder
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

Responder
Hugo

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

Responder
    Sergio

    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.

    Responder
julian

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

Responder
    Sergio

    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.

    Responder
Carlos

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

Saludos

Responder
    Sergio

    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.

    Responder
Marcelo

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

Responder
    Sergio

    Marcelo, mira el comentario anterior.

    Responder
Martha

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

Responder
    Sergio

    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.

    Responder
Lizbet Yanez

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

Responder
    Sergio

    Lizbet, si me quieres enviar el archivo, hazlo a través del foro de la web. Desde allí te ayudaremos rápidamente.

    Responder
javier

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.

Responder
    Sergio

    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.

    Responder
Lorena

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

Responder
    Sergio

    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.

    Responder
Mireya

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.

Responder
    Sergio

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

    Responder
Cómo funcionan SUMAR.SI y SUMAR.SI.CONJUNTO - Ayuda Excel

[…] Cómo utilizar SUMAR.SI en varias hojas […]

Responder
Marisol

Muchas gracias por este gran aporte, me fue de gran utilidad y mejor aún complementado con el ejemplo.

Responder

Escribe una respuesta:

Esta web usa cookies propias y de terceros para mejorar tu experiencia de navegación, elaborar información estadística y analizar tus hábitos de navegación. Si no estuvieras conforme puedes eliminarlos. Al hacer clic en "Aceptar" o si continúas navegando, aceptas su uso. Puedes revocar tu consentimiento en cualquier momento. Leer más

Los ajustes de cookies de esta web están configurados para «permitir cookies» y así ofrecerte la mejor experiencia de navegación posible. Si sigues utilizando esta web sin cambiar tus ajustes de cookies o haces clic en «Aceptar» estarás dando tu consentimiento a esto.

Cerrar