Funciones VBA SUMIF y SUMIFS

Este tutorial le mostrará cómo utilizar las funciones SUMIF y SUMIFS de Excel en VBA.

VBA no tiene un equivalente de las Funciones SUMIF o SUMIFS que pueda utilizar – un usuario tiene que utilizar las funciones incorporadas de Excel en VBA utilizando el objeto WorksheetFunction.

Función WorksheetFunction.SUMIF

El objeto WorksheetFunction puede utilizarse para llamar a la mayoría de las funciones de Excel que están disponibles en el cuadro de diálogo Insertar función en Excel. La función SUMIF es una de ellas.

Sub TestSumIf()
Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
End Sub

El procedimiento anterior sólo sumará las celdas del Rango(D2:D9) si la celda correspondiente de la columna C = 150.

 

Asignación de un resultado SUMIF a una variable

Puede que quieras utilizar el resultado de tu fórmula en otra parte del código en lugar de escribirlo directamente en un rango de Excel. Si este es el caso, puedes asignar el resultado a una variable para usarla más adelante en tu código.

Sub asignarSumifaUnaVariable()
   Dim resultado As Double
'Asignar la variable
  resultado = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
'Muestra el resultado
  MsgBox "La suma total de los elementos con código 150 es: " & resultado
End Sub

 

Uso de SUMIFS

La función SUMIFS es similar a la función SUMIF de la hoja de cálculo, pero permite comprobar más de un criterio. En el ejemplo siguiente, buscamos sumar el precio de venta si el código de venta es 150 Y el Precio de coste es mayor que 2. Observe que en esta fórmula, el rango de celdas a sumar está delante de los criterios, mientras que en la función SUMIF, está detrás.

Sub SumIfsVariosCriterios()
   Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), _
   Range("C2:C9"), 150, Range("E2:E9"), ">2")
End Sub

Uso de SUMIF con un objeto de rango

Puede asignar un grupo de celdas al objeto Range, y luego usar ese objeto Range con el objeto WorksheetFunction.

Sub pruebaSumifRango()
   Dim rngCriterio As Range
   Dim rngSuma As Range
'Asignación del rango
   Set rngCriterio = Range("C2:C9")
   Set rngSuma = Range("D2:D9")
'Uso del rango en la fórmula
   Range("D10") = WorksheetFunction.SumIf(rngCriterio, 150, rngSuma)
'Liberación de los objetos
  Set rngCriterio = Nothing
  Set rngSuma = Nothing
End Sub

Uso de SUMIFS en múltiples objetos de rango

De forma similar, puede utilizar SUMIFS en múltiples objetos de rango.

Sub pruebaSumifsVariosRangos()
   Dim rngCriterio1 As Range
   Dim rngCriterio2 As Range
   Dim rngSuma As Range
'Asignación de los rangos
   Set rngCriterio1 = Range("C2:C9")
   Set rngCriterio2 = Range("E2:E9")
   Set rngSuma = Range("D2:D9")
'Uso de los rangos en la fórmula.
Range("D10") = WorksheetFunction.SumIfs(rngSuma, rngCriterio1, 150, rngCriterio2, ">2")
'Liberación de los objetos.
  Set rngCriterio1 = Nothing
  Set rngCriterio2 = Nothing
  Set rngSuma = Nothing
End Sub

Observe que como está usando un signo mayor que, el criterio mayor que 2 necesita estar dentro de paréntesis.

Fórmula SUMIF

Cuando se utiliza la función WorksheetFunction.SUMIF para añadir una suma a un rango en la hoja de cálculo, se devuelve una suma estática, no una fórmula flexible. Esto significa que cuando sus cifras en Excel cambian, el valor que ha sido devuelto por la WorksheetFunction no cambiará.

 

En el ejemplo anterior, el procedimiento ha sumado Range(D2:D9) donde el código es igual a 150 en la columna C, y el resultado se ha puesto en D10. Como puede ver en la barra de fórmulas, este resultado es una cifra y no una fórmula. Si alguno de los valores cambia en Range(D2:D9) o Range(C2:D9), el resultado en D10 NO cambiará. En lugar de utilizar la función WorksheetFunction.SumIf, puedes utilizar VBA para aplicar una función SUMIF a una celda utilizando los botones Fórmula o FórmulaR1C1 .

Método de la Fórmula

El método de la fórmula le permite apuntar específicamente a un rango de celdas, por ejemplo: D2:D10 como se muestra a continuación.

Sub pruebaFormulaSumif()
  Range("D10").Formula = "=SUMIF(C2:C9,150,D2:D9)"
End Sub

 

Método FormulaR1C1

El método FormulaR1C1 es más flexible en el sentido de que no se limita a un rango de celdas determinado. El siguiente ejemplo nos dará la misma respuesta que el anterior.

Sub pruebaSumifRefRel()
   Range("D10").FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

Sin embargo, para flexibilizar la fórmula, podríamos modificar el código de la siguiente manera

Sub pruebaSumifFlexible()
   ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

En cualquier lugar de la hoja de cálculo, la fórmula sumará las celdas que cumplan con los criterios justo encima de ella y colocará la respuesta en su CeldaActiva. El Rango dentro de la función SUMIF tiene que ser referido usando la sintaxis de Fila (R) y Columna (C).

Ambos métodos le permiten utilizar fórmulas dinámicas de Excel dentro de VBA.

Ahora habrá una fórmula en D10 en lugar de un valor.