VBA-Funktionen SUMIF und SUMIFS

Dieses Tutorial zeigt Ihnen, wie Sie die Excel-Funktionen SUMIF (Deutsch: SUMMEWENN) und SUMIFS (Deutsch: SUMMEWENNS) in VBA verwenden können.

VBA verfügt nicht über ein Äquivalent der Funktionen SUMIF oder SUMIFS, die Sie verwenden können. Der Benutzer muss die eingebauten Excel-Funktionen in VBA verwenden, indem er das Arbeitsblattfunktionsobjekt nutzt.

Die Arbeitsblattfunktion SUMIF

Das Arbeitsblattfunktionsobjekt kann verwendet werden, um die meisten Excel-Funktionen aufzurufen, die im Dialogfeld „Funktion Einfügen“ in Excel verfügbar sind. Die SUMIF-Funktion ist eine von ihnen.

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

Die obige Prozedur addiert die Zellen in Range(D2:D9) nur auf, wenn die entsprechende Zelle in Spalte C = 150 ist.

Zuweisung eines SUMIF-Ergebnisses einer Variablen

Sie möchten möglicherweise das Ergebnis Ihrer Formel an anderer Stelle im Code verwenden, anstatt es direkt in einen Excel-Bereich zurückzuschreiben. In diesem Fall können Sie das Ergebnis einer Variablen zuweisen, die Sie später in Ihrem Code verwenden können.

Sub SumIfVariableZuweisen()
   Dim ergebnis as Double
'Der Variablen zuweisen
   ergebnis = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
'Das Ergebnis anzeigen
  MsgBox "Die Gesamtsumme des Ergebnisses, das dem Verkaufscode 150 entspricht, beträgt " & ergebnis
End Sub

SUMIFS verwenden

Die Funktion SUMIFS ähnelt der Arbeitsblattfunktion SUMIF, ermöglicht es Ihnen jedoch, mehr als ein Kriterium zu prüfen. Im folgenden Beispiel soll der Verkaufspreis aufsummiert werden, wenn der Verkaufscode  gleich 150 ist UND der Einkaufspreis größer als 2 ist. Beachten Sie, dass in dieser Formel der Bereich der Zellen, die addiert werden sollen, vor dem Kriterium steht, während er bei der Funktion SUMIF dahinter steht.

Sub MehrfachSumIfs()
'Summe der Kosten, wenn der Verkaufscode 150 ist UND der Verkaufspreis kleiner als 2 ist
   Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")
End Sub

Verwendung von SUMIF mit einem Range-Objekt

Sie können eine Gruppe von Zellen dem Range-Objekt zuweisen und dann dieses Range-Objekt mit dem Arbeitsblattfunktionsobjekt verwenden.

Sub SumIfBereich_Test()
   Dim bereichKriterien As Range
   Dim summenBereich as Range
'den Zellenbereich zuweisen
   Set bereichKriterien = Range("C2:C9")
   Set summenBereich = Range("D2:D9")
'Den Bereich in der Formel verwenden
   Range("D10") = WorksheetFunction.SumIf(bereichKriterien, 150, summenBereich)
'Die Bereichsobjekte freigeben
  Set bereichKriterien = Nothing
  Set summenBereich = Nothing
End Sub

Verwendung von SUMIFS für mehrere Range-Objekte

In ähnlicher Weise können Sie SUMIFS auf mehrere Range-Objekte anwenden.

Sub MehrereBereicheSummieren_Test() 
   Dim bereichKriterien1 As Range 
   Dim bereichKriterien2 As Range
   Dim summenBereich As Range
'Den Zellenbereich zuweisen 
   Set bereichKriterien1 = Range("C2:C9")
   Set bereichKriterien2 = Range("E2:E9")   
   Set summenBereich = Range("D2:D9")
'Die Bereiche in der Formel verwenden 
Range("D10") = WorksheetFunction.SumIfs(summenBereich, bereichKriterien1, 150, bereichKriterien2, ">2")
 'Das Bereichsobjekt freigeben
  Set bereichKriterien1 = Nothing
  Set bereichKriterien2 = Nothing
  Set summenBereich = Nothing
End Sub

Beachten Sie, dass die Kriterien, die größer als 2 sind, in Klammern stehen müssen, weil Sie ein Größer-als-Zeichen verwenden.

SUMIF-Formel

Wenn Sie die WorksheetFunction.SUMIF verwenden, um eine Summe zu einem Bereich in Ihrem Arbeitsblatt hinzuzufügen, wird eine statische Summe zurückgegeben, d.h. es handelt sich hier um keine flexible Formel. Das bedeutet, dass sich der von der Arbeitsblattfunktion zurückgegebene Wert nicht ändert, wenn sich Ihre Zahlen in Excel ändern.

Im obigen Beispiel hat die Prozedur alle Werte des Bereichs (D2:D9) aufsummiert, bei den der SaleCode in Spalte C gleich 150 ist, und das Ergebnis wurde in D10 eingetragen. Wie Sie in der Formelleiste sehen können, handelt es sich bei diesem Ergebnis um eine Zahl und nicht um eine Formel.

Wenn sich einer der Werte in Bereich (D2:D9) oder Bereich (C2:D9) ändert, wird das Ergebnis in D10 NICHT geändert.

Anstatt die WorksheetFunction.SumIf zu verwenden, können Sie mit VBA eine SUMIF-Funktion auf eine Zelle anwenden, indem Sie die Formula- oder FormulaR1C1-Methoden anwenden.

Formel-Methode

Mit der Formel-Methode können Sie speziell auf einen Bereich von Zellen verweisen, z. B. D2:D10, wie unten gezeigt.

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

FormulaR1C1-Methode

Die Methode FormulaR1C1 ist insofern flexibler, da sie Sie nicht auf einen bestimmten Bereich von Zellen beschränkt. Das folgende Beispiel liefert die gleiche Antwort wie das obige.

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

Um die Formel flexibler zu gestalten, könnten wir den Code jedoch wie folgt ändern

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

Unabhängig davon, wo Sie sich in Ihrem Arbeitsblatt befinden, addiert die Formel dann die Zellen auf, die die Kriterien direkt über ihr erfüllen, und platziert die Antwort in Ihrer Aktiven Zelle. Der Bereich innerhalb der Funktion SUMIF muss mit der Syntax Zeile (R) und Spalte (C) referenziert werden.

Mit diesen beiden Methoden können Sie dynamische Excel-Formeln in VBA verwenden.

In D10 befindet sich nun eine Formel anstelle eines Wertes.