TEILERGEBNIS in Excel: Summen ohne Zwischensummen (SUBTOTAL)

=TEILERGEBNIS(9;C2:C8) addiert die Zahlen in C2:C8 wie SUMME, mit zwei Unterschieden: Es überspringt jede andere TEILERGEBNIS-Formel im Bereich, und es überspringt Zeilen, die ein Filter ausblendet. Das erste Argument, 9, sagt, welche Berechnung ausgeführt wird. TEILERGEBNIS heißt in englischem Excel SUBTOTAL, und die Tabelle zeigt die Formeln so: =SUBTOTAL(9,C2:C7). Du kannst die Formeln in der Tabelle auch deutsch eingeben, mit Semikolons.

FormelnZurücksetzen

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =TEILERGEBNIS(9;C2:C7)

Die Gesamtsumme in C8 läuft über die ganze Spalte, die Zwischensummenzeilen eingeschlossen, und zeigt trotzdem 445: TEILERGEBNIS lässt C4 und C7 aus, weil dort TEILERGEBNIS-Formeln stehen. F2 macht dasselbe mit SUMME und zeigt 890, jeder Umsatz doppelt gezählt. Mit TEILERGEBNIS in jeder Summenzeile kannst du Gruppen hinzufügen oder verschieben, ohne die Gesamtsumme neu zu schreiben.

Funktionsnummern von TEILERGEBNIS

=SUBTOTAL(function_num, ref1, [ref2], ...)

Wenn du =TEILERGEBNIS( tippst, zeigt Excel diese Liste an, du musst sie also nicht auswendig lernen. 9 und 109 (SUMME), 1 (MITTELWERT) und 103 (sichtbare Zeilen zählen) werden am häufigsten verwendet.

FormelnZurücksetzen

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =TEILERGEBNIS(1;C2:C7)

Hier ist nichts ausgeblendet, also entspricht jede Zeile der normalen Funktion: ein Mittelwert von 88.33, 6 Zeilen, ein MAX von 200, ein MIN von 30 und eine SUMME von 530. Der Unterschied zeigt sich erst, wenn Zeilen ausgeblendet sind, und darum geht es im nächsten Abschnitt.

TEILERGEBNIS 9 oder 109, und gefilterte Zeilen

Schalte mit Daten > Filtern (Strg+Umschalt+L, Cmd+Umschalt+F auf dem Mac) einen Filter ein und wähle dann North im Dropdown von Region. Die Zeilen der anderen Regionen werden ausgeblendet:

• =SUMME(C2:C7) addiert weiterhin alle sechs Zeilen.
• =TEILERGEBNIS(9;C2:C7) und =TEILERGEBNIS(109;C2:C7) addieren nur die sichtbaren North-Zeilen.
• =TEILERGEBNIS(103;A2:A7) zählt die Zeilen, die auf dem Bildschirm bleiben: 2, dieselbe Zahl wie "2 von 6 Datensätzen gefunden" in der Statusleiste.

Die beiden Gruppen unterscheiden sich nur bei Zeilen, die du von Hand ausblendest (Zeilen markieren, Rechtsklick > Ausblenden). 9 addiert diese weiterhin, 109 nicht. Soll die Summe immer dem entsprechen, was auf dem Bildschirm steht, nimm 109. Blendest du Zeilen nur aus, um die Ansicht aufzuräumen, und willst sie trotzdem in der Summe haben, nimm 9.

TEILERGEBNIS funktioniert nur mit Zeilen. Ausgeblendete Spalten werden immer einbezogen, also addiert =TEILERGEBNIS(109;B2:G2) über eine Zeile auch ausgeblendete Spalten.

Am schnellsten bekommst du ein TEILERGEBNIS über die Schaltfläche AutoSumme bei eingeschaltetem Filter: Excel schreibt =TEILERGEBNIS(9;...) statt SUMME. Daten > Teilergebnis geht weiter: In einer nach einer Spalte sortierten Liste fügt es unter jeder Gruppe eine Summenzeile und eine Gesamtsumme ein, alle mit TEILERGEBNIS, dazu Gliederungsschaltflächen zum Zuklappen der Gruppen.

AGGREGAT: TEILERGEBNIS, das Fehler überspringen kann

Enthält eine Zelle im Bereich einen Fehler, liefern SUMME und TEILERGEBNIS diesen Fehler. AGGREGAT (englisch AGGREGATE, ab Excel 2010) ist TEILERGEBNIS mit einem zusätzlichen Optionsargument; Option 6 ignoriert Fehlerwerte.

FormelnZurücksetzen

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =AGGREGAT(9;6;C2:C7)

C3 enthält #N/A (in deutschem Excel #NV; die Tabelle zeigt die englischen Fehlernamen), also zeigt auch F2 #N/A. F3 ignoriert den Fehler und addiert die anderen fünf: 450. Sein erstes Argument verwendet dieselben Nummern wie TEILERGEBNIS (9 ist SUMME, 4 ist MAX). Weitere Optionen: 5 ignoriert ausgeblendete Zeilen, 7 ignoriert ausgeblendete Zeilen und Fehler, 3 ignoriert ausgeblendete Zeilen, Fehler und verschachtelte TEILERGEBNIS- und AGGREGAT-Formeln. Ersetze C3 durch eine Zahl, und F2 zeigt dieselbe Summe wie F3.

Übung: eine Gesamtsumme über Zwischensummen

FormelnZurücksetzen

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Du bist dran: Die Liste hat unter jeder Region eine Zwischensumme. Setz in C9 eine Gesamtsumme, die C2:C8 abdeckt, ohne die Zwischensummenzeilen doppelt zu zählen.

PrüfenLösung zeigen

Warum eine Summe mit TEILERGEBNIS trotzdem falsch ist

• Die Gruppensummen verwenden SUMME. TEILERGEBNIS überspringt andere TEILERGEBNIS-Formeln in seinem Bereich, keine SUMME-Formeln. Eine Gruppensumme, die als =SUMME(C2:C3) geschrieben ist, wird noch einmal gezählt. Stell jede Summenzeile auf TEILERGEBNIS um.
• Die Zeilen wurden von Hand ausgeblendet, und die Funktionsnummer ist 9. Nimm 109.
• Die Daten stehen in Spalten, nicht in Zeilen. Ausgeblendete Spalten werden nie übersprungen.
• Du brauchst eine Bedingung, keinen Filter. TEILERGEBNIS richtet sich danach, was der Filter ausblendet. Um North ohne Filter zu summieren, nimmst du SUMMEWENN. Für eine Übersicht aller Gruppen auf einmal erledigt eine PivotTable das ohne Summenzeilen in den Daten.