Wie zählt oder summiert man Zellen in Excel basierend auf einem Filter mit bestimmten Kriterien?
Bei der täglichen Datenanalyse-Nutzung von Excel ist es üblich, dass Sie Gesamtsummen oder Anzahlen benötigen, die nur gefilterte Zeilen berücksichtigen – insbesondere bei langen Listen oder Berichten, bei denen Sie sich auf bestimmte Datenabschnitte konzentrieren müssen. Die grundlegenden Excel-Funktionen wie COUNTA und SUM sind für nicht gefilterte Bereiche einfach anzuwenden, doch sobald Sie Filter anwenden – beispielsweise bestimmte Zeilen ausblenden oder die Ansicht nach Kriterien eingrenzen – können diese Standardfunktionen ausgeblendete Zeilen in ihre Ergebnisse einbeziehen, was zu ungenauen Summen oder Anzahlen führt. Um zuverlässig Summen und Anzahlen zu berechnen, die Ihre Filter – einschließlich Bedingungen oder Kriterien – respektieren, bietet dieses Tutorial eine Reihe praktischer Lösungen für unterschiedliche Szenarien und Kenntnisstände.
Zählen / Summieren von Zellen basierend auf einem Filter mithilfe von Formeln
Zählen / Summieren von Zellen basierend auf einem Filter mit Kutools für Excel
Zählen / Summieren von Zellen basierend auf einem Filter mithilfe von Formeln
Excel bietet die TEILERGEBNIS-Funktion (SUBTOTAL) für gefilterte Daten, mit der Sie sichtbare Zellen präzise zählen oder summieren – während ausgeblendete Zellen automatisch ignoriert werden. Das ist besonders wertvoll bei Datensätzen, die nach bestimmten Werten oder Bedingungen gefiltert wurden. Dank TEILERGEBNIS passen sich Ihre Berechnungen sofort an, sobald sich die Filterbedingungen ändern, und garantieren so stets die Genauigkeit Ihrer Analyse.
Um die Zellen in einem Filterbereich zu zählen, geben Sie folgende Formel in eine Zelle ein – beispielsweise D1 –, in der das Ergebnis angezeigt werden soll:
=SUBTOTAL(3, C6:C19)Hierbei ist C6:C19 der gefilterte Datenbereich, den Sie zählen möchten. Nach Eingabe der Formel drücken Sie Enter, und es wird die Anzahl der nur sichtbaren (gefilterten) Zellen in diesem Bereich angezeigt.
Um Werte in einem Filterbereich zu summieren, geben Sie folgende Formel ein (z. B. in Zelle D2):
=SUBTOTAL(9, C6:C19)Diese summiert nur die sichtbaren Zellen nach dem Filtern. Drücken Sie Enter, um die Gesamtsumme anzuzeigen.
Tipps: Die erste Zahl in der TEILERGEBNIS-Funktion ist der Funktionsnummer-Parameter, der den Berechnungstyp festlegt: 3 steht für ANZAHL2 (zählt nicht leere Werte) und 9 für SUMME. Stellen Sie sicher, dass Ihr Filter aktiviert und korrekt eingestellt ist, bevor Sie sich auf diese Ergebnisse verlassen. Sollte sich der Datenbereich ändern, passen Sie die Zellbezüge entsprechend an. Die Formel wird automatisch neu berechnet, sobald Sie den Filter ändern oder aufheben.
Zählen / Summieren von Zellen basierend auf einem Filter mit Kutools für Excel
Mit Kutools für Excel können Benutzer spezialisierte Funktionen nutzen – COUNTVISIBLE und SUMVISIBLE –, um sofort Anzahl- und Summenergebnisse basierend ausschließlich auf sichtbaren (d. h. gefilterten und nicht ausgeblendeten) Zellen anzuzeigen und so die Einschränkungen herkömmlicher Excel-Formeln zu umgehen. Das ist besonders praktisch bei häufigen Filteranalysen, spart Zeit und reduziert manuelle Fehler.
Nach der Installation von Kutools für Excel geben Sie die folgenden Formeln in Ihr Arbeitsblatt ein, um Ergebnisse aus gefilterten Zellen zu berechnen (z. B. in D1 oder D2):
Um gefilterte Zellen zu zählen, verwenden Sie:
=COUNTVISIBLE(C6:C19)Um die sichtbaren, gefilterten Zellen zu summieren, verwenden Sie:
=SUMVISIBLE(C6:C19)
Tipp: Diese Funktionen arbeiten sowohl mit manuell ausgeblendeten als auch mit gefilterten Zeilen und stellen sicher, dass Ihre Berechnungen exakt das widerspiegeln, was angezeigt wird. Sie können diese Funktionen auch über das Kutools-Menü aufrufen: Klicken Sie auf Kutools > Erweiterte Funktionen > Statistik & Mathematik > MITTELWERTSICHTBAR / ANZAHL_SICHTBAR / SUMMESICHTBAR. So erhalten Sie mit nur einem Mausklick Zugriff auf nützliche Zusammenfassungsfunktionen für gefilterte Datensätze.
Hinweis: Wenn Sie den Filter ändern oder Zeilen ausblenden, aktualisieren die erweiterten Funktionen die Ergebnisse automatisch. Diese Formeln stehen erst nach der Installation von Kutools zur Verfügung und sind in Standard-Excel-Installationen nicht enthalten.
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Tools, um Ihre Arbeit schneller und einfacher zu erledigen, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen
Zählen / Summieren von Zellen basierend auf einem Filter mit bestimmten Kriterien durch Verwendung von Formeln
In der Praxis müssen Sie gefilterte Daten möglicherweise anhand einer zusätzlichen Bedingung zählen oder summieren – beispielsweise nur die Zeilen berücksichtigen, in denen ein bestimmter Name vorkommt. Während das Filtern Ihre Daten visuell eingrenzt, ermöglichen Formeln solche Berechnungen „on the fly“, ohne dass Sie Ihre Filter ständig anpassen müssen. Im Folgenden finden Sie nützliche Formeln für diese Szenarien.
Zählen von Zellen basierend auf gefilterten Daten mit bestimmten Kriterien:
Um sichtbare (gefilterte) Zellen zu zählen, die einer bestimmten Bedingung entsprechen – beispielsweise solche mit dem Namen „Nelly“ –, geben Sie folgende Formel in eine Zelle ein (z. B. D1):
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), --(B6:B19="Nelly"))Dabei ist B6:B19 der Datenbereich und „Nelly" Ihr Kriterium. Diese Formel zählt nur die sichtbaren Zeilen, die nach dem Filtern der angegebenen Bedingung entsprechen. Drücken Sie Enter, und die Zelle zeigt die Anzahl an.
Summieren von Zellen basierend auf gefilterten Daten mit bestimmten Kriterien:
Möchten Sie zusätzlich Elemente mit denselben Kriterien summieren, verwenden Sie diese erweiterte Formel (z. B. in Zelle D2):
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),(B6:B19="Nelly")*(C6:C19))In dieser Formel ist B6:B19 die Kriterienspalte, C6:C19 die Betragsspalte und „Nelly" das Kriterium. Die Formel liefert die Summe der entsprechenden Werte in C6:C19, bei denen die Bedingung erfüllt ist und die Zeilen sichtbar sind. Drücken Sie Enter, um die Summe zu bestätigen und anzuzeigen.
Tipp: Achten Sie beim Eingeben dieser Formeln darauf, dass Ihre Bereiche und Kriterien mit Ihren gefilterten Daten übereinstimmen. Die Formeln passen sich dynamisch an Filteränderungen an und zeigen stets aktualisierte Summen oder Anzahlen an. Für andere Kriterien ersetzen Sie einfach „Nelly“ entsprechend.
VBA-Code – Automatisches Zählen oder Summieren nur sichtbarer Zellen basierend auf Filter und Kriterien über ein benutzerdefiniertes Makro
Für Benutzer, die mit Makros vertraut sind, bietet VBA eine flexible Möglichkeit, ausschließlich sichtbare Zellen zu zählen oder zu summieren – optional auch unter Berücksichtigung bestimmter Kriterien. Das ist besonders nützlich, wenn sich Ihre Filter häufig ändern oder Sie solche Berechnungen automatisieren möchten. Im Gegensatz zu Formeln verarbeiten Makros große Datensätze schnell und lassen sich exakt an Ihre individuellen Anforderungen anpassen.
Anwendungsszenarien: Ideal für alle, die mit großen gefilterten Tabellen arbeiten und benutzerdefinierte Berechnungen benötigen, die von Standardformeln nicht direkt unterstützt werden. Zu den Vorteilen zählen Automatisierung und Vielseitigkeit; Nachteile können der anfängliche Einrichtungsaufwand sowie die Notwendigkeit sein, Makros zu aktivieren.
Vorsichtsmaßnahmen: Speichern Sie Ihre Arbeit stets, bevor Sie VBA-Skripte ausführen. Makros stehen nur in Desktopversionen von Excel zur Verfügung – nicht in Web- oder Mobilversionen.
1. Klicken Sie auf Entwicklertools > Visual Basic. Klicken Sie im geöffneten Fenster Microsoft Visual Basic für Applikationen auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modulfenster ein:
Sub SumOrCountVisibleCellsWithCriteria()
Dim CriteriaCol As Range
Dim DataCol As Range
Dim Criteria As String
Dim Total As Double
Dim Count As Long
Dim i As Integer
Dim LastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set CriteriaCol = Application.InputBox("Select criteria column (e.g. B6:B19)", xTitleId, Type:=8)
Set DataCol = Application.InputBox("Select data/sum column (for sum, e.g. C6:C19; same as criteria for count)", xTitleId, Type:=8)
Criteria = Application.InputBox("Enter criteria (e.g. Nelly)", xTitleId, "", Type:=2)
Total = 0
Count = 0
LastRow = CriteriaCol.Rows.Count
For i = 1 To LastRow
If Not CriteriaCol.Rows(i).EntireRow.Hidden Then
If CriteriaCol.Cells(i, 1).Value = Criteria Then
Total = Total + DataCol.Cells(i, 1).Value
Count = Count + 1
End If
End If
Next i
MsgBox "Sum: " & Total & vbCrLf & "Count: " & Count, vbInformation, xTitleId
End Sub2. Klicken Sie auf die Schaltfläche 
, um das Makro auszuführen. Es erscheint ein Dialogfeld, in dem Sie die Kriterienspalte sowie die Spalte für Summe oder Anzahl auswählen und Ihr gewünschtes Kriterium (z. B. einen Namen) eingeben können. Sobald Sie fertig sind, zeigt das Makro sowohl die Summe als auch die Anzahl der sichtbaren Zellen an, die Ihren Kriterien entsprechen.
Tipp: Dieses Makro kombiniert Zählen und Summieren innerhalb sichtbarer, gefilterter Daten. Passen Sie die Auswahl von CriteriaCol und DataCol je nach Ihren Analyseanforderungen an. Stellen Sie sicher, dass die ausgewählten Spalten stets mit Ihrer Filterkonfiguration übereinstimmen. Wenn Sie nur zählen – nicht summieren – möchten, wählen Sie für beide Eingaben denselben Bereich.
Fehlerbehebung: Sollten Sie einen Laufzeitfehler erhalten, stellen Sie sicher, dass Sie einen Bereich gleicher Größe auswählen und dass Ihre Kriterien exakt mit dem Text in den Zellen übereinstimmen. Bei großen Datensätzen kann die Leistung variieren – filtern Sie daher vor Ausführung des Makros auf wesentliche Zeilen.
PivotTable – Verwenden Sie PivotTable, um gefilterte Daten (einschließlich nach Kriterien) zusammenzufassen (zählen/summieren) und interaktive Filteroptionen zu nutzen
PivotTables sind ein vielseitiges und interaktives Excel-Werkzeug zur effizienten Zusammenfassung großer Datenmengen – inklusive gefilterter Ergebnisse. Sie ermöglichen müheloses Gruppieren, Zählen und Summieren nach Kriterien wie Name oder Kategorie, und integrierte Filter lassen Sie blitzschnell wechseln, welche Daten angezeigt werden.
Anwendungsszenarien: Ideal für Situationen, in denen Sie dynamische Zusammenfassungen benötigen, flexible Aggregationen nach verschiedenen Feldern durchführen möchten oder Ergebnisse interaktiv erkunden wollen, indem Sie Kriterien anpassen. Zu den Vorteilen zählen eine benutzerfreundliche Bedienung und sofortige Neuberechnung dank Drag-and-Drop-Funktionalität.
Anleitung zur Verwendung:
1. Wählen Sie Ihren gefilterten Datenbereich aus, und achten Sie darauf, dass er alle Spalten enthält, die Sie analysieren möchten – einschließlich der Überschriften.
2. Gehen Sie zu Einfügen > PivotTable. Stellen Sie im Dialogfeld sicher, dass der Tabellenbereich korrekt ist, und wählen Sie aus, wo die PivotTable platziert werden soll (neues Arbeitsblatt oder vorhandenes Arbeitsblatt).
3. Ziehen Sie im Feldbereich der PivotTable das Kriterienfeld (z. B. „Name“) in den Zeilen-Bereich und das Ziel-Feld (z. B. „Bestellbetrag“) in den Werte-Bereich. Standardmäßig werden die Daten dabei als Summe zusammengefasst. Klicken Sie bei Bedarf auf das Feld und ändern Sie die Zusammenfassungsfunktion zu „Anzahl“ oder einer anderen Option.
4. Nutzen Sie die integrierten Filter-Dropdowns in der PivotTable, um gezielt bestimmte Elemente anzuzeigen (z. B. nach „Nelly“ zu filtern) oder mehrere Kriterien gleichzeitig anzuwenden – so fokussieren Sie sich ganz auf die relevanten Daten.
5. Ihre PivotTable wird sofort aktualisiert, um Summe und/oder Anzahl der sichtbaren Daten anzuzeigen. Sie können Felder neu anordnen, weitere Filter hinzufügen oder die PivotTable für bessere Lesbarkeit formatieren.
Tipp: Pivot-Tabellen reagieren nicht direkt auf Arbeitsblattfilter, sondern verfügen über eigene Filtersteuerelemente, die deutlich leistungsstärker und flexibler sind. Nutzen Sie für fortgeschrittene Analysen Slicer oder zusätzliche berechnete Felder – und aktualisieren Sie Ihre Pivot-Tabelle immer dann, wenn sich die Quelldaten ändern.
Fehlerbehebung: Falls die Ergebnisse nicht Ihren Erwartungen entsprechen, überprüfen Sie Ihre Feldauswahl und stellen Sie sicher, dass der Quellbereich alle relevanten Daten enthält. Wenn Ihre Daten keine klaren Überschriften aufweisen, fügen Sie diese ein, bevor Sie die PivotTable erstellen.
Zusammenfassende Empfehlungen: Jede Lösung in diesem Tutorial erfüllt ein konkretes Bedürfnis – von schnellen Formeln über Automatisierung bis hin zu interaktiver Analyse. Nutzen Sie SUBTOTAL oder erweiterte Funktionen für einfache Summen und Anzahlen gefilterter Daten, fortgeschrittene Formeln für kriterienbasierte Ergebnisse, Makros zur Automatisierung oder PivotTables für hochflexible Zusammenfassungen und tiefgehende Exploration. Achten Sie stets auf korrekte Zellbezüge und präzise Kriterien, um fehlerhafte Ergebnisse zu vermeiden. Für maximale Effizienz halten Sie Ihre Daten mit klaren Überschriften und einheitlicher Formatierung stets gut strukturiert.
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