No hay problema, esta es la macro, te enviaré todo lo que necesites:
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("E501").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.Sum(Sheets(strMeses(i)).Range("E5:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.Count(Sheets(strMeses(i)).Range("E5:E" & nFilas))
End If
Next
Else
For i = 1 To 12 'Una categoría específica'
nFilas = Sheets(strMeses(i)).Range("E501").End(xlUp).Row
If strOp = "SUM" Then
fcnCalcular = fcnCalcular + WorksheetFunction.SumIf( _
Sheets(strMeses(i)).Range(strColCat & "5:" & strColCat & nFilas), _
LCase(rngCeldaCat), _
Sheets(strMeses(i)).Range("E5:E" & nFilas))
Else
fcnCalcular = fcnCalcular + WorksheetFunction.CountIf( _
Sheets(strMeses(i)).Range(strColCat & "5:" & strColCat & nFilas), _
LCase(rngCeldaCat))
End If
Next
End If
Else 'Un mes específico'
With Sheets(LCase(rngCeldaMes))
nFilas = .Range("E501").End(xlUp).Row
If LCase(rngCeldaCat) = "en general" Then 'Todas las categorías'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.Sum(.Range("E5:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.Count(.Range("E5:E" & nFilas))
End If
Else 'Una categoría específica'
If strOp = "SUM" Then
fcnCalcular = WorksheetFunction.SumIf( _
.Range(strColCat & "5:" & strColCat & nFilas), _
LCase(rngCeldaCat), _
.Range("E5:E" & nFilas))
Else
fcnCalcular = WorksheetFunction.CountIf( _
.Range(strColCat & "5:" & strColCat & nFilas), _
LCase(rngCeldaCat))
End If
End If
End With
End If
End Function
James Bond