ZÄHLENWENNS und MITTELWERTWENNS in Excel: zählen und mitteln mit mehreren Kriterien

Wenn du SUMMEWENNS schon kennst, bist du zwei Drittel des Weges zu ZÄHLENWENNS und MITTELWERTWENNS, sie sind Geschwister und sprechen genau dieselbe Kriteriensprache. SUMMEWENNS summiert die Zeilen, die deine Bedingungen erfüllen. ZÄHLENWENNS zählt sie. MITTELWERTWENNS mittelt sie. Lerne die gemeinsame Grammatik einmal, und alle drei fallen an ihren Platz.

Dieser Leitfaden führt dich von einer einzelnen Bedingung zu mehreren gleichzeitig, zeigt dir die eine Reihenfolge-Falle, die fast jeden Einsteiger erwischt, und endet mit einer wirklich eleganten Idee: Ein Mittelwert ist nur eine Summe, aufgeteilt auf eine Anzahl, deshalb ist MITTELWERTWENNS in Wahrheit SUMMEWENNS ÷ ZÄHLENWENNS in einer Funktion. Wir bauen alles auf einem kleinen, einprägsamen Datensatz auf, sodass du jede Formel selbst nachbauen kannst.

FAQ — Welche Fragen beantwortet dieser Artikel?

Q: Was ist der Unterschied zwischen ZÄHLENWENNS und MITTELWERTWENNS?

A: ZÄHLENWENNS zählt, wie viele Zeilen deine Bedingungen erfüllen, es gibt eine ganze Zahl zurück und braucht keine Wertespalte. MITTELWERTWENNS mittelt die Zahlen in einer gewählten Spalte für die Zeilen, die deine Bedingungen erfüllen. Gleiche Kriterien, andere Frage: „wie viele?“ gegen „wie hoch ist der Schnitt?“.

Q: Was ist der Unterschied zwischen ZÄHLENWENN und ZÄHLENWENNS (bzw. MITTELWERTWENN und MITTELWERTWENNS)?

A: Die Einzahl-Varianten (ZÄHLENWENN, MITTELWERTWENN) erlauben nur eine Bedingung. Die Mehrzahl-Varianten mit „S“ am Ende erlauben viele Bedingungen gleichzeitig, verknüpft mit UND. Da die Mehrzahl-Varianten alles können, was die Einzahl kann, nehmen die meisten einfach immer ZÄHLENWENNS und MITTELWERTWENNS.

Q: Wie zähle oder mittele ich mit mehr als einer Bedingung?

A: Füge weitere Kriterienpaare hinzu. =ZÄHLENWENNS(Bereich1; Kriterium1; Bereich2; Kriterium2; ...). Jedes Paar muss erfüllt sein, damit eine Zeile zählt, das UND ist eingebaut. MITTELWERTWENNS funktioniert genauso, nur ist sein erstes Argument die Spalte der Zahlen, die gemittelt werden soll.

Q: Warum gibt MITTELWERTWENNS einen #DIV/0!-Fehler?

A: Weil keine Zeile deine Kriterien erfüllt hat, es also nichts zu mitteln gibt und das Teilen durch null Zeilen ist nicht definiert. ZÄHLENWENNS würde in derselben Lage einfach 0 zurückgeben. Umschließe MITTELWERTWENNS mit WENNFEHLER, um stattdessen eine freundliche Meldung zu zeigen.

Q: Kann ich Größer-als, Platzhalter oder den Wert einer Zelle als Kriterium nutzen?

A: Ja. Kriterien schreibst du als Text: ">50", "<>Berlin", "M*" (Platzhalter) oder ">="&C1, um einen Wert aus einer Zelle zu holen. Dieselben Kriterien funktionieren in ZÄHLENWENNS, MITTELWERTWENNS und SUMMEWENNS.

Q: Soll ich diese Funktionen oder eine PivotTable nehmen?

A: Für eine Handvoll bestimmter Kennzahlen auf einem Dashboard sind diese Funktionen perfekt und aktualisieren sich live. Zum interaktiven Erkunden vieler Gruppierungen ist eine PivotTable schneller. Sie ergänzen sich.

