VBA-Funktionen COUNTIF und COUNTIFS

Dieses Tutorial zeigt Ihnen, wie Sie die Excel-Funktionen COUNTIF und COUNTIFS (Deutsch: ZÄHLENWENN & ZÄHLENWENNS) in VBA verwenden können.

VBA verfügt nicht über ein Äquivalent zu den Excel-Funktionen COUNTIF und COUNTIFS. Stattdessen müssen Sie die Excel-Funktionen über das WorkSheetFunction-Objekt aufrufen.

Die COUNTIF-Arbeitsblattfunktion

Das WorksheetFunction-Objekt kann verwendet werden, um die meisten Excel-Funktionen aufzurufen, die im Dialogfenster „Funktion einfügen“ in Excel verfügbar sind. Die Funktion COUNTIF ist eine von ihnen.

Sub CountIf_Test()
   Range("D10") = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
End Sub

Die obige Prozedur zählt nur die Zellen in Range(D2:D9), die einen Wert von 5 oder höher enthalten. Da Sie ein Größer-als-Zeichen verwenden, muss das Kriterium „>5“ in Klammern gesetzt werden.

Zuweisung eines COUNTIF-Ergebnisses einer Variablen

Möglicherweise möchten Sie 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 CountIf_Variable_Zuweisen()
   Dim ergebnis As Double
'Die Variable zuweisen
   ergebnis = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
'Das Ergebnis anzeigen
  MsgBox "Die Anzahl der Zellen mit einem Wert größer als 5 beträgt " & ergebnis
End Sub

COUNTIFS verwenden

Die Funktion COUNTIFS ähnelt der Arbeitsblattfunktion COUNTIF. Sie ermöglicht es Ihnen jedoch, mehr als ein Kriterium zu überprüfen. Im folgenden Beispiel wird die Formel die Anzahl der Zellen in D2 bis D9 zählen, in denen der Verkaufspreis größer als 6 UND der Einkaufspreis größer als 5 ist.

Sub Verwendung_CountIfs()
   Range("D10") = WorksheetFunction.CountIfs(Range("C2:C9"), ">6", Range("E2:E9"), ">5")
End Sub

Verwendung von COUNTIF mit einem Range-Objekt

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

Sub CountIf_Bereich_Test()
   Dim Bereich_Zaehlen as Range
'Den Zellenbereich zuweisen
   Set Bereich_Zaehlen = Range("D2:D9")
'Den Bereich in der Formel verwenden
   Range("D10") = WorksheetFunction.SUMIF(Bereich_Zaehlen, ">5")
'Die Bereichsobjekte freigeben
  Set Bereich_Zaehlen = Nothing
End Sub

Verwendung von COUNTIFS für mehrere Bereichsobjekte

Auf ähnliche Weise können Sie COUNTIFS auf mehrere Range-Objekte anwenden.

Sub CountIfs_Mehrere_Bereiche_Test() 
   Dim bereich_Kriterien1 As Range 
   Dim bereich_Kriterien2 As Range

'den Zellenbereich zuweisen 
   Set bereich_Kriterien1= Range("D2:D9")
   Set bereich_Kriterien2 = Range("E2:E10")
   
'Die Bereiche in der Formel verwenden 
  Range("D10") = WorksheetFunction.CountIfs(bereich_Kriterien1, ">6", bereich_Kriterien2, ">5")

'die Bereichsobjekte freigeben
  Set bereich_Kriterien1 = Nothing
  Set bereich_Kriterien2 = Nothing
End Sub

COUNTIF-Formel

Wenn Sie die WorksheetFunction.COUNTIF verwenden, um eine Summe zu einem Bereich in Ihrem Arbeitsblatt hinzuzufügen, wird ein statischer Wert zurückgegeben (keine flexible Formel). Das bedeutet, dass sich der von der WorksheetFunction zurückgegebene Wert nicht ändert, wenn sich Ihre Zahlen in Excel ändern.

Im obigen Beispiel hat die Prozedur die Anzahl der Zellen mit Werten im Range(D2:D9) gezählt, in denen der Verkaufspreis größer als 6 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) ändert, wird sich das Ergebnis in D10 NICHT ändern.

Anstatt die WorksheetFunction.SumIf zu verwenden, können Sie mit VBA eine COUNTIF-Funktion auf eine Zelle anwenden, indem Sie die Formel- oder FormelR1C1-Methoden anwenden.

Formelmethode

Mit der Formelmethode können Sie speziell auf einen Bereich von Zellen verweisen, z. B. D2:D9, wie unten gezeigt.

Sub CountIf_Test()
  Range("D10").Formula ="=COUNTIF(D2:D9, "">5"")"
End Sub

FormulaR1C1-Methode

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

Sub CountIf_Test()
   Range("D10").FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">5"")"
End Sub

Um die Formel noch flexibler zu gestalten, könnten wir den Code jedoch folgendermaßen ändern:

Sub CountIf_Test() 
   ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">5"")"
End Sub

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

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

In D10 befindet sich nun eine Formel anstelle eines Wertes.