TEILERGEBNIS in Excel: Summe, die den Filter beachtet

Warum SUMME nach dem Filtern nicht stimmt

Du filterst eine Rechnungsliste auf einen Monat, und die Summe darunter bleibt gleich. Das ist kein Fehler: SUMME rechnet mit jeder Zelle im Bereich, egal ob sie gerade zu sehen ist. Ein AutoFilter blendet Zeilen nur aus, er löscht sie nicht.

TEILERGEBNIS fragt bei jeder Zeile zuerst: Ist sie sichtbar? Erst dann rechnet sie mit. Darum passt das Ergebnis immer zu dem, was auf dem Bildschirm steht. Änderst du den Filter, rechnet die Formel sofort neu. Das ist ideal für eine Ergebniszeile, die bei jeder Auswertung mitgehen soll: Umsatz pro Kunde, Stunden pro Projekt, Kosten pro Kostenstelle.

Alle Funktionsnummern im Überblick

Die erste Zahl entscheidet, was TEILERGEBNIS rechnet. 1 Mittelwert, 2 Anzahl (nur Zahlen, wie ANZAHL), 3 Anzahl aller gefüllten Zellen (wie ANZAHL2), 4 Maximum, 5 Minimum, 6 Produkt, 7 Standardabweichung einer Stichprobe (STABW), 8 Standardabweichung der Grundgesamtheit (STABWN), 9 Summe, 10 Varianz einer Stichprobe, 11 Varianz der Grundgesamtheit.

Zu jeder Nummer gibt es eine Zwillingsnummer mit 100 davor: 101 bis 111. Die rechnen genauso, lassen aber zusätzlich von Hand ausgeblendete Zeilen weg. Im Alltag brauchst du fast nur 9, 1, 3 und ihre Hunderter-Zwillinge. Was genau ANZAHL und ANZAHL2 zählen, steht auf der Seite ANZAHL.

TEILERGEBNIS 9 oder 109: der Unterschied

Zeilen können auf zwei Arten verschwinden. Gefiltert: Du hast im Filterpfeil einen Wert abgewählt. Ausgeblendet: Du hast Zeilen markiert und mit Rechtsklick › Ausblenden versteckt, oder eine Gruppierung zugeklappt.

Gefilterte Zeilen lassen 9 und 109 beide weg. Ausgeblendete Zeilen lässt nur 109 weg, die 9 rechnet sie mit. Wenn du nur mit dem AutoFilter arbeitest, ist es also egal. Wenn du in der Liste auch mal Zeilen von Hand versteckst und die Summe dazu passen soll, nimm 109. Im Zweifel ist 109 die Variante, die zu dem passt, was du siehst.

Ausgeblendete Spalten ignoriert TEILERGEBNIS dagegen nie. Die Funktion ist für Listen gebaut, die nach unten laufen. Summierst du eine Zeile von links nach rechts und blendest eine Spalte aus, zählt sie trotzdem mit.

Zwischensummen ohne Doppelzählen

Eine Eigenschaft wird oft übersehen: TEILERGEBNIS überspringt alle anderen TEILERGEBNIS-Formeln in seinem Bereich. Hast du in einer Liste Zwischensummen pro Monat mit TEILERGEBNIS gebildet, kannst du ganz unten einfach =TEILERGEBNIS(9;D2:D80) über die ganze Spalte schreiben. Die Zwischensummen werden nicht ein zweites Mal addiert.

Mit SUMME wäre das Ergebnis doppelt so hoch, weil jede Zwischensumme noch einmal in der Gesamtsumme landet. Das ist einer der häufigsten Fehler in gewachsenen Excel-Listen und ein guter Grund, Zwischensummen grundsätzlich mit TEILERGEBNIS zu bauen.

Daten › Teilergebnis: Gruppensummen auf Knopfdruck

Neben der Funktion gibt es einen gleichnamigen Befehl, der dir die Arbeit abnimmt. Er fügt nach jeder Gruppe eine Zeile mit Zwischensumme ein und ganz unten ein Gesamtergebnis. Vorher musst du die Liste nach der Spalte sortieren, nach der gruppiert wird, zum Beispiel nach Tierart oder Monat. Sonst entsteht bei jedem Wechsel eine neue Gruppe. Wie das geht, zeigt Excel sortieren.

Dann klickst du in die Liste und wählst Daten › Gliederung › Teilergebnis. Im Dialog stellst du ein: „Gruppieren nach“ die sortierte Spalte, „Unter Verwendung von“ Summe, und bei „Teilergebnis addieren zu“ einen Haken bei der Betragsspalte. Nach OK stehen unter jeder Gruppe Zeilen wie „Hund Ergebnis“, darunter das „Gesamtergebnis“. In diesen Zeilen stecken ganz normale TEILERGEBNIS-Formeln mit der 9.

Links neben den Zeilennummern erscheinen die Gliederungsknöpfe 1, 2 und 3. Mit 2 siehst du nur noch die Zwischensummen, mit 1 nur das Gesamtergebnis. Wieder weg bekommst du alles über denselben Dialog mit Alle entfernen.

Der Befehl ist ausgegraut? Dann steckt deine Liste in einer Excel-Tabelle. Klick in die Tabelle und wähl Tabellenentwurf › In Bereich konvertieren. Für Auswertungen nach mehreren Merkmalen ist eine Pivot-Tabelle meist bequemer, denn sie verändert die Liste nicht.

Excel schreibt TEILERGEBNIS oft von selbst

Klickst du unter einer gefilterten Liste auf AutoSumme (Alt + Umschalt + 0), setzt Excel automatisch TEILERGEBNIS mit der 9 ein statt SUMME. Und in einer Excel-Tabelle mit eingeschalteter Ergebniszeile steht unter jeder Spalte =TEILERGEBNIS(109;[Betrag]). Wenn dir die Formel also in fremden Dateien begegnet, stammt sie oft genau daher.

Wenn Fehler in der Liste stehen: AGGREGAT

Steht in einer Zelle im Bereich ein Fehler wie #NV, liefert TEILERGEBNIS selbst #NV. Die Lösung heißt AGGREGAT, die große Schwester von TEILERGEBNIS. =AGGREGAT(9;7;D2:D50) bildet die Summe und ignoriert dabei ausgeblendete Zeilen und Fehlerwerte. Die 9 ist wieder die Summe, die 7 die Option „ausgeblendete Zeilen und Fehler ignorieren“. Besser ist natürlich, den Fehler an der Quelle zu beheben, zum Beispiel mit WENNFEHLER.

Statusleiste als Kontrolle

Markier nach dem Filtern die Betragsspalte. Unten rechts in der Statusleiste zeigt Excel Summe, Mittelwert und Anzahl, und zwar nur für die sichtbaren Zellen. Stimmt die Zahl mit deinem TEILERGEBNIS überein, passt die Formel. Zeigt die Formel mehr, ist es wahrscheinlich doch eine SUMME oder der Bereich ist zu groß.

Brauchst du eine Summe nach einer Bedingung, ganz ohne Filter, ist SUMMEWENN der direktere Weg.