Eine Kriteriensprache, drei Aufgaben

ZÄHLENWENNS, SUMMEWENNS und MITTELWERTWENNS sind eine Familie. Gib allen drei dieselben Bedingungen, und sie wählen dieselben Zeilen aus, sie melden nur eine andere Zahl darüber.

Anzahl, Summe, Schnitt. Beachte schon jetzt, dass die drei Berlin-Zahlen zusammenhängen — 228 ÷ 4 = 57 — und merk dir das, denn das ist die schöne Idee, mit der wir enden.

Der Datensatz: ein 10-km-Wochenendlauf

Alles unten nutzt eine kleine Ergebnistabelle. Spalten A bis D, Daten in den Zeilen 2 bis 8:

Zwei Textspalten zum Gruppieren, eine Zahlenspalte (Zielzeit in Minuten) zum Zählen und Mitteln. Perfekt.

ZÄHLENWENNS: Zeilen zählen, die Bedingungen erfüllen

Die Syntax

=ZÄHLENWENNS(Kriterienbereich1; Kriterium1; [Kriterienbereich2; Kriterium2]; ...)

Du gibst Paare: einen Bereich, in dem gesucht wird, und das Kriterium, das er erfüllen muss. Es gibt keine Wertespalte, ZÄHLENWENNS zählt immer nur Zeilen. Jeder Bereich muss gleich hoch sein.

Eine Bedingung

Wie viele Läufer kamen aus Berlin?

=ZÄHLENWENNS(B2:B8; "Berlin")

Excel läuft die Stadt-Spalte ab und zählt die Treffer: Anna, Cara, Eva, Gwen, das sind 4.

Zwei Bedingungen: das UND ist automatisch

Wie viele Läufer waren aus Berlin und in der Kategorie Senioren?

=ZÄHLENWENNS(B2:B8; "Berlin"; C2:C8; "Senioren")

Stell dir jedes Kriterium als ein Tor vor. Eine Zeile wird nur gezählt, wenn sie jedes Tor passiert, das ist die Bedeutung von „UND“ hier. Cara und Gwen sind die einzigen beiden, die sowohl in Berlin als auch bei den Senioren sind, also lautet die Antwort 2. Füge ein drittes Paar für ein drittes Tor hinzu, und so weiter.

Zahlen, nicht nur Text

Kriterien können Zahlen vergleichen. Wie viele Läufer waren unter 50 Minuten im Ziel?

=ZÄHLENWENNS(D2:D8; "<50")

Dan (49) und Finn (47), das sind 2. Der Vergleich steht innerhalb der Anführungszeichen: "<50", nicht <50.

Zwischen zwei Zahlen: derselbe Bereich zweimal

Hier eine kleine Eleganz: Um Werte in einem Intervall zu zählen, nimm denselben Bereich zweimal, einmal mit einer Untergrenze und einmal mit einer Obergrenze. Wie viele Läufer waren zwischen 50 und 60 Minuten (jeweils einschließlich) im Ziel?

=ZÄHLENWENNS(D2:D8; ">=50"; D2:D8; "<=60")

Anna (52), Cara (58) und Eva (55) qualifizieren sich, 3. ZÄHLENWENNS hat kein Problem damit, dieselbe Spalte gegen zwei Bedingungen zu prüfen.

Ein Platzhalter

Textkriterien können * (beliebig viele Zeichen) und ? (genau ein Zeichen) verwenden. Wie viele Läufer kamen aus einer Stadt, die mit „M“ beginnt?

=ZÄHLENWENNS(B2:B8; "M*")

München taucht zweimal auf, 2.

MITTELWERTWENNS: die passenden Zeilen mitteln

MITTELWERTWENNS beantwortet „Was ist der Schnitt dieser Zahlen für die Zeilen, die meine Bedingungen erfüllen?“.

Die Syntax: achte auf die Reihenfolge

=MITTELWERTWENNS(Mittelwert_Bereich; Kriterienbereich1; Kriterium1; [Kriterienbereich2; Kriterium2]; ...)

Lies das erste Argument genau, denn genau hier rutscht fast jeder aus.

