Wie summiert man in Excel nur die sichtbaren Zellen basierend auf bestimmten Kriterien?
In Excel können Benutzer Zellen normalerweise mithilfe der Funktion SUMMEWENNS basierend auf bestimmten Kriterien summieren. Bei gefilterten Daten berücksichtigt SUMMEWENNS jedoch sowohl sichtbare als auch ausgeblendete Zellen – was häufig zu falschen Ergebnissen führt, wenn ausschließlich die sichtbaren (also nicht ausgefilterten) Zellen summiert werden sollen, die bestimmte Kriterien erfüllen, wie in der folgenden Abbildung veranschaulicht.
Sichtbare Zellen nur anhand eines oder mehrerer Kriterien mit einer Hilfsspalte summieren
Sichtbare Zellen nur anhand eines oder mehrerer Kriterien mit einer Formel summieren
Sichtbare Zellen nur anhand von Kriterien mithilfe von VBA-Code summieren
Im täglichen Reporting und in Datenanalyse-Workflows ist es häufig erforderlich, Daten in gefilterten Tabellen präzise zu aggregieren – beispielsweise beim Berechnen von Umsätzen für ein bestimmtes Produkt oder eine Kategorie nach Anwendung verschiedener Filter. Eine fehlerhafte Vorgehensweise kann dazu führen, dass unbeabsichtigte Daten in die Summen einfließen. Daher ist es wichtig, Methoden zu verwenden, die ausschließlich die sichtbaren Daten auf dem Bildschirm summieren.
Dieser Artikel stellt mehrere praktische Methoden vor, die sich ideal an unterschiedliche Szenarien und Kenntnisstufen anpassen – jede mit eigenen Stärken und gegebenenfalls bestimmten Einschränkungen. Wählen Sie einfach die Lösung, die am besten zu Ihrer Arbeitsblattgröße, Datenstruktur und Ihren individuellen Arbeitsgewohnheiten passt. Im Folgenden finden Sie detaillierte Schritt-für-Schritt-Anleitungen für jede Methode sowie hilfreiche Hinweise zu möglichen Fehlerquellen und Optimierungsmöglichkeiten für noch zuverlässigere Ergebnisse.
Sichtbare Zellen nur anhand eines oder mehrerer Kriterien mit einer Hilfsspalte summieren
Eine der intuitivsten und stabilsten Methoden, um sichtbare Zellen anhand bestimmter Kriterien zu summieren, ist die Verwendung einer Hilfsspalte, die Werte ausschließlich für sichtbare Zeilen liefert. Anschließend wenden Sie die SUMMEWENNS-Funktion mit Ihren gewünschten Bedingungen an. Diese Vorgehensweise eignet sich besonders gut, wenn Ihr Datensatz häufig unterschiedlich gefiltert wird oder wenn Sie Berechnungen einrichten möchten, die Kollegen problemlos nachvollziehen und anpassen können.
Vorteile: Einfache Einrichtung; sämtliche Logik und Berechnungen bleiben im Arbeitsblatt sichtbar; ideal für kleine bis mittlere Tabellen; robust bei Anpassungen oder beim Prüfen von Formeln.
Einschränkungen: Erzeugt zusätzliche Spalten; Formeln müssen gegebenenfalls aktualisiert werden, wenn sich das Zeilenlayout ändert; bei sehr großen Datensätzen kann der Aufwand unübersichtlich werden.
Beispielsweise, um nur die Bestellwerte für das Produkt „Hoodie“ in einer Filterbereich zu summieren:
1. Geben Sie die folgende Formel in eine leere Spalte neben Ihrem Datensatz ein (z. B. in Zelle E2, sofern Spalte D Ihre Werte enthält):
Ziehen Sie den Ausfüllknauf nach unten, um diese Formel auf alle Zeilen Ihres Datenbereichs zu kopieren. Sie gibt den Wert aus Spalte D zurück, wenn die Zeile sichtbar ist – andernfalls 0, falls die Zeile durch Filtern ausgeblendet wurde.
2. Nachdem Sie die Hilfswerte in Spalte E erstellt haben, nutzen Sie die SUMMEWENNS-Funktion, um ausschließlich die sichtbaren Werte gemäß Ihren Kriterien zu summieren. Beispiel: Um die Werte für „Hoodie“ in Spalte A zu summieren:
Sie können weitere Kriterien hinzufügen, indem Sie die Argumente von SUMMEWENNS im folgenden Format erweitern:=SUMMEWENNS(Summe_Bereich; Kriterienbereich1; Kriterium1; [Kriterienbereich2; Kriterium2]; [Kriterienbereich3; Kriterium3]; …). Achten Sie stets darauf, dass Ihre Bereiche korrekt ausgerichtet sind, um die gewünschten Ergebnisse zu erhalten.
Achtung: Wenn Sie nach der Einrichtung Ihrer Formeln Zeilen verschieben, einfügen oder löschen, prüfen Sie stets, ob alle Bezüge weiterhin korrekt mit Ihrer Datenstruktur übereinstimmen – Fehler entstehen häufig durch falsch ausgerichtete Bereiche oder übersehene Aktualisierungen der Kriterienzellen.
Sichtbare Zellen nur anhand von Kriterien mit einer Formel summieren
Wenn Sie eine formelbasierte Lösung ohne Hilfsspalten bevorzugen, können Sie eine Kombination aus SUMMENPRODUKT, TEILERGEBNIS, BEREICH.VERSCHIEBEN, ZEILE und der MIN-Funktion nutzen, um sichtbare Zellen gezielt nach bestimmten Kriterien zu summieren. Diese Methode ist ideal für erfahrene Excel-Nutzer, die mit Matrixformeln vertraut sind, und bewahrt die Übersichtlichkeit Ihres Arbeitsblatts – ganz ohne zusätzliche Spalten.
Vorteile: Keine zusätzlichen Spalten erforderlich – flexibel, dynamisch und aktualisiert sich sofort beim Filtern oder Ändern der Kriterien.
Einschränkungen: Formeln können schwer lesbar und aufwendig beim Fehlersuchen sein – besonders für Personen, die mit Matrixfunktionen nicht vertraut sind. Bei sehr großen Tabellen kann zudem die Leistung beeinträchtigt werden.
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein (z. B. zur Summierung der sichtbaren „Hoodie“-Zellen in A2:A12, wobei die tatsächlichen Werte in D2:D12 stehen und das Kriterium in A17 angegeben ist):
Drücken Sie nach der Eingabe der Formel Enter, um das gewünschte Ergebnis zu erhalten, wie unten dargestellt:
Achtung: Diese Methode reagiert empfindlich auf die angegebenen Bereiche – inkonsistente oder überlappende Bereiche können zu Fehlern oder unerwarteten Ergebnissen führen. Testen Sie unbedingt Randfälle, insbesondere wenn Filter die Anzahl oder Position sichtbarer Zeilen verändern.
Sichtbare Zellen nur anhand von Kriterien mithilfe von VBA-Code summieren
Für fortgeschrittene Anwender bietet VBA eine flexible Lösung, um sichtbare Zellen gezielt nach bestimmten Kriterien zu summieren – ideal bei komplexen Szenarien oder großen Datensätzen, bei denen Standardformeln aufgrund von Leistungseinbußen an ihre Grenzen stoßen oder mehrstufige Kriterienlogik sich kaum in einer einzigen Formel abbilden lässt. Mit VBA können Sie jede sichtbare Zeile durchlaufen, Ihre Bedingungen prüfen und die Summe effizient berechnen – perfekt geeignet für wiederkehrende Reporting-Aufgaben oder automatisierte Zusammenfassungen.
Vorteile: Verarbeitet problemlos große Datensätze, mehrere oder dynamische Kriterien sowie komplexe Logik; überzeugt durch schnelle Ausführung – selbst bei Tausenden von Zeilen; reduziert das Risiko von Fehlern durch manuelle Formelanpassungen.
Einschränkungen: Erfordert die Aktivierung von Makros – einige Benutzer sind mit VBA nicht vertraut oder verfügen nicht über ausreichende Berechtigungen. Änderungen erfordern Zugriff auf den Makro-Editor. Erstellen Sie stets eine Sicherungskopie, bevor Sie VBA auf wichtigen Datensätzen ausführen.
1. Öffnen Sie zunächst den VBA-Editor, indem Sie auf Entwicklertools > Visual Basic klicken. Wählen Sie im erscheinenden Fenster Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modul ein:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Klicken Sie auf die 
„Ausführen“-Schaltfläche (oder drücken Sie)F5), um den Code auszuführen. Anschließend fordert Sie ein Dialog auf, sowohl den Kriterienbereich (z. B. Ihre Produktnamen) als auch den zu summierenden Wertebereich und den gewünschten Filterwert (z. B. „Hoodie“) auszuwählen. Das Makro summiert ausschließlich die sichtbaren Zeilen, die Ihr Kriterium erfüllen, und zeigt das Ergebnis in einer Meldungsbox an.
Praktische Tipps: Verwenden Sie diesen VBA-Code, wenn Sie Ihre Summen häufig neu berechnen müssen – etwa nachdem Sie Daten oder Filter geändert haben. Erweitern Sie den Code ganz einfach, um mehrere Kriterien zu unterstützen: Fügen Sie dazu weitere Eingabeaufforderungen oder logische Bedingungen hinzu.
Fehlerbehebung: Stellen Sie stets sicher, dass die von Ihnen ausgewählten Kriterien- und Wertebereiche dieselbe Anzahl an Zeilen aufweisen und denselben Spalten Ihres gefilterten Datensatzes entsprechen. Sollte der Code einen Fehler melden oder nicht das erwartete Ergebnis liefern, überprüfen Sie Ihre Filtereinstellungen und die aktive Auswahl.
Zusammenfassende Empfehlungen: Wenn Sie wiederholt sichtbare Zellen für Ihre Datenanalyse summieren müssen, beschleunigt das Speichern dieses Makros in Ihrer persönlichen Makroarbeitsmappe Ihr tägliches Reporting. Sollte kein Dialog erscheinen, überprüfen Sie Ihre Makroeinstellungen und Sicherheitsberechtigungen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins