MITTELWERTWENN & MITTELWERTWENNS – Durchschnitt nach Kriterien

Einen einfachen Durchschnitt hat Excel in Sekunden gebildet: ein paar Zahlen zusammenzählen, durch ihre Anzahl teilen, fertig. Im Alltag willst du den Schnitt über wirklich alles aber nur selten wissen. Spannender ist fast immer der Durchschnitt einer bestimmten Gruppe, also der mittlere Umsatz einer einzelnen Abteilung oder die Durchschnittsnote einer Klasse. Für genau diese Fälle gibt es MITTELWERTWENN und seinen großen Bruder MITTELWERTWENNS. Beide bilden den Mittelwert nur aus den Zeilen, die deine Bedingungen erfüllen, und nehmen dir so das mühsame Vorsortieren der Daten ab.

So funktioniert MITTELWERTWENN

Die Funktion erwartet bis zu drei Angaben: =MITTELWERTWENN(Bereich; Kriterien; [Mittelwert_Bereich]). Der erste Bereich ist die Spalte, in der Excel nach deinem Kriterium sucht. An zweiter Stelle steht das Kriterium selbst, etwa ein Text wie “Vertrieb”, eine Zahl oder ein Vergleich wie “>1000”. Der dritte Bereich ist freiwillig. Lässt du ihn weg, mittelt Excel die Werte aus dem ersten Bereich; gibst du ihn an, holt es sich die Zahlen aus einer anderen Spalte.

Ein Beispiel: =MITTELWERTWENN(A2:A50;"Vertrieb";B2:B50) liefert den durchschnittlichen Umsatz aller Zeilen, in denen in Spalte A das Wort “Vertrieb” steht.

Soll der Durchschnitt nur für Beträge über 1.000 Euro gelten, genügt =MITTELWERTWENN(B2:B50;">1000"). Hier fehlt der dritte Bereich, also rechnet Excel direkt mit den Zahlen in Spalte B. Vergleichsoperatoren wie >, <, >= oder <> gehören dabei immer in Anführungszeichen.

MITTELWERTWENNS, wenn mehrere Bedingungen zählen

Sobald zwei oder mehr Kriterien ins Spiel kommen, übernimmt MITTELWERTWENNS. Dabei dreht sich die Reihenfolge um, und genau das ist der häufigste Grund für Fehler: =MITTELWERTWENNS(Mittelwert_Bereich; Kriterienbereich1; Kriterien1; Kriterienbereich2; Kriterien2; ...). Anders als bei MITTELWERTWENN steht die Spalte mit den zu mittelnden Zahlen jetzt ganz vorne. Danach folgen die Paare aus Suchbereich und Bedingung, so viele, wie du brauchst.

Willst du den mittleren Umsatz der Abteilung Vertrieb im Jahr 2024 wissen, sieht das so aus: =MITTELWERTWENNS(B2:B50;A2:A50;"Vertrieb";C2:C50;2024). Excel zählt dann nur die Zeilen, in denen beide Bedingungen zugleich stimmen.

Jedes weitere Kriterium hängst du als neues Paar hinten an. Wichtig ist dabei nur, dass alle Bereiche gleich groß sind, sonst quittiert Excel das mit einem Fehler.

Richtig praktisch wird es, wenn das Kriterium nicht fest in der Formel steht, sondern aus einer Zelle kommt. Steht in E1 der gesuchte Abteilungsname, reicht =MITTELWERTWENNS(B2:B50;A2:A50;E1). Tippst du in E1 etwas Neues, etwa über ein Dropdown, aktualisiert sich das Ergebnis von selbst. Bei Vergleichen kombinierst du Operator und Bezug mit dem kaufmännischen Und, also zum Beispiel ">="&E1 für alle Werte ab dem Inhalt von E1.

Beispiel mit echten Zahlen

Damit die Argument-Reihenfolge greifbar wird, hilft eine kleine Beispieltabelle. In Spalte A steht die Abteilung, in Spalte B der Umsatz und in Spalte C das Jahr.

A: Abteilung, B: Umsatz, C: Jahr • Datenbereich A2:C7

Excel sucht in Spalte A nach „Vertrieb“ und mittelt die zugehörigen Umsätze aus Spalte B. Aus 1000, 1400, 1200 und 2000 wird im Schnitt 1400.

Ohne dritten Bereich rechnet Excel direkt mit Spalte B. Es zählen nur die vier Werte über 1000, also 1400, 1200, 1200 und 2000.

Jetzt müssen beide Bedingungen stimmen: Abteilung „Vertrieb“ und Jahr 2024. Übrig bleiben die Zeilen 2 und 3 mit 1000 und 1400.

Steht in E1 das Wort „Marketing“, mittelt die Formel nur die Marketing-Zeilen 4 und 6 mit 1200 und 800. Ein neuer Eintrag in E1 aktualisiert das Ergebnis sofort.

Häufige Fehler und die Abgrenzung zu SUMMEWENN

Der Klassiker ist die schon erwähnte Reihenfolge. Wer MITTELWERTWENNS wie MITTELWERTWENN aufbaut und den Mittelwert-Bereich nach hinten setzt, bekommt entweder ein falsches Ergebnis oder die Meldung #WERT!. Hier hilft eine einfache Eselsbrücke: Bei der Mehrzahl-Variante kommt das Wichtigste, nämlich die zu mittelnde Spalte, zuerst.

Ein zweiter Stolperstein ist #DIV/0!. Diese Fehlermeldung erscheint, wenn keine einzige Zeile die Bedingung erfüllt, denn dann teilt Excel durch null.

Beruhigend ist dagegen, dass beide Funktionen leere Zellen und reinen Text im Mittelwert-Bereich von sich aus überspringen. Gemittelt werden nur echte Zahlen, sodass eine versehentliche Leerzeile das Ergebnis nicht verfälscht.

Inhaltlich sind die drei verwandten Funktionen schnell sortiert. SUMMEWENN addiert die passenden Werte, ZÄHLENWENN zählt sie, und MITTELWERTWENN macht im Grunde beides auf einmal: Es teilt die Summe der Treffer durch ihre Anzahl. Wer dieses Prinzip einmal verinnerlicht hat, wechselt mühelos zwischen den dreien hin und her.