Die Mehrzahl-Funktionen mit „S“ setzen den auszuwertenden Bereich zuerst: SUMMEWENNS beginnt mit dem Summenbereich, MITTELWERTWENNS mit dem Mittelwertbereich, und dann kommen die Kriterienpaare. (ZÄHLENWENNS ist nur deshalb die Ausnahme, weil es überhaupt keinen Wertebereich hat.) Die Falle: Die Einzahl-Cousins machen es umgekehrt, MITTELWERTWENN(Bereich; Kriterien; [Mittelwert_Bereich]) setzt diesen Bereich ans Ende. Gleiche Idee, umgekehrte Reihenfolge. Wenn sich ein MITTELWERTWENNS nicht benimmt, prüfe zuerst das.

Eine Bedingung

Durchschnittliche Zielzeit der Berliner Läufer:

=MITTELWERTWENNS(D2:D8; B2:B8; "Berlin")

Die vier Berliner Zeiten sind 52, 58, 55 und 63, was im Schnitt 57 Minuten ergibt.

Zwei Bedingungen

Durchschnittliche Zielzeit der Berliner Senioren:

=MITTELWERTWENNS(D2:D8; B2:B8; "Berlin"; C2:C8; "Senioren")

Nur Cara (58) und Gwen (63): ein Schnitt von 60,5 Minuten. Wie bei ZÄHLENWENNS muss jedes Kriterium erfüllt sein, dasselbe eingebaute UND.

Der elegante Teil: MITTELWERTWENNS = SUMMEWENNS ÷ ZÄHLENWENNS

Ein Mittelwert ist nichts Geheimnisvolles, er ist eine Summe, aufgeteilt auf eine Anzahl. Den Berliner Schnitt könntest du also von Hand aus seinen zwei Geschwistern bauen:

=SUMMEWENNS(D2:D8; B2:B8; "Berlin") / ZÄHLENWENNS(B2:B8; "Berlin")

Das ist 228 / 4 = 57, genau das, was MITTELWERTWENNS zurückgegeben hat. MITTELWERTWENNS erledigt beide Schritte für dich in einer Zelle.

Dieses Bild erklärt auch den verwirrendsten Fehler von MITTELWERTWENNS. Passt keine Zeile, ist die Anzahl darunter 0, und du kannst eine Summe nicht auf null Zeilen aufteilen. Also gibt MITTELWERTWENNS #DIV/0! zurück, während ZÄHLENWENNS, das nichts zu teilen hat, ruhig 0 liefert. Dieser Unterschied ist kein Fehler; er folgt direkt daraus, was ein Mittelwert ist.

Gegen #DIV/0! absichern

Wenn ein Kriterium nichts treffen könnte (etwa eine Stadt ohne Läufer), umschließe die Formel so, dass sie sauber scheitert:

=WENNFEHLER(MITTELWERTWENNS(D2:D8; B2:B8; "Köln"); "keine Läufer")

Jetzt zeigt ein leeres Ergebnis „keine Läufer“ statt eines Fehlers. (Mehr zum Aufräumen von Fehlern findest du im unten verlinkten WENNFEHLER-/WENNNV-Leitfaden.)

Dein Kriterien-Kochbuch

Jedes Kriterium unten funktioniert identisch in ZÄHLENWENNS, MITTELWERTWENNS und SUMMEWENNS. Halte diese Karte griffbereit.

Zwei Details lohnen sich zu merken. Erstens steht der Vergleich oder Platzhalter immer innerhalb der Anführungszeichen: ">50", "M*". Zweitens verbindest du den Operator mit einem & mit dem Zellbezug, um gegen einen Wert in einer Zelle zu vergleichen: ">="&C1. Dieser Kniff lässt ein Kriterium einem Dropdown oder einer Eingabezelle folgen, ohne die Formel zu ändern, praktisch für interaktive Dashboards, und er beruht auf derselben Idee des Zusammensetzens von Text wie im Leitfaden zu Zellbezügen.

Häufige Fehler und wie du sie behebst

Bereiche unterschiedlicher Höhe. Jeder Kriterienbereich und der Mittelwert_Bereich müssen exakt dieselben Zeilen umfassen. Ungleiche Höhen ergeben einen #WERT!-Fehler. Eine Excel-Tabelle (siehe unten) beseitigt das Problem vollständig.

Operatoren außerhalb der Anführungszeichen. ">50" ist ein Textkriterium; ein nacktes >50 funktioniert nicht. Halte die ganze Bedingung innerhalb eines Paars Anführungszeichen.

Den Wertebereich in MITTELWERTWENNS vergessen. Wenn MITTELWERTWENNS etwas Rätselhaftes zurückgibt, hast du wahrscheinlich mit einem Kriterienbereich statt mit dem Mittelwertbereich begonnen. Der zu mittelnde Bereich kommt zuerst.

Erwarten, dass MITTELWERTWENNS Text ignoriert. Es mittelt nur die passenden Zahlen, aber der Mittelwert_Bereich sollte Zahlen enthalten; leere Zellen werden übersprungen, Textzellen können überraschen. Halte diese Spalte sauber.

Aus Versehen ein ODER bauen. Kriterienpaare sind immer UND. Um „Berlin oder München“ zu zählen, addiere zwei ZÄHLENWENNS, oder greif zu SUMMENPRODUKT (unten verlinkt), das ODER in einer einzigen Zelle ausdrücken kann.

Robust mit Excel-Tabellen

Fest verdrahtete Bereiche wie B2:B8 veralten, sobald ein neuer Läufer hinzukommt. Lege die Daten in eine Tabelle (markieren und Strg+T drücken) und verwende ihre Spaltennamen:

=ZÄHLENWENNS(Lauf[Stadt]; "Berlin"; Lauf[Kategorie]; "Senioren")
=MITTELWERTWENNS(Lauf[Minuten]; Lauf[Stadt]; "Berlin")

Jetzt bedeutet Lauf[Stadt] immer die ganze Stadt-Spalte, egal auf wie viele Zeilen die Tabelle wächst. Neue Ergebnisse werden automatisch einbezogen, und die Bereiche können nie aus dem Takt geraten.

Wann du was verwendest

Nimm ZÄHLENWENNS, wenn die Frage lautet „wie viele Zeilen passen?“. Nimm MITTELWERTWENNS für „Was ist der Schnitt einer Zahl für die passenden Zeilen?“. Nimm SUMMEWENNS für die Summe. Alle drei teilen eine Kriteriensprache, sobald du eine schreiben kannst, kannst du alle drei.

Greif über sie hinaus zu SUMMENPRODUKT, wenn du ODER-Logik oder eine Bedingung brauchst, die auf einer Berechnung beruht, in einer einzigen Zelle. Und greif zu einer PivotTable, wenn du viele Gruppierungen gleichzeitig erkunden willst, statt ein paar bestimmte Kennzahlen festzunageln.

Zusammenfassung

Lerne die Kriteriensprache einmal, und drei Funktionen kommen gratis dazu. ZÄHLENWENNS zählt, SUMMEWENNS summiert, MITTELWERTWENNS mittelt und die letzte ist immer nur die ersten beiden, still geteilt.

Was du als Nächstes lesen solltest

Falls du das Arbeitspferd der Familie noch nicht kennst, beginne mit SUMMEWENNS. Der Fehler-Leitfaden behandelt die WENNFEHLER-Hülle, die #DIV/0! zähmt. Der Tabellen-Leitfaden bewahrt deine Bereiche vor dem Veralten, und der SUMMENPRODUKT-Leitfaden ist der Ort, an den du gehst, wenn du ODER-Logik oder eine berechnete Bedingung brauchst, die ZÄHLENWENNS nicht ausdrücken kann.

Siehe: So verwendest du SUMMEWENNS, um mit mehreren Kriterien zu summieren

Siehe: Umgang mit Fehlermeldungen: WENNFEHLER und WENNNV

Siehe: Excel-Tabellen: warum jeder Datensatz eine sein sollte

Siehe: SUMMENPRODUKT: multiplizieren, summieren und mit Bedingungen zählen