Anónimo
¿Se puede hacer una fórmula o macro para que recorra varias hojas contando lo que le especifique en otra celda?
Lo que necesito es una especie de contador, mi archivo se basa en 13 hojas, 12 de ellas son los meses del año, les puse como nombre a cada una las 3 primeras letras de cada mes (Ene, Feb, Mar...). En cada hoja se llevará un registro de gastos por categoría, entonces, en la hoja número 13 tengo 3 celdas, una de ellas es para seleccionar la categoría que deseo consultar la cantidad de gastos que hice, tiene una lista desplegable con todas las categorías de gastos que uso, en la segunda celda hay una lista desplegable con el nombre de cada hoja (NOMBRE DEL MES) y en la tercera celda va la fórmula o macro para que cuente los gastos dependiendo de la categoría y el mes que seleccione. OJO: En la primera celda cree una opción llamada "En general" que es para contar todas las categorías y en la segunda celda cree una opción llamada "Todos", que es para contar los gastos anuales, es decir todos los meses. Si selecciono "En general" en la primera celda y en la segunda pongo un mes, me contará todos los gastos que hice ese mes, si selecciono "General" y "Todos" me contará todos los gastos que hice en el año y si selecciono una categoría en la primera celda y en la segunda pongo "Todos", me contará todos los gastos que hice en el año con relación a dicha categoría.
1 respuesta
H0la Chrisr:
Sube una captura de uno de los meses para ver la estructura de la hoja.
La solción que habíamos implementado en el caso anterior (con fórmulas) queda obsoleta frente a este caso, obligándonos a implementar una macro. Quizá con una fórmula se podría hacer, pero quedaría monstruosa.
S@lu2
Esa es la estructura de las 12 hojas del mes... todos los datos van desde la fila 6 a la fila 200.
H0la Chrisr:
Costó, pero salió. Te dejo la macro para hacer lo que solicitas
Function fcnCalcular(rngCeldaCat As Range, rngCeldaMes As Range) As Double
'Por GP'
Dim strMeses() As Variant
Dim i As Integer, nFilas As Integer
strMeses = Array("Todos", "ene", "feb", "mar", _
"abr", "may", "jun", "jul", _
"ago", "sep", "oct", "nov", "dic")
If LCase(rngCeldaMes) = "todos" Then 'Todos los meses'
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
For i = 1 To 12
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
fcnCalcular = fcnCalcular + WorksheetFunction.Sum(Sheets(strMeses(i)).Range("E6:E" & nFilas))
Next
Else
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
fcnCalcular = fcnCalcular + WorksheetFunction.SumIf( _
Sheets(strMeses(i)).Range("A6:A" & nFilas), _
LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range("E6:E" & nFilas))
Next
End If
Else 'Un mes específico'
With Sheets(LCase(rngCeldaMes))
nFilas = .Range("E200").End(xlUp).Row
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
fcnCalcular = WorksheetFunction.Sum(.Range("E6:E" & nFilas))
Else 'Una categoría específica'
fcnCalcular = WorksheetFunction.SumIf( _
.Range("A6:A" & nFilas), _
LCase(rngCeldaCat), _
.Range("E6:E" & nFilas))
End If
End With
End If
End FunctionDebes crear un módulo y agregar esta función en la sección de código.
En tu planilla, en la celda en que quiere que arroje el resultado, agregas esto
=fcnCalcular(D13;F13)
Donde D13 sería la celda en que seleccionas la categoría por la que deseas consultar y F13 es la celda en la que seleccionas el mes por el que deseas consultar.
S@lu2
¡Gracias! Me sirvió bastante, solo me gustaría corregirle lo siguiente:
¿Cómo puedo hacer para que cuando estén vacías las dos casillas, no me muestre ese error, es decir, que se quede en blanco.
En tu celda, en lugar de utilizar
=fcnCalcular(D13;F13)
Utiliza
=SI.ERROR(fcnCalcular(D13;F13);"")
De esa forma puedes decirle exactamente lo que quieres mostrar en lugar de un error.
S@lu2
¡Gracias! Me funcionó. :)
Saludos, la macro me está presentando un error, solo me presenta el monto, si gaste 100 US en la categoría de Ahorros, solo me muestra el 100.
Solo me está sumando, no me está contando los gastos.
¿Qué crees que sea?
Pues, eso es exactamente el objetivo para lo que fue programada la macro, para SUMAR. Según lo que entendí de la pregunta inicial, tenemos sólo una celda de destino, por lo que mi consulta es ¿Quieres la suma o quieres la cuenta de los gastos realizados?
S@lu2
Quiero la cuenta, solo va a contar los gastos, te pondré de nuevo las especificaciones:
Lo que necesito es una especie de contador, mi archivo se basa en 13 hojas, 12 de ellas son los meses del año, les puse como nombre a cada una las 3 primeras letras de cada mes (Ene, Feb, Mar...). En cada hoja se llevará un registro de gastos por categoría, entonces, en la hoja número 13 tengo 3 celdas, una de ellas es para seleccionar la categoría que deseo consultar la cantidad de gastos que hice, tiene una lista desplegable con todas las categorías de gastos que uso, en la segunda celda hay una lista desplegable con el nombre de cada hoja (NOMBRE DEL MES) y en la tercera celda va la fórmula o macro para que cuente los gastos dependiendo de la categoría y el mes que seleccione. OJO: En la primera celda cree una opción llamada "En general" que es para contar todas las categorías y en la segunda celda cree una opción llamada "Todos", que es para contar los gastos anuales, es decir todos los meses. Si selecciono "En general" en la primera celda y en la segunda pongo un mes, me contará todos los gastos que hice ese mes, si selecciono "General" y "Todos" me contará todos los gastos que hice en el año y si selecciono una categoría en la primera celda y en la segunda pongo "Todos", me contará todos los gastos que hice en el año con relación a dicha categoría.
La estructura de las 12 hojas está en una foto más arriba.
Tal vez estás confundido, ese es el contador, lo que necesito, pero te hice otra pregunta aparte que es para la suma solamente, entonces en esa pregunta de la suma te dejé un comentario de un problema que me está pasando y me dijiste que me ibas a responder por aquí, es decir, son dos preguntas diferentes, El contador y La suma condicional.
¿Quieres que te haga la pregunta de la suma aparte para que no te confundas y sea mejor para ti?
No, no es necesrio que hagas la pregunta nuevamente, pero necesito salir de otra duda para ver si podemos resolver ambas cosas de una vez.
¿La cuenta que deseas actúa bajo los mismos criterios que la suma?
A lo que voy es:
Si seleccionas "Enero" con la categoría "Ahorro", según tu ejemplo ¿la suma debiera ser igual a 5 y la cuenta igual a 2?
Y si en es seleccionas "Todos" y en categoría "En general" ¿La suma debiera ser de todos los registros de todos los meses y la cuenta de todos los registros de todos los meses?
En otras palabras ¿Bajo los mismos criterios se hace tanto la suma como la cuenta?
Saludos
Muchas gracias por tu ayuda. Sí, es exáctamente como dijiste, también es bueno tomar en cuenta esto:
OJO: En la primera celda cree una opción llamada "En general" que es para contar todas las categorías y en la segunda celda cree una opción llamada "Todos", que es para contar los gastos anuales, es decir todos los meses. Si selecciono "En general" en la primera celda y en la segunda pongo un mes, me contará todos los gastos que hice ese mes, si selecciono "General" y "Todos" me contará todos los gastos que hice en el año y si selecciono una categoría en la primera celda y en la segunda pongo "Todos", me contará todos los gastos que hice en el año con relación a dicha categoría.
En todas las condiciones es la misma, ya me habías ayudado con la suma, pero tuve que modificar la estructura de las 12 hojas, los datos partiran de la fila 6 a la fila 200 y posee 5 columnas, te adjuntaré una foto:
Traté simplemente de modificar el rango de cada suma en la fórmula que hiciste, pero no me funcionó.
Perfecto!
Vamos a reemplazar la macro anterior por la siguiente
Function fcnCalcular(rngCeldaCat As Range, rngCeldaMes As Range, _
Optional strOp As String = "CUENTA") As Double
'Por GP'
Dim strMeses() As Variant
Dim strCeldaCat As String, strCeldaMes As String, strCeldaResult As String
Dim i As Integer, nFilas As Integer
strMeses = Array("Todos", "ene", "feb", "mar", _
"abr", "may", "jun", "jul", _
"ago", "sep", "oct", "nov", "dic")
If LCase(rngCeldaMes) = "todos" Then 'Todos los meses'
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
For i = 1 To 12
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.Sum(Sheets(strMeses(i)).Range("E6:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.Count(Sheets(strMeses(i)).Range("E6:E" & nFilas))
End If
Next
Else
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.SumIf( _
Sheets(strMeses(i)).Range("A6:A" & nFilas), _
LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range("E6:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.CountIf( _
Sheets(strMeses(i)).Range("A6:A" & nFilas), _
LCase(rngCeldaCat))
End If
Next
End If
Else 'Un mes específico'
With Sheets(LCase(rngCeldaMes))
nFilas = .Range("E200").End(xlUp).Row
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.Sum(.Range("E6:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.Count(.Range("E6:E" & nFilas))
End If
Else 'Una categoría específica'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.SumIf( _
.Range("A6:A" & nFilas), _
LCase(rngCeldaCat), _
.Range("E6:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.CountIf( _
.Range("A6:A" & nFilas), _
LCase(rngCeldaCat))
End If
End If
End With
End If
End FunctionLuego, en la celda en la que necesitas que esté la cuenta de movimientos, agregas esto
=SI.ERROR(fcnCalcular(D13;F13);"")
Y en la cenda donde necesitas que esté la suma de los movimientos te olvidas de la fórmula y agregas esto
=SI.ERROR(fcnCalcular(D13;F13;"SUM");"")
Si el tercer parámetro de la función fcnCalcular es SUM, entonces sumará y si es algo distinto, entonces contará.
Puébalo y me comentas.
S@lud2
Saludos, muchas gracias, funciona a la perfección, pero me gustaría saber si yo puedo reutilizar esa misma macro para otra celda... Es decir, en las 12 hojas, de la columna B6:B200 van las subcategorías, ¿Se podría en la hoja número 13 hacer lo mismo que con la categoría? Es decir, que cuando yo ponga la subcategoría me sume lo que gasté. La celda en donde está la subcategoría es la B32 y la celda en donde está el mes es la misma; F13. Intenté modificar esta formula que me diste a ver si funcionaba: "=SI.ERROR(fcnCalcular(D13;F13;"SUM");"")", y la puse así: "=SI.ERROR(fcnCalcular(B32;F13;"SUM");"")", pero no me funcionó. ¿Crees que la pueda reutilizar o hay que modificar la macro?
Dependiendo de lo que quieras, la modificación de la macro puede ser en menor o mayor medida.
Si lo que quieres es:
- Dada una subcategoría y el mes, obtener la suma, no es mucho el cambio.
- Dada una categoría, una subcategoría y el mes, obtener la suma, entonces se nos va a ampliar bastante la macro.
¿Cuál es tu caso, el 1 o el 2?
S@lu2
Saludos.
Mi caso es el número 1... Dada la categoría se me activará en la celda B32 una lista desplegable (Ya me habías ayudado con eso), luego pongo el mes... hasta ahí lo tengo bien, lo que quiero es que cuando se me habilite las lista desplegable en la celda B32 (Subcategoría), y seleccione una, me haga una suma de todos los gastos con esa subcategoría... va con las mismas condiciones de la macro madre, la que me mandaste hace un momento.
Ok, hice un pequeño cambio en la macro, por lo que tendrás que hacer un cambio en la forma en que se agrega la función a la celda.
Function fcnCalcular(rngCeldaCat As Range, strColCat As String, rngCeldaMes As Range, _
Optional strOp As String = "CUENTA") As Double
'Por GP'
Dim strMeses() As Variant
Dim strCeldaCat As String, strCeldaMes As String, strCeldaResult As String
Dim i As Integer, nFilas As Integer
strMeses = Array("Todos", "ene", "feb", "mar", _
"abr", "may", "jun", "jul", _
"ago", "sep", "oct", "nov", "dic")
If LCase(rngCeldaMes) = "todos" Then 'Todos los meses'
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
For i = 1 To 12
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.Sum(Sheets(strMeses(i)).Range("E6:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.Count(Sheets(strMeses(i)).Range("E6:E" & nFilas))
End If
Next
Else
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.SumIf( _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range("E6:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.CountIf( _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat))
End If
Next
End If
Else 'Un mes específico'
With Sheets(LCase(rngCeldaMes))
nFilas = .Range("E200").End(xlUp).Row
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.Sum(.Range("E6:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.Count(.Range("E6:E" & nFilas))
End If
Else 'Una categoría específica'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.SumIf( _
.Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat), _
.Range("E6:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.CountIf( _
.Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat))
End If
End If
End With
End If
End FunctionPara la cuenta de Categorías (búsqueda en columna A)
=SI.ERROR(fcnCalcular(D13;"A";F13);"")
Para la suma de Categorías (búsqueda en columna A)
=SI.ERROR(fcnCalcular(D13;"A";F13);"SUM")
Para la cuenta de subcategorías (búsqueda en columna B)
=SI.ERROR(fcnCalcular(D13;"B";F13);"")
Para la suma de Categorías (búsqueda en columna B)
=SI.ERROR(fcnCalcular(D13;"B";F13);"SUM")
EL segundo parámetro de fcnCalcular, ahora determina la columna en la que se está buscando el criterio.
S@lu2
Saludos, muchas gracias, quiero corregir algo...
En caso del contador, el de las categorías y subcategorías es el mismo, es decir, solo se van a contar los gastos como tal, cada fila de la tabla que yo llene es un gasto, el contador va a contar cada uno, ¿lo podría unificar?
No entiendo ¿me puedes dar un ejemplo?
Donde dice cantidad, va el contador, es el mismo para todo, donde dice "TOTAL", la celda que está debajo del gráfico, es donde va la suma de cada subcategoría.
El contador va a depender solo de la casilla "Categoría" (D13).
Esta es a macro para contar categorías sin tomar en consideración los meses
Function fcnContarCat(rngCeldaCat As Range, strColCat As String) As Double
'Por GP'
Dim strMeses() As Variant
Dim strCeldaCat As String
Dim i As Integer, nFilas As Integer
strMeses = Array("Todos", "ene", "feb", "mar", _
"abr", "may", "jun", "jul", _
"ago", "sep", "oct", "nov", "dic")
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
For i = 1 To 12
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
fcnContarCat = fcnContarCat + WorksheetFunction.Count(Sheets(strMeses(i)).Range("E6:E" & nFilas))
Next
Else
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
fcnContarCat = fcnContarCat + WorksheetFunction.CountIf( _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat))
Next
End If
End FunctionPara contar las categorías
=SI.ERROR(fcnContarCat(D13;"A"))
Donde el primer argumento es el criterio y el segundo, es el nombre de la columna en que buscará los datos.
S@lu2
Saludos, ¿A qué te refieres con que no tomará en cuenta los meses? Sí los tiene que tomar...
¿Qué haré con la penúltima macro? Esa es la que me ayudaría más porque tiene todo en uno, lo único que había que hacerle es unificar el contador, sí tiene que tener la condición de los meses, es lo mismo que la suma, solo que en vez de sumar, va a contar. A la penúltima macro solo hay que unificarle el contador, como le mandé la foto, hay que especificar la categoría y el mes, luego me dirá la cantidad de gastos que hice, es la penúltima macro, solo que con esta pequeña corrección. Disculpe los inconvenientes, me deja saber cualquier duda, muchas gracias por todo.
Simplemente esa parte de la macro se eliminaría, solo hará un conteo dependiendo de la categoría y el mes.
Cualquier cosa me deja saber por favor.
Ya no entiendo nada.
Si la función fcnContarCat no es lo que quieres, entonces no sé qué es lo que no hace la función fcnCalcular. No entiendo a lo que te refieres cuando dices unificar el contador.
Me dices "El contador va a depender solo de la casilla "Categoría" (D13)" , pues eso es lo que hace fcnContarCat, depender sólo de la casilla Categoría.
---------
FcnCalcular puede hacer lo siguiente:
- Sumar todos los datos de todos los meses si seleccionas "Todos" en la lista mes y "En general" en la lista categorías (No hay diferencia si se trata de subcategorías)[TODO DE TODO EL AÑO]
- Sumar todos los datos de un mes, si seleccionas un mes en la lista meses y "En general" en la lista categoría (puedes alternar entre columna A y B para definir la suma en categoría o subcategoría) [TODO DE UN MES].
- Sumar todos los datos de una categoría específica si seleccionas "Todos" en la lista de meses y, alguna categoría, en la lista de categorías (puedes alternar entre columna A y B para definir la suma en categoría o subcategoría)[TODO EL AÑO DE UNA CATEGORIA]
- Sumar todos los datos de una caategoría específica en un mes específico, si en la lista de meses seleccionas un mes y en la lista de categorías seleccionas una categoría (puedes alternar entre columna A y B para definir la suma en una categoría o subcategoría)[TODO EL MES DE UNA CATEGORIA]
- Exactamente las mismas cuatro cosas que hace con la suma, las puede hacer con la Cuenta.
Entonces vuelvo a pregunta ¿Qué es lo que no hace fcnCalcular?
S@lu2
Si agregas una imagen de la planilla, por favor procura que se muestre el nombre de las filas y de las columnas.
Saludos amigo, entiendo tu punto...
Cuando hablo de unificar al contador, me refiero a que solo habrá una celda en donde se mostrará la cantidad, en la penúltima macro que mandaste pusiste dos conteos:
1-Para la cuenta de Categorías (búsqueda en columna A)
=SI.ERROR(fcnCalcular(D13;"A";F13);"")
2-Para la cuenta de subcategorías (búsqueda en columna B)
=SI.ERROR(fcnCalcular(D13;"B";F13);"")
Entonces, yo solo te digo que si es posible solamente usar en esa macro que mandaste la opción número 1, la #2 quedaría descartada. La penúltima macro quedaría igual, solo que con un solo conteo (EL NúMERO 1).
OJO: ES LA PENULTIMA MACRO, quedaría exáctamente igual, con todas las condiciones, solo que no necesito contar con gastos en base a las subcategorías, solo se eliminará ese conteo:
Para la cuenta de subcategorías (búsqueda en columna B)
=SI.ERROR(fcnCalcular(D13;"B";F13);"")
Solo eso amigo, gracias por tu ayuda.
Amigo, trabajemos con la penúltima macro, solo le faltan dos cosas, la suma de las subcategorías no funciona, le mandaré las siguientes fotos:
Esa es la estructura de las 12 hojas...
La hoja número 13:
Cuando yo seleccione una de esas subcategorías de "Ahorros", en la celda J32 me dará el resultado de la suma de los gastos que hice con categorías "Ahorros" y con la subcategoría que yo seleccione en dicha lista desplegable (LA CELDA DE DICHA LISTA ES LA B32).
Aclarar que las líneas de este tipo
=SI.ERROR(fcnCalcular(D13;"A";F13);"")
Representan las distintas posibilidades de la macro, lo que no quiere decir que las tengas que usar todas, sino, sólo las que necesites.
Si lo que quieres es el total de lasubcategoria que está en B32 y suponiendo que el mes está en F13, en la celda J32 debiera ir esto
=SI.ERROR(fcnCalcular(D32;"B";F13);"SUM")
¿Lo tienes así?
S@lu2
Denante te di estas dos opciones
- Dada una subcategoría y el mes, obtener la suma, no es mucho el cambio.
- Dada una categoría, una subcategoría y el mes, obtener la suma, entonces se nos va a ampliar bastante la macro.
Tú elegiste la opción 1 y es eso lo que hace la función fcnCalcular.
¿Es la opción 1 lo que necesitas o es la opción 2?
S@lu2
Si yo tengo mi tabla configurada de esa manera y en subcategoría pongo "En dólares", en TOTAL debería de darme la suma de $6.00, porque fue el único gasto de categoría ahorros, con subcategoría "En dólares" en enero... Eso es lo que necesito...
En TOTAL me debería de dar $6.00, es un ejemplo... Si te fijas solo hay una única celda que tiene el contador, a eso me refiero, desde que yo ponga la categoría y el mes, el contador me da el resultado, por eso te decía que el contador de las subcategorías no es necesario. Hay dos sumas y un contador... Espero que te haya servido la información, me dejas saber.
Chrisr, acá tenemos un calcular 2 para realizar lo que quieres.
Function fcnCalcular2(rngCeldaCat As Range, _
rngCeldaSubCat As Range, _
rngCeldaMes As Range, _
Optional strOp As String = "CUENTA") As Double
'Por GP'
Dim strMeses() As Variant
Dim strCeldaCat As String, strCeldaMes As String
Dim i As Integer, nFilas As Integer, strComp1 As String, strComp2 As String
Dim strColCat As String, strColSubCat As String, strColVal As String
strColCat = "A" 'Columna en la que se buscará la categoría'
strColSubCat = "B" 'Columna en la que se buscará la subcategoría'
strColVal = "E" 'Clumna en la que están los valores que se van a sumar'
strMeses = Array("Todos", "ene", "feb", "mar", _
"abr", "may", "jun", "jul", _
"ago", "sep", "oct", "nov", "dic")
strComp1 = IIf(LCase(rngCeldaCat) = "en general", "<>", "")
strComp2 = IIf(LCase(rngCeldaSubCat) = "en general", "<>", "")
If LCase(rngCeldaMes) = "todos" Then 'Todos los meses'
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range(strColVal & "200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular2 = fcnCalcular2 + WorksheetFunction.SumIfs( _
Sheets(strMeses(i)).Range(strColVal & "6:" & strColVal & nFilas), _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
strComp1 & LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range(strColSubCat & "6:" & strColSubCat & nFilas), _
strComp2 & LCase(rngCeldaSubCat))
Else
fcnCalcular2 = fcnCalcular2 + WorksheetFunction.CountIfs( _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
strComp1 & LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range(strColSubCat & "6:" & strColSubCat & nFilas), _
strComp2 & LCase(rngCeldaSubCat))
End If
Next
Else 'Un mes específico'
With Sheets(LCase(rngCeldaMes))
nFilas = .Range(strColVal & "200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular2 = fcnCalcular2 + WorksheetFunction.SumIfs( _
.Range(strColVal & "6:" & strColVal & nFilas), _
.Range(strColCat & "6:" & strColCat & nFilas), _
strComp1 & LCase(rngCeldaCat), _
.Range(strColSubCat & "6:" & strColSubCat & nFilas), _
strComp2 & LCase(rngCeldaSubCat))
Else
fcnCalcular2 = fcnCalcular2 + WorksheetFunction.CountIfs( _
.Range(strColCat & "6:" & strColCat & nFilas), _
strComp1 & LCase(rngCeldaCat), _
.Range(strColSubCat & "6:" & strColSubCat & nFilas), _
strComp2 & LCase(rngCeldaSubCat))
End If
End With
End If
End FunctionEn F32 iría esto
=SI.ERROR(fcnCalcular2(D13;B32;F13;"");"")
Y en la J32
=SI.ERROR(fcnCalcular2(D13;B32;F13;"SUM");"")
S@lu2
Saludos, amigo, la macro no me está funcionando... Te mandaré la última macro que hiciste y que me funcionó, luego te diré lo que falta por agregarle, ya que es simplemente una suma lo que falta... Me refiero a la 4ta macro que me mandaste en orden ascendente... Esa es la que tengo y la que me está funcionando, partiendo de esa macro, todo se quedará igual, te explicaré todo lo que tiene y lo que quiero que tenga:
-En la celda B32, cuando se selecciona una categoría y un mes, se activa una lista desplegable de las subcategorías de la categoría que seleccioné, si la categoría no está seleccionada, no se activa la lista desplegable de la celda B32. (ES ALGO QUE YA LO TENGO, HASTA AHÍ ME FUNCIONA TODO BIEN).
-Una vez tenga la categoría, el mes, y la subcategoría seleccionada, en la celda J32 me hará una suma tomando en cuenta SOLO LA SUBCATEGORÍA QUE SELECCIONE Y EL MES.
-Si yo selecciono la SUBCATEGORÍA (B32), y en la celda F13 selecciono el mes de enero o de cualquier mes, me buscará todos los gastos que hice en dicho mes con la subcategoría que seleccioné en (B32), me sumará el monto de esos gastos y mostrará el resultado en la celda J32. Si en la celda F13 yo selecciono "Todos", me buscará y hará una suma anual de todos los gastos que tengan la subcategoría que seleccioné y lo mostrará en la celda J32 también. Si yo selecciono en la celda D13 (Categoría) "En general" y en la celda F13 (Mes) pongo "Todos", tanto en la celda B32 como en la celda J32, se quedarán vacías, porque yo no cree una subcategoría para la categoría "En general" y porque al seleccionar "Todos" con la anterior categoría mencionada, ya hay una suma, que es la primera y es totalmente independiente a la que te digo que me hace falta, que me dará un resultado en la celda H13, por lo tanto no es necesario que sume lo mismo también en la celda J32 (SE QUEDARA VACÍA).
-Si la casilla B32 está vacía, en la J32 también estará vacía.
OJO: RECUERDE QUE ESA MACRO ME ESTA FUNCIONANDO A LA PERFECCIÓN, TANTO EL CONTADOR QUE TIENE Y LA SUMA ME ESTA FUNCIONANDO A LA PERFECCIÓN, SOLO LE LE AGREGARÍA UNA SEGUNDA SUMA QUE ES LA QUE TE DIJE AHÍ ARRIBA, SOLO ME HACE FALTA ESO. AQUÍ LE ADJUNTE UNA FOTO, TODAS LAS CELDAS QUE TIENE INFORMACIÓN (D13, F13, H13 Y F32) ME ESTÁN FUNCIONANDO, NO SE LE VA A CAMBIAR NADA, LA ÚNICA CELDA EN DONDE ME HACE FALTA LA SEGUNDA SUMA ES LA J32.
Perfecto!
Vamos a reemplazar la macro anterior por la siguiente
Function fcnCalcular(rngCeldaCat As Range, rngCeldaMes As Range, _ Optional strOp As String = "CUENTA") As Double 'Por GP' Dim strMeses() As Variant Dim strCeldaCat As String, strCeldaMes As String, strCeldaResult As String Dim i As Integer, nFilas As Integer strMeses = Array("Todos", "ene", "feb", "mar", _ "abr", "may", "jun", "jul", _ "ago", "sep", "oct", "nov", "dic") If LCase(rngCeldaMes) = "todos" Then 'Todos los meses' If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías' For i = 1 To 12 nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row If strOp = "SUM" Then fcnCalcular = fcnCalcular + WorksheetFunction.Sum(Sheets(strMeses(i)).Range("E6:E" & nFilas)) Else fcnCalcular = fcnCalcular + WorksheetFunction.Count(Sheets(strMeses(i)).Range("E6:E" & nFilas)) End If Next Else For i = 1 To 12 'Una categoría específica' nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row If strOp = "SUM" Then fcnCalcular = fcnCalcular + WorksheetFunction.SumIf( _ Sheets(strMeses(i)).Range("A6:A" & nFilas), _ LCase(rngCeldaCat), _ Sheets(strMeses(i)).Range("E6:E" & nFilas)) Else fcnCalcular = fcnCalcular + WorksheetFunction.CountIf( _ Sheets(strMeses(i)).Range("A6:A" & nFilas), _ LCase(rngCeldaCat)) End If Next End If Else 'Un mes específico' With Sheets(LCase(rngCeldaMes)) nFilas = .Range("E200").End(xlUp).Row If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías' If strOp = "SUM" Then fcnCalcular = WorksheetFunction.Sum(.Range("E6:E" & nFilas)) Else fcnCalcular = WorksheetFunction.Count(.Range("E6:E" & nFilas)) End If Else 'Una categoría específica' If strOp = "SUM" Then fcnCalcular = WorksheetFunction.SumIf( _ .Range("A6:A" & nFilas), _ LCase(rngCeldaCat), _ .Range("E6:E" & nFilas)) Else fcnCalcular = WorksheetFunction.CountIf( _ .Range("A6:A" & nFilas), _ LCase(rngCeldaCat)) End If End If End With End If End FunctionLuego, en la celda en la que necesitas que esté la cuenta de movimientos, agregas esto
=SI.ERROR(fcnCalcular(D13;F13);"")
Y en la cenda donde necesitas que esté la suma de los movimientos te olvidas de la fórmula y agregas esto
=SI.ERROR(fcnCalcular(D13;F13;"SUM");"")
Si el tercer parámetro de la función fcnCalcular es SUM, entonces sumará y si es algo distinto, entonces contará.
Puébalo y me comentas.
S@lud2
Esa es la macro que me funciona amigo, solo hay que agregarle la suma que te explique arriba, cualquier duda me deja saber.
Esta es la macro que te envié ayer y hace todo lo que pides, simplemente debes entender cómo funciona. Entiendo que no quieras modicar algo que ya te está funcionando, pero lee esta respuesta hasta el final.
Function fcnCalcular(rngCeldaCat As Range, strColCat As String, rngCeldaMes As Range, _
Optional strOp As String = "CUENTA") As Double
'Por GP'
Dim strMeses() As Variant
Dim strCeldaCat As String, strCeldaMes As String, strCeldaResult As String
Dim i As Integer, nFilas As Integer
strMeses = Array("Todos", "ene", "feb", "mar", _
"abr", "may", "jun", "jul", _
"ago", "sep", "oct", "nov", "dic")
If LCase(rngCeldaMes) = "todos" Then 'Todos los meses'
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
For i = 1 To 12
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.Sum(Sheets(strMeses(i)).Range("E6:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.Count(Sheets(strMeses(i)).Range("E6:E" & nFilas))
End If
Next
Else
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range("E200").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.SumIf( _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range("E6:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.CountIf( _
Sheets(strMeses(i)).Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat))
End If
Next
End If
Else 'Un mes específico'
With Sheets(LCase(rngCeldaMes))
nFilas = .Range("E200").End(xlUp).Row
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.Sum(.Range("E6:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.Count(.Range("E6:E" & nFilas))
End If
Else 'Una categoría específica'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.SumIf( _
.Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat), _
.Range("E6:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.CountIf( _
.Range(strColCat & "6:" & strColCat & nFilas), _
LCase(rngCeldaCat))
End If
End If
End With
End If
End FunctionEn la explicación te dejé lo siguiente:
Para la cuenta de Categorías (búsqueda en columna A)
=SI.ERROR(fcnCalcular(D13;"A";F13);"") [1° funcionalidad]
Para la suma de Categorías (búsqueda en columna A)
=SI.ERROR(fcnCalcular(D13;"A";F13;"SUM");"") [2° funcionalidad]
Para la cuenta de subcategorías (búsqueda en columna B)
=SI.ERROR(fcnCalcular(D13;"B";F13);"") [3° funcionalidad]
Para la suma de Categorías (búsqueda en columna B)
=SI.ERROR(fcnCalcular(D13;"B";F13;"SUM");"") [4° funcionalidad]
EL segundo parámetro de fcnCalcular, ahora determina la columna en la que se está buscando el criterio.
Entonces, vamos viendo:
- Reemplaza la macro existente por la que te envío en esta respuesta (por últmo guardas una copia del libro si no quieres perder lo que ya tienes)
- En tu celda H13 necesitas la suma según categoría y mes seleccionado, entonces en H13 utilizaremos la 2° funcionalidad (Sumar categorías)
=SI.ERROR(fcnCalcular(D13;"A";F13;"SUM");"")
Donde: D13 contiene la categoría, "A" es la columna de cada mes en que buscaremos esa categoría, F13 contiene el mes por el que vamos a preguntar y "SUM" le infica que sume. - En J32 tienes la suma de las subcategorías según el mes seleccionado (esta suma es independiente de la categoría seleccionada), que es la 4° funcionalidad, por lo tanto, en la J32 debiera ir lo siguiente
=SI.ERROR(fcnCalcular(B32;"B";F13;"SUM");"")
Donde: B32 contiene la subcategoría buscada, "B" es la columna de cada mes en que se buscará la subcategoría, F13 contiene el més por el que vamos a preguntar y "SUM" le indica que lo que quieres es sumar. Si B32 está vacía, la función entregará un error, por lo que la función SI. ERROR hará que J32 quede vacía. - F32 no la mencionas, pero según veo, debiera tener la cuenta de las subcategorías según el mes seleccionado (esta cuenta es independiente de la categoría seleccionada), entonces es la 3° funcionalidad, por lo tanto, en la celda F32 debiera ir lo siguiente
=SI.ERROR(fcnCalcular(B32;"B";F13);"")
Donde: B32 contiene la subcategoría buscada, "B" es la columna de cada mes en que se buscará la subcategoría y F13 contiene el mes por el que vamos a preguntar (El comportamiento será el mismo que en el caso anterior, o sea, quedará vacía si B32 está vacía). - Ahora, si F32 no contiene la cuenta de subcategorias, sino, de categorias según el mes selecionado (1° funcionalidad), debes reemplazar B32 por D13 y "B" por "A", entonces en F32 quedaría lo siguiente
=SI.ERROR(fcnCalcular(D13;"A";F13);"") - Observar que los puntos 3 y 4 son excluyentes, se usa uno o el otro.
Luego de hacer todos estos cambios, me comentas:
- ¿Funciona H13?
- ¿Funciona J32?
- ¿Funciona F32?
S@lu2
El punto 6 debiera decir
Observar que los puntos 4 y 5 son excluyentes, se usa uno o el otro.
S@lu2
¡Gracias! Saludos, veo que me está funcionando, gracias por explicarme detalladamente.
Me hiciste sudar!
¡Qué bueno que te esté funcionando!
S@lu2
Una pregunta... En caso de yo querer usar los dos contadores, cuando yo pongo el contador de subcategorías en otra celda, me da un error, supongo que es porque debería de estar en la celda F32, pero ya ahí está el contador de las categorías. ¿Qué puedo hacer en ese caso?
OJO: Es esta fórmula (=SI.ERROR(fcnCalcular(B32;"B";F13);"")), la quiero poner en F33, pero me da error, ese es el contador de las subcategorías, el contador de las categorías (F32) me funciona a la perfección. Probé poniendo esa fórmula en la celda F32 y de todas formas me da error.
Como lo haría una función cualquiera, fcnCalcular debe entregar los mismos resultados independiente de la celda en la que la ocupes, siempre y cuando los parámetros estén bien.
Si funciona la suma de subcategorías y la cuenta de categorías, la cuenta de subcategorías debiera funcionar sin problemas.
Asegúrate de que la función en F33 efectivamente es
=SI.ERROR(fcnCalcular(B32;"B";F13);"")
y no
=SI.ERROR(fcnCalcular(B33;"B";F14);"") o algo parecido.
Si no funciona, sube una imagen de la planilla donde se vean todas las celdas involucradas y mostrando la fórmula de F33.
S@lu2
Donde sea que pongo la fórmula, me lanza ese error.
¿Puedes subir una imagen en que se muestre el contenido de la F32?
¿Puedes subir una imagen en que se vea la fórmula de la F32?
Ahí está...
Perfecto!
Si te das cuenta, el error que te aparece en F33 es porque tu separador es una coma (,) y la función que estás intentando insertar tiene como separador el punto y coma (;) que es el separador que está predeterminado en mi excel.
En F33 reemplaza los punto y coma por comas y problema resuelto.
S@lu2
¡Gracias! Me funciona bien, disculpa las molestias, me ayudaste bastante, infinitas gracias amigo.
Qué bueno que llegamos a puerto.
Tanto si una respuesta te ha servido o no, es una buena práctica valorarla, de esa forma cierras la pregunta, evalúas la ayuda y le permites saber a otros usuarios si la respuesta a tu pregunta fue satisfactoria.
S@lu2