Excel-Tutorial: Nur sichtbare Zellen summieren und zählen (Gefilterte Daten)

Hier ist ein ausführliches Tutorial, das dir zeigt, wie du in Excel nur die sichtbaren Zellen summierst oder zählst und dabei gefilterte oder manuell ausgeblendete Zeilen ignorierst.

In Excel stehen wir oft vor dem Problem, dass die Standardfunktionen SUMME oder ANZAHL auch die Werte mitberechnen, die durch einen Filter oder manuelles Ausblenden unsichtbar sind. Um nur mit den Werten zu arbeiten, die du tatsächlich auf dem Bildschirm siehst, benötigst du spezielle Funktionen: TEILERGEBNIS oder AGGREGAT.

1. Das Problem mit der Standard-SUMME

Wenn du eine Liste filterst und am Ende =SUMME(A1:A10) nutzt, wird Excel immer noch alle 10 Zellen addieren, selbst wenn durch einen Filter nur noch 3 Zeilen sichtbar sind.

2. Die Lösung: Die Funktion TEILERGEBNIS

Die Funktion TEILERGEBNIS (englisch: SUBTOTAL) ist der Standardweg, um gefilterte Listen auszuwerten.

Die Syntax: =TEILERGEBNIS(Funktion; Bereich)

A) Nur sichtbare Zellen summieren

Verwende die Funktionszahl 109 (Summe, ignoriert verborgene Zeilen).

• Formel: =TEILERGEBNIS(109; B2:B100)

B) Nur sichtbare Zellen zählen (Zahlen)

Verwende die Funktionszahl 102 (Anzahl, ignoriert verborgene Zeilen).

• Formel: =TEILERGEBNIS(102; B2:B100)

C) Nur sichtbare Zellen zählen (Text/Nicht leer)

Verwende die Funktionszahl 103 (Anzahl2, ignoriert verborgene Zeilen).

• Formel: =TEILERGEBNIS(103; B2:B100)

Wichtiger Hinweis zum Unterschied (9 vs. 109):

• Code 1-11: Ignoriert nur Zeilen, die durch einen Filter ausgeblendet wurden.
• Code 101-111: Ignoriert Zeilen, die durch einen Filter ODER manuell (Rechtsklick -> Ausblenden) versteckt wurden.
• Empfehlung: Nutze meistens die 100er-Codes (z. B. 109), um auf der sicheren Seite zu sein.

3. Die Profi-Lösung: Die Funktion AGGREGAT

Die Funktion AGGREGAT ist noch mächtiger als TEILERGEBNIS, da sie nicht nur ausgeblendete Zeilen, sondern optional auch Fehlermeldungen (wie #NV oder #DIV/0!) ignorieren kann.

Die Syntax: =AGGREGAT(Funktion; Optionen; Bereich)

• Summieren (Sichtbare Zellen & keine Fehler): =AGGREGAT(9; 5; B2:B100) (9 = Summe; 5 = Nur verborgene Zeilen ignorieren)

  Oder um Fehler UND verborgene Zeilen zu ignorieren: =AGGREGAT(9; 7; B2:B100)

• Zählen (Sichtbare Zellen): =AGGREGAT(2; 5; B2:B100)

4. Schritt-für-Schritt Anleitung (Praxisbeispiel)

Angenommen, du hast eine Liste von Verkaufszahlen in Spalte B (B2:B10).

1. Filter setzen: Markiere deine Überschriften und drücke Strg + Umschalt + L. Filter die Liste nach einem beliebigen Kriterium.
2. Ergebnis-Zelle wählen: Gehe in eine leere Zelle unter deiner Liste (z.B. B12).
3. Formel eingeben:
   • Für die Summe: =TEILERGEBNIS(109; B2:B10)
   • Für die Anzahl: =TEILERGEBNIS(102; B2:B10)
4. Testen: Ändere deinen Filter. Du wirst sehen, dass sich das Ergebnis sofort anpasst und nur die Werte berechnet, die aktuell sichtbar sind.

5. Quick-Tipp: Automatische Summe in "Als Tabelle formatierten" Listen

Wenn du deine Daten als offizielle Excel-Tabelle formatierst (Strg + T), kannst du die "Ergebniszeile" aktivieren:

1. Klicke in deine Tabelle.
2. Gehe oben im Menü auf Tabellenentwurf.
3. Setze einen Haken bei Ergebniszeile.
4. Excel fügt unten eine Zeile ein. Dort kannst du per Dropdown-Menü einfach "Summe" oder "Anzahl" auswählen. Excel nutzt hierbei automatisch die TEILERGEBNIS-Funktion.

Zusammenfassung der wichtigsten Codes für TEILERGEBNIS: