Formulas Excel

Ejemplos de Formulas Excel

  • Home
  • Apps
    • Formas de Pago
    • Activacion
    • Configurar
    • Fallas
  • Sobre mi
  • Contacto
  • FAQ
  • Consulta

Macro para suma condicional en múltiples hojas Excel

por Nolberto Lopez 16 Comments

Ahora veremos como se puede aplicar una macro para realizar una suma condicional con información contenida en múltiples hojas, esto es, con un solo clic poder obtener un total en base a una condición y datos que se encuentran en diferentes hojas, esta macro es de gran ayuda en formatos contables que requieren procesar muchos datos contenidos en varias hojas.

Plantillas Contables en Excel

Particularmente no me gusta separar datos del mismo tipo o de la misma naturaleza en distintas hojas, prefiero poner todo en una sola hoja como base de datos, de ese modo es mas sencillo trabajar después con esos datos, pero si tu prefieres separar por ejemplo, las ventas mensuales, una hoja para cada mes o incluso una hoja para cada día, entonces este ejemplo te sera de enorme ayuda.

Para poder hacer ese consolidado o suma de algún dato en particular, sera necesario recorrer las hojas del libro que contienen la informacion necesaria para ese caso, no importa si son 10 o 100 hojas, la idea es que se haga de una manera rápida y automatizada, con un solo clic.

Cabe precisar que la estructura de todas esas hojas que contienen la informacion deberán tener la misma estructura, es decir, que tengan las mismas columnas de datos en el mismo orden.

Obtener el total de ventas de un vendedor en particular

La idea en este ejemplo es poder obtener el total de ventas que un vendedor en particular ha realizado, para ello sera necesario simplemente indicar el nombre del vendedor y al presionar en un botón deberá recorrerse  todas las hojas del libro ( excepto la que contendrá la suma ), buscar el nombre de ese vendedor en la columna que corresponda y sumar el importe de la venta de cada registro donde aparece.

macro recorre hojas para suma condicional - registro de ventas

En la primera hoja del libro se realizara dicha suma, para ello usaremos una celda donde vamos a poner en nombre del vendedor, en la celda de a lado deberá aparecer la suma al momento de presionar en un botón.

macro recorre hojas para suma condicional - hoja de consolidado

La celda donde se pone el nombre del vendedor tiene una lista desplegable que esta ligada a una hoja donde esta la lista de todos los vendedores, de ese modo se podrá seleccionar el nombre de esa lista.

macro recorre hojas para suma condicional - lista vendedores

Este libro contiene múltiples hojas, la macro deberá recorrer a partir de la tercera hoja, las dos primeras no porque la primera es donde se realizara la suma y la segunda es la que contiene la lista de vendedores.

macro recorre hojas para suma condicional - lista de hojas

Macro para recorrer las hojas y hacer la suma condicional

La siguiente macro es la que recorre las hojas y realizara la suma de las ventas del vendedor seleccionado en la primera hoja.

Option Explicit

Sub SumaDato()
    Dim i, j As Integer
    Dim filas As Integer
    Dim Vendedor As String
    Dim Suma As Double

    Vendedor = range("nombre")
    Suma = 0

    For i = 3 To Worksheets.Count
        filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
        For j = 2 To filas
            If Worksheets(i).Cells(j, 3) = Vendedor Then
                Suma = Suma + Worksheets(i).Cells(j, 5).Value
            End If
        Next j
    Next i
    range("total").Value = Suma

End Sub

Explicando la macro que recorre las hojas y hace la suma

Dim i, j As Integer
Dim filas As Integer
Dim Vendedor As String
Dim Suma As Double

En esa parte se definen las variables que usaremos en el proceso

Vendedor = range("nombre")

A la variable vendedor le asignamos el dato que ponemos en la celda del nombre del vendedor en la primera hoja, a esa celda le ponemos un nombre de rango, en este caso “nombre”.

Suma = 0

Aquí se inicia la variable suma en cero.

Vamos a recorrer las hojas con un ciclo for.

For i = 3 To Worksheets.Count

i es el contador, en este caso el contador inicia en 3 porque es el numero de la hoja donde iniciaremos, recordar que las dos primeras no se recorren, con Worksheets.Count obtenemos en numero de hojas que tiene el libro, así que con ese código le estamos diciendo que recorreremos desde 3 hasta n numero de hojas.

filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row

Con ese código obtenemos el numero de la ultima fila con datos en la hoja que en ese momento se esta procesando.

For j = 2 To filas

Con este código le decimos que vamos a recorrer las filas de esa hoja a partir de la numero dos, hasta la ultima fila con datos.

If Worksheets(i).Cells(j, 3) = Vendedor Then
    Suma = Suma + Worksheets(i).Cells(j, 5).Value
End If

Este código es el que hace la comparación del nombre de empleado que se pone en la primera hoja, mismo que esta referenciado con la variable “Vendedor” contra el nombre del vendedor que se encuentra en la fila que en ese momento esta recorriendo la macro, si es igual entonces sumara la cantidad que esta en la columna que contiene el importe de la venta realizada, si no es igual continuara con el bucle para ir a la siguiente fila.

Una vez que termina de recorrer todas las hojas, se pone el valor de la variable suma en la celda donde va el total de la primera hoja del libro, justo a lado del nombre del vendedor seleccionado.

range("total").Value = Suma

Como hemos visto, esta macro es muy corta, pero a la vez muy eficiente y poderosa, espero que les sea de mucha ayuda, estaré atento a sus comentarios.

A continuación les dejo el vídeo con la explicación para una mejor comprensión del ejercicio.

https://youtu.be/F3LEKzr6Ql4

Descargar suma condicional en múltiples hojas

Categoría: Macros

Ingresa tu e-mail:

About Nolberto Lopez

Experto en Microsoft Excel. Desarrollador de aplicaciones en Excel con VBA. Autor del sitio formulasexcel.com

« Control de Gastos con Formulas Excel

Comentarios

  1. Luis Martínez dice

    septiembre 1, 2020 at 10:54 pm

    Si en lugar de la suma acumulada quisiera el promedio, ¿cómo sería?

    Responder
  2. Manuel dice

    julio 30, 2018 at 3:20 am

    necesito hacer esto pero para diferentes archivos

    ayuda plisssssss

    Responder
  3. Edgar Acedo dice

    marzo 27, 2018 at 5:32 pm

    Buenos días Nolberto, gracias por su aporte, lo he probado y me ha funcionado, sin embargo necesito habilitarlo para que se repita en varias celdas a la vez, he visto que alguien comentó esta duda en su video en youtube, pero no me ha quedado clara la respuesta:

    Formulas Excel
    Hace 7 meses
    Si es posible, para ello es necesario meter el ciclo for que se indica en el video, dentro de otro ciclo.

    Sub test()
    Dim VendedorAs String
    Range(“a1”).Activate
    Do While ActiveCell.Value “”
    Vendedor= ActiveCell.Value
    for i = 1 to Worksheets.Count
    ….
    …..
    Next i
    Loop
    End Sub

    De qué manera se añadiría esta información en el macro original? Gracias de antemano, que tenga buen día.

    Responder
    • Camila dice

      marzo 12, 2019 at 2:00 am

      Tengo esta misma duda, lo he intentado de varias formas pero no lo consigo. Por favor colaborenos

      Responder
      • Gabriel dice

        septiembre 25, 2019 at 2:30 am

        Me podrias ayudar, tambien intento hacerlo pero no se como

        Responder
    • Juan Gonzalez dice

      marzo 17, 2020 at 5:58 pm

      Hola muchas gracias por la ayuda….tengo unda duda quedaria masomenos asi? para que sume sin poner listas? es decir que escriba yo los valores que quiero y me los valla acomodando en cada una de las filas…. la vdd no lo entiendo muy bien.. una diculpa,, nos e donde meter ese ciclo…. para que se repita con varios valores o no en una lista…

      Option Explicit

      Sub SumaDato()
      Sub test()
      Dim VendedorAs String

      Dim i, j As Integer
      Dim filas As Integer
      Dim Vendedor As String
      Dim Suma As Double

      Vendedor = range(“nombre”)
      Suma = 0

      Range(“a1”).Activate
      Do While ActiveCell.Value “”
      Vendedor= ActiveCell.Value
      for i = 1 to Worksheets.Count
      For i = 3 To Worksheets.Count
      filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
      For j = 2 To filas
      If Worksheets(i).Cells(j, 3) = Vendedor Then
      Suma = Suma + Worksheets(i).Cells(j, 5).Value
      End If
      Next j
      Next i
      range(“total”).Value = Suma
      Next i
      Loop
      End Sub
      End Sub

      Responder
      • paola hernandez dice

        mayo 27, 2020 at 3:00 pm

        Buen dia Juan, no he podido resolver la formula, esta que ud publica no me arranca.

        Responder
      • Nolberto Lopez dice

        mayo 27, 2020 at 8:32 pm

        Se debe contar con un bucle exterior que recorra la columna que contiene los nombres en la hoja destinada para ello, luego en cada ciclo se debe obtener el nombre y poner en 0 la suma.

        Por cada nombre se genera un ciclo para todas las hojas y en cada hoja hay otro ciclo para recorrer las filas de la hoja activa en el momento.

        Al final de cada ciclo exterior se pone la suma del nombre que corresponde, en el ejemplo se considera que la suma va en la columna siguiente a la de los nombres.

        Sub testSumaHojas()
        Dim i, j As Integer
        Dim filas As Integer
        Dim Vendedor As String
        Dim Suma As Double
        Dim rng As Range
        Dim r As Range
        Dim uFila As Long
        Application.ScreenUpdating = False

        uFila = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row

        ‘Sheets3 es el nombre interno de la hoja donde esta la lista de vendedores, los cuales estaran en la col B a partir de la fila 1
        Set rng = Sheet3.Range(“B2:B” & uFila)

        For Each r In rng
        Vendedor = r
        Suma = 0
        For i = 1 To Worksheets.Count
        filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
        For j = 2 To filas
        If Worksheets(i).Cells(j, 3) = Vendedor Then
        Suma = Suma + Worksheets(i).Cells(j, 5).Value
        End If
        Next j
        Next i
        r.Offset(0, 1) = Suma
        Next r
        Application.ScreenUpdating = True
        End Sub

        Responder
        • Julio Enrique Barrenechea Montenegro dice

          junio 19, 2020 at 3:07 am

          Buenas noches Sr Nolberto, quise aplicar la macro que envio, a la misma lista de vendedores que explico en You tube, pero no me corre la macro, reemplace la anterior macro por la nueva, me sale error de compilación y me señala sheet 3.
          Lo que necesito para mi proyecto es que sume el total y lo coloque al lado de cada nombre en un lista la suma, pero no la lista desplegable. osea vendedor1 suma total, vendedor2 suma total, vendedor3 suma total y asi sucesivamente hasta el final

          Responder
  4. Saul LA dice

    febrero 5, 2018 at 6:05 pm

    Sr. Nolberto Lopez muchas muchas gracias por el grandisimo aporte, lo he adaptado a mi proyecto y me ha funcionado de maravilla, Dios le bendiga!
    Saludos!!

    Responder
  5. Raúl JIRÓN dice

    agosto 26, 2017 at 11:18 pm

    Muchas gracia por su aporte, me a servido mucho, lo modifique para mi trabajo, agradecido desde Talcahuano, Chile

    Responder
  6. Danilo dice

    junio 16, 2016 at 7:30 am

    Excelente ejercicio
    Gracias por compartir tus conocimientos.
    Danilo

    Responder
  7. Mario Herrera dice

    junio 15, 2016 at 9:54 pm

    Gracias por tu Ayuda, Nolberto

    Responder
  8. Danilo dice

    junio 14, 2016 at 8:23 pm

    Excelente ejercicio.
    Servirá de mucho.
    Gracias por compartir conocimiento.
    Saludos!!!!!

    Responder
  9. RAYMUNDO VALENZUELA dice

    junio 14, 2016 at 6:45 pm

    no funciona correctamente

    Responder
    • Nolberto Lopez dice

      junio 14, 2016 at 8:00 pm

      Saludos Raymundo.

      Puede descargar de nuevo.

      Se sustituye

      filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp)
      por
      filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row

      Responder

Deja un comentario Cancelar respuesta

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

Ponte en contacto conmigo para definir detalles de tu nuevo proyecto.

Contactar
  • INICIO
  • CONTACTO
  • APLICACIONES
  • CONTABLES
  • VIDEOS
  • CATEGORIAS
  • VBA EXCEL

Copyright © 2021 · Formulas Excel · by Nolberto Lopez

Copyright © 2021

Ingresa tu e-mail: