TEILERGEBNIS und AGGREGAT: Summen, die mit dem Filter rechnen

Kurz gesagt: =TEILERGEBNIS(109;C2:C50) summiert nur die sichtbaren Zeilen, gefilterte und von Hand ausgeblendete fallen weg. Mit 9 statt 109 fallen nur gefilterte Zeilen weg. =AGGREGAT(9;6;C2:C50) summiert und überspringt dabei Fehlerwerte wie #NV.

Probier es gleich aus

Eine Liste mit Reisekosten soll nach Abteilung gefiltert werden, und darunter soll der Durchschnitt stehen, der zum Filter passt.

Aufgabe Unter einer gefilterten Liste soll der Durchschnitt der Reisekosten stehen, der sich mit dem Filter ändert. Schreib in D10 TEILERGEBNIS mit dem Code für Mittelwert ohne ausgeblendete Zeilen.

	A	B	C	D
1	Mitarbeiter	Abteilung	Reise	Kosten €
2	Weber	Vertrieb	Hamburg	412,5
3	Kaya	Einkauf	Köln	186
4	Novak	Vertrieb	Wien	640,8
5	Weber	Vertrieb	Bremen	95,4
6	Schulz	IT	München	328
7	Kaya	Einkauf	Leipzig	271,3
8	Novak	IT	Zürich	504
9
10	Ø sichtbar

So ist TEILERGEBNIS aufgebaut

=TEILERGEBNIS(Funktion;Bezug1;[Bezug2];…). Die erste Zahl sagt, was gerechnet wird, danach folgen ein oder mehrere Bereiche.

Code mit ausgeblendeten Zeilen	Code ohne ausgeblendete Zeilen	Funktion
1	101	MITTELWERT
2	102	ANZAHL (Zahlen zählen)
3	103	ANZAHL2 (nicht leere Zellen zählen)
4	104	MAX
5	105	MIN
6	106	PRODUKT
7, 8	107, 108	STABW, STABWN
9	109	SUMME
10, 11	110, 111	VARIANZ, VARIANZEN

TEILERGEBNIS 9 oder 109?

Beide Varianten lassen Zeilen weg, die ein Filter ausblendet. Der Unterschied betrifft nur Zeilen, die du von Hand ausblendest (Rechtsklick auf die Zeilennummer, Ausblenden): 9 zählt sie mit, 109 lässt sie weg. Wer „nur sichtbare Zellen summieren“ will, nimmt also 109. Die Ergebniszeile einer intelligenten Tabelle schreibt genau so eine Formel mit 109 hinein.

Ein zweiter Vorteil: Steht in einem Bereich schon ein TEILERGEBNIS, etwa eine Zwischensumme je Abteilung, überspringt ein äußeres TEILERGEBNIS diese Zelle. So wird nichts doppelt gezählt, und die Gesamtsumme darf über die ganze Spalte laufen.

Gefilterte Daten auswerten und nach Farbe zählen

Setz einen Filter und schreib unter die Liste =TEILERGEBNIS(109;C2:C50) für die Summe und =TEILERGEBNIS(103;A2:A50) für die Zahl der sichtbaren Zeilen. Jede Änderung am Filter ändert beide Werte sofort.

Excel hat keine Formel, die Zellen nach ihrer Füllfarbe zählt. Mit dem Filter klappt es trotzdem: Im Filtermenü der Spalte wählst du Nach Farbe filtern und die Farbe. Danach zeigt TEILERGEBNIS mit 103 die Anzahl und mit 109 die Summe der farbigen Zeilen.

Teilergebnisse automatisch einfügen

Für Zwischensummen je Gruppe gibt es einen Befehl: Sortiere die Liste zuerst nach der Spalte, nach der gruppiert wird. Dann klickst du auf der Registerkarte Daten in der Gruppe Gliederung auf Teilergebnis (in der aktuellen Microsoft-Hilfe heißt die Schaltfläche Zwischensumme). Excel fügt bei jeder Änderung in der gewählten Spalte eine Zeile mit TEILERGEBNIS ein. In einer intelligenten Tabelle ist der Befehl grau, dort nimmst du die Ergebniszeile oder wandelst die Tabelle vorher in einen normalen Bereich um.

