Kapitel 17 · 17.18
Nur die gefilterten Zeilen summieren, auch mit einer zusätzlichen Bedingung
Sie filtern eine Verkaufsliste nach einer Kategorie, aber die Summe darunter zeigt weiterhin den Betrag aller Zeilen. Das ist richtig so: SUMME zählt auch die Zeilen mit, die der Filter ausblendet. Möchten Sie nur das summieren, was Sie sehen, verwenden Sie TEILERGEBNIS. Und wenn Sie innerhalb der gefilterten Zeilen noch eine zusätzliche Bedingung brauchen oder Fehlerwerte überspringen möchten, gibt es zwei weitere praktische Formeln.
Als Beispiel nehmen wir die Verkaufsliste von Bloemisterij Het Zonnetje: in A das Datum, in B das Produkt, in C die Kategorie und in D der Betrag, von Zeile 2 bis einschließlich 300. Auf der Kopfzeile liegt ein Filter (siehe Sortieren und Filtern).
Wo platzieren Sie die Formeln?
Nicht unter der Liste: Eine Formel direkt unter der Liste kann beim Filtern selbst ausgeblendet werden und beim Sortieren mitwandern. Aber auch nicht einfach neben der Liste: Ein Filter blendet ganze Zeilen aus, eine Formel in F3 verschwindet also mit, wenn Zeile 3 herausgefiltert wird. Platzieren Sie die Formeln deshalb in der Kopfzeile (Zeile 1, neben den Überschriften) oder auf einem separaten Arbeitsblatt. Im Beispiel stehen sie in Zeile 1, ab Spalte F.
Die Summe der sichtbaren Zeilen
=TEILERGEBNIS(109;D2:D300)
Die erste Zahl wählt die Berechnung. 9 ist die Summe, 109 ebenfalls, mit einem Unterschied: 109 überspringt auch Zeilen, die Sie von Hand ausgeblendet haben (rechte Maustaste, Ausblenden). 9 überspringt nur Zeilen, die der Filter ausblendet. Verwenden Sie also 109, wenn Sie das summieren möchten, was Sie sehen.
| Berechnung | Gefiltert | Auch manuell ausgeblendet |
|---|---|---|
| Summe | 9 | 109 |
| Anzahl gefüllter Zellen | 3 | 103 |
| Mittelwert | 1 | 101 |
| Maximum | 4 | 104 |
Die Anzahl der sichtbaren Zeilen ermitteln Sie mit =TEILERGEBNIS(103;B2:B300).

Sichtbar und eine zusätzliche Bedingung
Sie haben nach der Kategorie Boeketten gefiltert und möchten darin nur die Trauergestecke (Rouwstuk) summieren. SUMMEWENNS berücksichtigt den Filter nicht, TEILERGEBNIS kennt keine Bedingung. Im Internet finden Sie dafür eine Formel mit SUMMENPRODUKT, TEILERGEBNIS und BEREICH.VERSCHIEBEN. Diese liefert in Euro-Office den Fehlerwert #ZAHL!. Mit einer Hilfsspalte klappt es dagegen, und zwar einfacher:
- Legen Sie neben der Liste eine Spalte Kann ansehen (sichtbar) an, hier Spalte E. Markieren Sie E2:E300, geben Sie =TEILERGEBNIS(103;D2) ein und drücken Sie Strg+Enter. Jede Zeile erhält eine 1, solange sie sichtbar ist, und eine 0, wenn der Filter sie ausblendet.
- Summieren Sie mit SUMMEWENNS und nehmen Sie die Hilfsspalte als zusätzliche Bedingung auf: =SUMMEWENNS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Ändern Sie den Filter, berechnen die Hilfsspalte und die Summe sofort neu.
Fehlerwerte überspringen
Steht irgendwo in der Spalte ein #NV oder #DIV/0!, liefern SUMME und TEILERGEBNIS ebenfalls einen Fehlerwert. AGGREGAT kann Fehler überspringen:
=AGGREGAT(9;7;D2:D300)
Die 9 ist wieder die Summe, die 7 bedeutet: ausgeblendete Zeilen und Fehlerwerte überspringen.
Stolperfallen
- Ausgeblendete Spalten werden mitgezählt: TEILERGEBNIS berücksichtigt nur ausgeblendete Zeilen.
