ZWISCHENSUMME-WENN-Formel – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die bedingte Zwischensumme (ZWISCHENSUMME-WENN) von Zellen, die bestimmte Kriterien erfüllen, mit der Funktion TEILERGEBNIS berechnen können.

Die TEILERGEBNIS-Funktion

Die TEILERGEBNIS-Funktion wird verwendet, um verschiedene Berechnungen mit einem Datenbereich durchzuführen (Zählung, Summe, Mittelwert usw.). Wenn Sie mit der Funktion nicht vertraut sind, fragen Sie sich vielleicht, warum Sie nicht einfach die Funktionen ANZAHL, SUMME oder MITTELWERT selbst verwenden… Dafür gibt es zwei gute Gründe:

1. Sie können eine Tabelle erstellen, in der die Optionen für die Zwischensumme (1, 2, 3, 4 usw.) aufgelistet sind und eine einzige Formel nach unten kopieren, um eine Zusammenfassung der Daten zu erstellen. (Dies kann besonders zeitsparend sein, wenn Sie versuchen, die ZWISCHENSUMME-WENN zu berechnen, wie wir in diesem Artikel zeigen).
2. Die TEILERGEBNIS-Funktion kann verwendet werden, um nur sichtbare (gefilterte) Zeilen zu berechnen.

Wir werden uns auf die zweite Implementierung der TEILERGEBNIS-Funktion konzentrieren.

In diesem Beispiel verwenden wir die Funktion (ANZAHL2) zum Zählen der sichtbaren Zeilen, indem wir das TEILERGEBNIS-Argument Funktion auf 3 setzen (eine vollständige Liste der möglichen Funktionen finden Sie hier.)

=TEILERGEBNIS(3;$D$2:$D$14)

Beachten Sie, wie sich die Ergebnisse ändern, wenn wir die Zeilen manuell filtern.

ZWISCHENSUMME-WENN

Um eine bedingte Zwischensumme (ZWISCHENSUMME-WENN) zu erstellen, verwenden wir eine Kombination aus SUMMENPRODUKT, TEILERGEBNIS, BEREICH.VERSCHIEBEN, ZEILE und MIN in einer Array-Formel. Mit dieser Kombination können wir im Wesentlichen eine generische „ZWISCHENSUMME-WENN“-Funktion erstellen. Lassen Sie uns ein Beispiel durchgehen.

Wir haben eine Liste von Mitgliedern und deren Anwesenheitsstatus für jede Veranstaltung:

Angenommen, wir sollen die Anzahl der Mitglieder, die an einer Veranstaltung teilgenommen haben, dynamisch zählen, indem wir die Liste folgendermaßan manuell filtern:

Um dies zu erreichen, können wir diese Formel verwenden:

=SUMMENPRODUKT((<Wertebereich>=<Kriterien>)*(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(<erste Zelle im Bereich>;ZEILE(<Wertebereich>)-MIN(ZEILE(<Wertebereich>));0))))

=SUMMENPRODUKT((D2:D14="Teilgenommen")*(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(D2;ZEILE(D2:D14)-MIN(ZEILE(D2:D14));0))))

Wenn Sie Excel 2019 und ältere Versionen verwenden, müssen Sie die Array-Formel durch Drücken von STRG + UMSCHALTTASTE + ENTER eingeben, um Excel mitzuteilen, dass Sie eine Array-Formel eingeben. Sie wissen, dass die Formel korrekt als Array-Formel eingegeben wurde, wenn geschweifte Klammern um die Formel herum erscheinen (siehe Abbildung oben).

Wie funktioniert die Formel?

Die Formel funktioniert durch Multiplikation von zwei Arrays innerhalb von SUMMENPRODUKT, wobei das erste Array unsere Kriterien behandelt und das zweite Array nur die sichtbaren Zeilen filtert:

=SUMMENPRODUKT(<Kriterien-Array>*<Sichtbarkeits-Array>)

Das Kriterien-Array

Das Kriterien-Array wertet jede Zeile in unserem Wertebereich aus (in diesem Beispiel der Status „teilgenommen“) und erzeugt ein Array wie dieses:

=(<Wertebereich>=<Kriterien>)

=(D2:D14="Teilgenommen")

Ausgabe:

{WAHR; FALSCH; FALSCH; WAHR; FALSCH; WAHR; WAHR; WAHR; FALSCH; FALSCH; WAHR; FALSCH; WAHR; WAHR}

Beachten Sie, dass die Ausgabe im ersten Array in unserer Formel ignoriert, ob die Zeile sichtbar ist oder nicht. Hier kommt unser zweites Array zum Einsatz.

Das Sichtbarkeits-Array

Indem wir TEILERGEBNIS verwenden, um nicht sichtbare Zeilen in unserem Bereich auszuschließen, können wir unser Sichtbarkeits-Array erzeugen. TEILERGEBNIS allein gibt jedoch einen einzigen Wert zurück, während SUMMENPRODUKT ein Array von Werten erwartet. Um dies zu umgehen, verwenden wir BEREICH.VERSCHIEBEN, um eine Zeile nach der anderen zu übergeben. Bei dieser Technik muss BEREICH.VERSCHIEBEN ein Array, das jeweils eine Zahl enthält, übergeben werden. Das zweite Array sieht folgendermaßen aus:

=TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(<erste Zelle im Bereich>,ROW(<Wertebereich>)-MIN(ROW(<Wertebereich>));0))

=TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(D2;ZEILE(D2:D14)-MIN(ZEILE(D2:D14));0))

Ausgabe:

{1;1;0;0;1;1}

Zusammenfügen der beiden:

=SUMMENPRODUKT({WAHR; WAHR; FALSCH; FALSCH; WAHR; WAHR} * {1; 1; 0; 0; 1; 1})

= 4

ZWISCHENSUMME-WENN mit mehreren Kriterien

Um mehrere Kriterien hinzuzufügen, kombinieren Sie diese einfach innerhalb der SUMMENPRODUKT-Funktion wie folgt:

=SUMMENPRODUKT((<Wertebereich 1>=<Kriterium 1>)*(<Wertebereich 2>=<Kriterium 2>)*(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(<erste Zelle im Bereich>;ZEILE(<Wertebereich>)-MIN(ZEILE(<Wertebereich>));0))))

=SUMMENPRODUKT((E2:E14="Teilgenommen")*(B2:B14=2019)*(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(E2;ZEILE(E2:E14)-MIN(ZEILE(E2:E14));0))))

ZWISCHENSUMME-WENN in Google Sheets

Die ZWISCHENSUMME-WENN-Funktion funktioniert in Google Sheets genau so wie in Excel. Beachten Sie jedoch, dass Google Sheets die ARRAYFORMULA-Funktion zur Formel hinzufügt, wenn Sie STRG + UMSCHALTTASTE + ENTER zur Eingabe der Array-Formel verwenden (Sie können diese Funktion auch manuell hinzufügen).