Summe nach Kategorie oder Gruppe – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie Zwischensummen nach Gruppen mit der SUMMEWENNS-Funktion in Excel und Google Sheets berechnen können.

Zwischensummentabelle nach Kategorie oder Gruppe

Dieses erste Beispiel setzt voraus, dass Sie Zugriff auf die EINDEUTIG-Funktion haben. Diese Funktion ist in neueren Versionen von Excel, Excel 365 oder Google Sheets verfügbar. Wenn Sie keinen Zugang zu dieser Funktion haben, fahren Sie mit dem nächsten Abschnitt fort.

Wir können die EINDEUTIG-Funktion und die SUMMEWENNS-Funktion verwenden, um die Zwischensumme der Produktanzahl nach Produktgruppe automatisch zu bilden. Dies ist die endgültige SUMMEWENNS-FUNKTION:

=SUMMEWENNS(C3:C11;B3:B11;E3)

Allerdings fügen wir zunächst die EINDEUTIG-Funktion in Zelle E3 ein:

=EINDEUTIG(B3:B11)

Wenn diese Formel eingegeben wird, wird eine Liste unter der Zelle, die alle eindeutigen Werte innerhalb der Produktgruppe enthält, automatisch erstellt.

Es handelt sich hierbei um eine dynamische Array-Funktion, bei der die Größe der Ergebnisliste nicht definiert werden muss. Sie wird automatisch verkleinert und vergrößert, wenn sich die Eingabedaten ändern.

Beachten Sie, dass in Excel 365 bei der EINDEUTIG-Funkion nicht zwischen Groß- und Kleinschreibung unterschieden wird, aber in Google Sheets hingegen schon.

Zwischensummentabelle nach Kategorie oder Gruppe (ohne EINDEUTIG-Funktion)

Wenn Sie eine Version von Excel ohne die EINDEUTIG-Funktion verwenden, können Sie die INDEX-Funktion und die VERGLEICH-Funktion mit einer ZÄHLENWENN-Funktion kombinieren, um eine Array-Formel, die eine Liste eindeutiger Werte aus einem Bereich von Zellen erzeugt, zu erstellen:

{=INDEX($B$3:$B$11;VERGLEICH(0;ZÄHLENWENN($E$2:E2;$B$3:$B$11);0))}

Damit diese Formel funktioniert, müssen die festen Zellenbezüge sorgfältig geschrieben werden, wobei die Funktion ZÄHLENWENN den Bereich $E$2:E2 referenziert, d.h. den Bereich von E2 bis zur Zelle über der Zelle, die die Formel enthält.

Die Formel muss auch als Array-Formel eingegeben werden, indem Sie nach dem Schreiben die Tasten STRG + UMSCHALT + ENTER drücken. Diese Formel ist eine 1-Zellen-Array-Formel, die dann in die Zellen E4, E5 usw. kopiert werden kann. Geben Sie diese Formel nicht als Array-Formel für den gesamten Bereich E3:E5 in einem Arbeitsgang ein.

Auf die gleiche Weise wie im vorherigen Beispiel wird dann eine SUMMEWENNS-Funktion verwendet, um die Zwischensumme der Produktanzahl nach Produktgruppe zu bilden:

=SUMMEWENNS(C3:C11;B3:B11;E3)

Summe nach Kategorie oder Gruppe – Zwischensummen in Datentabellen

Alternativ können wir Zwischensummen direkt in eine Datentabelle mit den Funktionen WENN und SUMMEWENNS einfügen.

=WENN(B3=B2;"";SUMMEWENNS(C3:C11;B3:B11;B3))

In diesem Beispiel verwenden wir eine SUMMEWENNS-Funktion, die in eine WENN-Funktion eingebettet ist.  Teilen wir das Beispiel in einzelne Schritte auf:

Zunächst summieren wir die Produktanzahl, die der jeweiligen Produktgruppe entsprechen:

=SUMMEWENNS(C3:C11;B3:B11;B3)

Mit dieser Formel wird eine Zwischensumme für jede Datenzeile gebildet. Um Zwischensummen nur in der ersten Datenzeile jeder Produktgruppe anzuzeigen, verwenden wir die WENN-Funktion. Beachten Sie, dass die Daten bereits nach Produktgruppen sortiert sein müssen, damit die Zwischensummen korrekt angezeigt werden.

=WENN(B3=B2;"";SUMMEWENNS(C3:C11;B3:B11;B3))

Die WENN-Funktion vergleicht den Produktgruppenwert jeder Datenzeile mit der darüber liegenden Datenzeile und gibt bei gleichem Wert eine leere Zelle („“) aus.

Wenn die Produktgruppenwerte unterschiedlich sind, wird die Summe angezeigt. Auf diese Weise wird jede Produktgruppensumme nur einmal angezeigt (in der Zeile ihrer ersten Instanz).

Sortieren von Datensätzen nach Gruppe

Wenn die Daten noch nicht sortiert sind, können wir immer noch die gleiche Formel für die Zwischensumme verwenden.

Der obige Datensatz ist nicht nach Produktgruppe sortiert, so dass jede Zwischensumme in der Spalte Zwischensumme nach Gruppe mehr als einmal angezeigt wird. Um die Daten in das gewünschte Format zu bringen, können wir die Datentabelle auswählen und auf „A bis Z sortieren“ klicken.

Zellenbezüge sperren

Um unsere Formeln besser lesbar zu machen, haben wir einige der Formeln ohne gesperrten Zellenbezüge dargestellt:

=WENN(B3=B2;"";SUMMEWENNS(C3:C11;B3:B11;B3))

Diese Formeln funktionieren jedoch nicht richtig, wenn sie kopiert und an anderer Stelle in Ihrer Datei eingefügt werden. Stattdessen sollten Sie gesperrte Zellenbezüge wie diese verwenden:

=WENN(B3=B2;"";SUMMEWENNS($C$3:$C$11;$B$3:$B$11;B3))

Lesen Sie unseren Artikel über das Sperren von Zellenbezügen, um mehr zu erfahren.

Verwenden von Pivot-Tabellen zum Anzeigen von Zwischensummen

Um die Daten nicht nach Produktgruppen vorsortieren zu müssen, können wir stattdessen die Leistung von Pivot-Tabellen nutzen, um die Daten zusammenzufassen. Pivot-Tabellen berechnen automatisch Zwischensummen und zeigen Summen und Zwischensummen in mehreren verschiedenen Formaten an.

Summe nach Kategorie oder Gruppe in Google Sheets

Diese Formeln funktionieren in Google Sheets auf die gleiche Weise wie in Excel. Allerdings wird bei der EINDEUTIG-Funktion in Google Sheets zwischen Groß- und Kleinschreibung unterschieden.