Datenanalyse in ExcelExcel-Zellen zählen – Anzahl der Daten pro Intervall ermitteln

Eine häufige Anforderung in der Praxis ist, dass Sie Zellen in einer Liste zählen müssen, deren Wert zwischen zwei Zahlen (Intervall) liegt. Hierfür können Sie die Funktion ZÄHLENWENNS() in Excel einsetzen.

Beispiel: Anzahl Einträge zählen, die in einem Intervall liegen

In der folgenden Abbildung sehen Sie eine Liste mit Daten in den Spalten A und B. Darin sind Personen und die Punkte einer Prüfung pro Person dargestellt.

Im Bereich D1:F5 sollen nun die entsprechenden Punktintervalle aus der Liste gezählt werden. In der Zelle F2 soll die Anzahl der Prüflinge ermittelt werden, die eine Punktzahl von 61 bis 70 Punkten haben.

Für diese Aufgabe können Sie die Funktion ZÄHLENWENNS() einsetzen. Die allgemeine Syntax lautet:

=ZÄHLENWENNS(Kriterienbereich1; Kriterien1; [Kriterienbereich2; Kriterien2]; …)

Dabei gilt:

• Kriterienbereich1: Bereich der Tabelle, in dem Sie die Kriterien1 überprüfen wollen.
• Kriterien1: Kriterien, die angeben, was genau gezählt werden soll.
• Kriterienbereich2 (optional): Ein weiterer Bereich, der anhand weiterer, einschränkender Kriterien überprüft wird.
• Kriterien2 (optional): Weitere Kriterien zur Einschränkung, was gezählt werden soll.

Wichtig: Die Größe aller Kriterienbereiche muss gleich sein. Die Kriterien können Sie eingeben als Zahl (61), als Text in Anführungsstrichen ("Thomas") oder als Verweis (D2). Diese kombinieren Sie mit Vergleichsoperatoren (=, >, <, >=, <=) wie im folgenden Beispiel in Zelle F2:

=ZÄHLENWENNS($B$2:$B$13; ">="&D2; $B$2:$B$13; "<="&E2)

Erfassen Sie den Listenbereich B2:B13 in der Formel als absoluten Bezug (mit $-Zeichen), damit Sie die Formel ohne Probleme in die Zellen F3 bis F5 kopieren können.

Tipp: Wenn Sie in der Bearbeitungsleiste mit der linken Maustaste auf einen Bezug klicken und anschließend die F4-Taste drücken, dann können Sie mit jedem Drücken der F4-Taste die Bezugsarten zwischen absoluten und relativen Bezügen automatisch wechseln.

Beachten Sie: Festlegen der Intervallgrenzen

Die Intervallgrenzen in den Spalten D und E müssen eindeutig sein. Die Intervalle oder Zahlenbereiche dürfen sich nicht überschneiden. Ansonsten werden Einträge in der Liste möglicherweise doppelt gezählt.

Gleichzeitig müssen Sie darauf achten, dass mit der Intervalleingabe alle möglichen Listeneinträge erfasst werden. Käme beispielsweise die Punktzahl 70,5 vor, würde dieser Eintrag nicht berücksichtigt.

Sie können dies vermeiden, indem Sie die Formel ein wenig ändern und die Obergrenze des vorhergehenden Intervalls als Untergrenze des nächsten Intervalls nutzen. Die Formel in Zelle F3 lautet dann:

=ZÄHLENWENNS($B$2:$B$13; ">"&E2; $B$2:$B$13; "<="&E3)

Und in den Zellen F4 und F5 entsprechend.

Wie geht ZÄHLENWENNS() bei der Intervallauswertung vor?

Die Funktion ZÄHLENWENNS() akzeptiert Kriterien in Bereichs- oder Kriterienpaaren. Im Gegensatz zu ZÄHLENWENN() können Sie mehrere Kriterien definieren.

Mit dem ersten Bereichs-/Kriterienpaar suchen und zählen Sie die Werte in Spalte B (Punktzahl), die größer oder gleich (>=) dem „von“-Wert in Spalte D sind:

=ZÄHLENWENNS($B$2:$B$13; ">="&D2; $B$2:$B$13; "<="&E2)

Das zweite Bereichs-/Kriterienpaar sucht und zählt die Einträge, die kleiner oder gleich (<=) dem „bis“-Wert in Spalte E sind:

=ZÄHLENWENNS($B$2:$B$13; ">="&D2; $B$2:$B$13; "<="&E2)

Die Zellen, bei denen beide Kriterien zutreffen, werden gezählt und als Wert in die Zelle F2 zurückgegeben.

Alternative Funktion SUMMENPRODUKT()

Eine Alternative zu ZÄHLENWENNS() ist die Funktion SUMMENPRODUKT(), mit der Sie diese Aufgabe ebenfalls meistern können. Erfassen Sie hierzu die folgende Formel in der Zelle F2:

=SUMMENPRODUKT(($B$2:$B$13>=D2) * ($B$2:$B$13<=E2))

Erfassen Sie auch hier den Listenbereich, in welchem die Punkte aufgelistet sind, in absoluten Bezügen ($), damit Sie die Formel nach unten kopieren können.

Wie rechnet die Formel SUMMENPRODUKT()?

Im ersten Teil der Formel

=SUMMENPRODUKT(($B$2:$B$13>=D2) * ($B$2:$B$13<=E2))

wird jede Zelle im Bereich B2:B13 überprüft, ob der Wert >=D2 ist. Ist die Bedingung wahr, dann wird eine 1 sonst eine 0 als Ergebnis zurückgegeben.

Das Gleiche passiert mit dem zweiten Teil der Formel. Hier wird durch

=SUMMENPRODUKT(($B$2:$B$13>=D2) * ($B$2:$B$13<=E2))

jede einzelne Zelle im Bereich B2:B13 überprüft, ob diese <= dem Wert in der Zelle E2 ist. Ist die Bedingung wahr, dann wird eine 1 sonst eine 0 als Ergebnis zurückgegeben.

Anschließend werden die Ergebnisse der beiden Überprüfungen miteinander multipliziert und das Ergebnis summiert.

Nur bei den Paaren, bei denen in beiden Fällen eine 1 als Prüfergebnis zurückgegeben wird (beide Bedingungen sind erfüllt), ergibt sich nach der Multiplikation eine 1 (=1*1) als Wert, der anschließend in die Summierung eingeht.