Nach Kriterien mit AVERAGEIF mitteln

Sie haben mit SUMIF summiert und mit COUNTIF gezählt. Das dritte Mitglied der Familie ist AVERAGEIF. Damit ermitteln Sie den Mittelwert nur der Werte, die eine Bedingung erfüllen.

Statt alle Verkäufe zu mitteln, können Sie beispielsweise nur die Verkäufe der Region Ost oder nur die Punktzahlen über 50 mitteln. So verbinden Sie Filterung und Mittelwertbildung in einem übersichtlichen Schritt.

AVERAGEIF verwendet dieselbe dreiteilige Struktur wie SUMIF:

• range die zu prüfenden Zellen
• criteria die Bedingung
• average_range die zu mittelnden Zellen

Die Funktion durchsucht range, findet die passenden Werte und bildet anschließend den Mittelwert der entsprechenden Zellen in average_range.

Bei Regionen in A2:A10 und Beträgen in B2:B10 prüft die Berechnung des durchschnittlichen Verkaufs für die Region Ost Spalte A auf Ost und mittelt die passenden Werte in Spalte B.

Im Hintergrund wird dabei eine SUMIF durch eine COUNTIF geteilt. AVERAGEIF fasst diesen Vorgang jedoch in einer einzigen gut lesbaren Formel zusammen.

Wenn die zu prüfende Spalte auch die Spalte ist, deren Werte Sie mitteln möchten, können Sie das dritte Argument weglassen. Um nur die Punktzahlen in B2:B10 zu mitteln, die über 70 liegen:

Hier wird Spalte B sowohl geprüft als auch gemittelt. Wenn Sie average_range weglassen, verwendet die Tabellenkalkulation range für beide Zwecke.

Wie bei den anderen IF-Funktionen sollten Sie die Bedingung für mehr Flexibilität in einer Zelle speichern. Wenn D1 einen Regionsnamen enthält, verweisen Sie beim Kriterium auf D1.

Nun steuert eine einzige Zelle den Mittelwert. Wenn Sie D1 von Ost in West ändern, wird der Wert sofort neu berechnet – ideal für eine Übersichtstabelle oder eine Dashboard-Auswahl.

AVERAGEIF akzeptiert wie die verwandten Funktionen Operatoren innerhalb von Anführungszeichen. Um nur Verkäufe von mindestens 100 zu mitteln:

• ">=100" 100 oder mehr
• "<50" unter 50
• "<>0" ohne Nullen

Die letzte Variante ist besonders beliebt, weil sie beim Mitteln Nulleinträge ignoriert, die den Mittelwert sonst nach unten ziehen würden.

Um einen Schwellenwert aus einer Zelle zu verwenden, verbinden Sie den Operator mit einem kaufmännischen Und mit dieser Zelle. Wenn D1 einen Grenzwert enthält, mitteln Sie alle Werte darüber.

Mit & wird der Bedingungstext aus ">" und dem Wert in D1 erstellt. Dies ist derselbe Verbindungstrick wie bei SUMIF und COUNTIF.

AVERAGEIF hat eine Besonderheit, die die anderen Funktionen nicht haben. Wenn keine Zeilen übereinstimmen, gibt es nichts, durch das geteilt werden kann, und Sie erhalten den Fehler #DIV/0!.

Wenn Sie beispielsweise den Mittelwert einer Region bilden, die in den Daten nicht vorkommt, wird dieser Fehler zurückgegeben. Die Tabellenkalkulation teilt Ihnen damit mit, dass der Filter keine Zellen gefunden hat – nicht, dass Ihre Formel fehlerhaft ist.

Betten Sie die Formel in IFERROR ein, um bei fehlenden Übereinstimmungen eine verständliche Meldung statt #DIV/0! anzuzeigen.

Nun zeigt eine leere Region den Text Keine Daten statt eines alarmierenden Fehlers an. So bleiben Berichte übersichtlich, auch wenn einige Kategorien fehlen.

AVERAGEIF mittelt unter den passenden Zellen nur diejenigen, die Zahlen enthalten. Tatsächlich leere Zellen in average_range werden ignoriert und daher nicht als Null gezählt.

Das ist wichtig: Ein fehlender Wert zieht den Mittelwert nicht so in Richtung null wie eine tatsächliche 0. Wenn auch Nullen ausgeschlossen werden sollen, fügen Sie für die Wertespalte das Kriterium "<>0" hinzu.

Erstellen Sie eine übersichtliche Zusammenfassung: Listen Sie jede Kategorie in den Zellen D2, D3 und D4 auf. Schreiben Sie anschließend eine AVERAGEIF-Formel, die auf die Beschriftungszelle verweist, und füllen Sie sie nach unten aus.

Nun zeigt jede Zeile den Mittelwert der jeweiligen Kategorie – neben der SUMIF-Summe und der COUNTIF-Anzahl aus den vorherigen Lektionen. Zusammen bilden sie einen kompakten, vollständig formelbasierten Bericht.

Testen Sie Ihr Verständnis des Verhaltens von AVERAGEIF.

Sie können jetzt mit AVERAGEIF bedingt Mittelwerte bilden. Die wichtigsten Punkte:

• Die Reihenfolge ist range, criteria, average_range. Lassen Sie das letzte Argument weg, um den Testbereich selbst zu mitteln.
• Die Funktion unterstützt Text, Zahlen und Operatoren wie ">=100".
• Keine Übereinstimmungen führen zum Fehler #DIV/0!. Fangen Sie ihn daher mit IFERROR ab.
• Leere Zellen werden übersprungen und nicht als null behandelt.

Als Nächstes suchen Sie mit Platzhaltern nach Teiltexten.