AGGREGAT: wie TEILERGEBNIS, aber mit Optionen

=AGGREGAT(Funktion;Optionen;Bezug1;…) kennt 19 Funktionen und lässt dich wählen, was übersprungen wird. Die Funktionen 1 bis 11 entsprechen denen von TEILERGEBNIS (1 MITTELWERT, 4 MAX, 5 MIN, 9 SUMME), dazu kommen unter anderem 12 MEDIAN, 14 KGRÖSSTE, 15 KKLEINSTE und 16 QUANTIL.INKL. Die Optionen:

Option	AGGREGAT ignoriert
0 oder leer	verschachtelte TEILERGEBNIS und AGGREGAT
1	ausgeblendete Zeilen und verschachtelte Ergebnisse
2	Fehlerwerte und verschachtelte Ergebnisse
3	ausgeblendete Zeilen, Fehlerwerte und verschachtelte Ergebnisse
4	nichts
5	ausgeblendete Zeilen
6	Fehlerwerte
7	ausgeblendete Zeilen und Fehlerwerte

Das typische Einsatzgebiet: Eine Spalte enthält ein paar #NV aus einem SVERWEIS. SUMME zeigt dann selbst #NV, =AGGREGAT(9;6;C2:C50) rechnet mit den übrigen Werten. Die Funktionen 14 bis 19 brauchen am Ende noch ein k, etwa 1 für den größten oder kleinsten Wert, wie bei KGRÖSSTE. Mit ihnen geht auch eine Rechnung statt eines Bereichs.

Noch eine Übung: Fehler mit AGGREGAT überspringen

Zwei stornierte Bestellungen haben die Menge 0. Teilst du den Betrag durch die Menge, entsteht dort #DIV/0!. Gesucht ist trotzdem der niedrigste Stückpreis.

Aufgabe Der Stückpreis ist Betrag durch Menge. Zwei Bestellungen wurden storniert und haben Menge 0, dort ergäbe die Division #DIV/0!. Berechne in C10 den niedrigsten Stückpreis und lass die Fehler von AGGREGAT überspringen.

	A	B	C
1	Lieferant	Menge	Betrag €
2	Nordwerk	400	948
3	Elbtal AG	0	0
4	Kaya GmbH	250	562,5
5	Sachsenbau	600	1338
6	Hafenlogistik	0	0
7	Weber & Söhne	150	361,5
8	Spree Media	320	726,4
9
10	Niedrigster Stückpreis

Diese Übung gehört zu Premium.

Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.

Premium ansehen Schon Premium? Anmelden

Die erste Übung dieser Lektion ist kostenlos.

Typische Fehler

Ausgeblendete Spalten zählen mit

TEILERGEBNIS und AGGREGAT sind für senkrechte Bereiche gebaut. Blendest du Spalten aus, ändert sich eine waagerechte Summe auch mit 109 nicht.

#WERT! bei AGGREGAT

Fehlt bei den Funktionen 14 bis 19 das k am Ende, zeigt AGGREGAT #WERT!. Dasselbe passiert bei 3D-Bezügen über mehrere Blätter, auch bei TEILERGEBNIS.

Option ignoriert bei einer Rechnung keine ausgeblendeten Zeilen

Steckt im Bereich eine Rechnung wie C2:C8/B2:B8, ignoriert AGGREGAT laut Microsoft keine ausgeblendeten Zeilen und keine verschachtelten Ergebnisse. Fehlerwerte überspringt die Option 6 trotzdem. Brauchst du nur einen Ersatzwert für Fehler, ist WENNFEHLER die einfachere Wahl.

TEILERGEBNIS und AGGREGAT auf Englisch

Deutsch	Englisch
=TEILERGEBNIS(109;C2:C50)	=SUBTOTAL(109,C2:C50)
=AGGREGAT(9;6;C2:C50)	=AGGREGATE(9,6,C2:C50)
Daten, Gliederung, Teilergebnis	Data, Outline, Subtotal

In welchen Excel-Versionen gibt es TEILERGEBNIS und AGGREGAT?

Microsoft führt beide für Microsoft 365, Excel 2024, 2021, 2019 und 2016, jeweils auch für den Mac. AGGREGAT gibt es laut Microsoft zusätzlich in Excel im Web.

Häufige Fragen