Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie man „Zwischensumme wenn“ berechnet, indem nur sichtbare Zeilen mit Kriterien gezählt werden.
ZWISCHENSUMME Funktion
Die Funktion ZWISCHENSUMME kann verschiedene Berechnungen für eine Reihe von Daten durchführen (Zählung, Summe, Durchschnitt usw.). Am wichtigsten ist, dass es verwendet werden kann, um nur auf sichtbare (gefilterte) Zeilen zu rechnen. In diesem Beispiel verwenden wir die Funktion zum Zählen (COUNTA) sichtbarer Zeilen, indem wir das Argument SUBTOTAL function_num auf 3 setzen (Eine vollständige Liste der möglichen Funktionen finden Sie hier.)
=ZWISCHENSUMME(3,$D$2:$D$14)
Beachten Sie, wie sich die Ergebnisse ändern, wenn wir Zeilen manuell filtern.
ZWISCHENSUMME WENN
Um eine „Zwischensumme Wenn“ zu erstellen, verwenden wir eine Kombination aus SUMPRODUKT, ZWISCHENSUMME, OFFSET, ZEILE und MIN in einer Matrixformel. Mit dieser Kombination können wir im Wesentlichen eine generische „ZWISCHENSUMME WENN“-Funktion erstellen. Gehen wir ein Beispiel durch.
Wir haben eine Liste der Mitglieder und deren Anwesenheitsstatus für jede Veranstaltung:
Angenommen, wir werden gebeten, die Anzahl der Mitglieder, die an einer Veranstaltung teilgenommen haben, dynamisch zu zählen, während wir die Liste wie folgt manuell filtern:
Um dies zu erreichen, können wir diese Formel verwenden:
=SUMMENPRODUKT((=)*(ZWISCHENSUMME(3,OFFSET(,ZEILE()-MIN(ZEILE()),0))))
=SUMMENPRODUKT((D2:D14="Betreut")*(ZWISCHENSUMME(3,OFFSET(D2,REIHE(D2:D14)-MIN(ZEILE(D2:D14)),0))))
Wenn Sie Excel 2022 und früher verwenden, müssen Sie die Matrixformel eingeben, indem Sie drücken STRG + UMSCHALT + EINGABETASTE um Excel mitzuteilen, dass Sie eine Arrayformel eingeben. Sie wissen, dass die Formel richtig als Array-Formel eingegeben wurde, wenn geschweifte Klammern um die Formel erscheinen (siehe Abbildung oben).
Wie funktioniert die Formel?
Die Formel funktioniert, indem zwei Arrays innerhalb von SUMPRODUCT multipliziert werden, wobei das erste Array unsere Kriterien behandelt und das zweite Array nur auf sichtbare Zeilen filtert:
=SUMMENPRODUKT(*)
Das Kriterien-Array
Das Kriterien-Array wertet jede Zeile in unserem Wertebereich aus (in diesem Beispiel „Besetzter“ Status) und generiert ein Array wie folgt:
=(=)
=(D2:D14="Anwesend")
Ausgabe:
{WAHR; FALSCH; FALSCH; WAHR; FALSCH; TUR; TUR; TUR; FALSCH; FALSCH; WAHR; FALSCH; WAHR}
Beachten Sie, dass die Ausgabe im ersten Array in unserer Formel ignoriert, ob die Zeile sichtbar ist oder nicht, und hier hilft unser zweites Array.
Das Sichtbarkeits-Array
Wenn wir SUBTOTAL verwenden, um nicht sichtbare Zeilen in unserem Bereich auszuschließen, können wir unser Sichtbarkeitsarray generieren. SUBTOTAL allein gibt jedoch einen einzelnen Wert zurück, während SUMPRODUCT ein Array von Werten erwartet. Um dies zu umgehen, verwenden wir OFFSET, um jeweils eine Zeile zu übergeben. Diese Technik erfordert die Eingabe von OFFSET in ein Array, das jeweils eine Zahl enthält. Das zweite Array sieht so aus:
=ZWISCHENSUMME(3,OFFSET(,ZEILE()-MIN(ZEILE()),0))
=ZWISCHENSUMME(3,OFFSET(D2,REIHE(D2:D14)-MIN(ZEILE(D2:D14)),0))
Ausgabe:
{1;1;0;0;1;1}
Zusammennähen 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, müssen Sie einfach mehrere weitere Kriterien im SUMPRODUCT wie folgt zusammenfassen:
=SUMMENPRODUKT((=)*(=)*(ZWISCHENSUMME(3,OFFSET(,ZEILE()-MIN(ZEILE()),0))))
=SUMMENPRODUKT((E2:E14="Betreut")*(B2:B14=2019)*(ZWISCHENSUMME(3,OFFSET(E2,REIHE(E2:E14)-MIN(ZEILE(E2:E14)),0)) ))
ZWISCHENSUMME WENN in Google Tabellen
Die ZWISCHENSUMME IF-Funktion funktioniert in Google Sheets genauso wie in Excel:
