Excel-TippSo können Sie in Excel trotz Fehlerwert summieren

Werte zum Summieren mit Fehlerwerten

Wollen Sie Zellen summieren, in denen teilweise Fehlerwerte (zum Beispiel #DIV/0!) vorkommen? Dann bekommen Sie als Ergebnis ebenfalls eine Fehlermeldung, wenn Sie die „normalen“ Wege zum Summieren in Excel wählen.

In der folgenden Abbildung sehen Sie einen Bereich von Werten, die summiert werden sollen. Da hier Fehlerwerte enthalten sind, bekommen Sie über die Funktion SUMME() ebenfalls einen Fehlerwert angezeigt.

Formel fürs Summieren mit Fehlerwerten

Wollen Sie einen solchen Bereich mit Daten trotzdem summieren und einfach die Zellen mit den Fehlerwerten ignorieren, dann können Sie hierfür die folgende Formel verwenden:

{=SUMME(WENN(ISTFEHLER(A2:C11);0;A2:C11))}

Erfassen Sie die Formel ohne die geschweiften Klammern am Anfang und am Ende.

Bestätigen Sie die Formel dann aber nicht mit Enter, sondern mit der Tastenkombination Strg + Umschalttaste + Enter, da es sich um eine Matrixformel handelt. Durch die Tastenkombination werden dann automatisch die geschweiften Klammern am Anfang und Ende eingefügt.

Nutzen Sie ein Microsoft 365-Abo, dann können Sie die Formel auch normal mit Enter erfassen.

Wie funktioniert die Summenformel mit Fehlerwerten?

Zunächst wird mit der Funktion ISTFEHLER(A2:C11) jede Zelle im Bereich, der addiert werden soll (hier: A2:C11) untersucht, ob hier ein Fehlerwert vorliegt oder nicht.

Liegt ein Fehlerwert vor, dann wird über die WENN()-Funktion der Wert 0, sonst der tatsächliche Wert der Zelle zurückgegeben: {=SUMME(WENN(ISTFEHLER(A2:C11);0;A2:C11))}

Die über die Funktion WENN() zurückgegebenen Werte werden anschließend an die Funktion SUMME() weitergeleitet und von dieser summiert: {=SUMME(WENN(ISTFEHLER(A2:C11);0;A2:C11))}

Auf diese Weise gehen nur die Zellen ohne Fehlerwerte in die Summierung mit ein.

Alternative Funktion AGGREGAT()

Eine interessante Alternative für diese Aufgabenstellung ist die Funktion AGGREGAT() in Excel. Sie ermöglicht es, Berechnungen wie zum Beispiel Mittelwert, Summe oder Maximum durchzuführen, während bestimmte Werte oder Fehler ignoriert werden können. Das ist besonders nützlich, wenn man Daten analysiert, die Ausreißer oder Fehler enthalten.

Erfassen Sie die folgende Funktion, um die Daten ohne die Fehlerwerte zu summieren:

=AGGREGAT(9;6;A2:C11)

Die Funktion AGGREGAT() in Excel ist äußerst flexibel und leistungsstark, da sie mehrere Berechnungsfunktionen integriert. Außerdem lassen sich bestimmte Werte oder Fehler in den Daten ignorieren. Dies ist besonders hilfreich in umfangreichen Datensätzen, bei denen man zum Beispiel Nullwerte, Fehler oder ausgeblendete Zellen nicht berücksichtigen möchte.

AGGREGAT() besitzt die folgende Syntax:

AGGREGAT(Funktionsnummer; Optionen; Bereich)

Funktionsnummer

Eine Zahl zwischen 1 und 19, die die auszuführende Berechnung angibt.

• 1 - MITTELWERT
• 2 - ANZAHL
• 3 - ANZAHL2
• 4 - MAX
• 5 - MIN
• 6 - PRODUKT
• 7 - STABW.S
• 8 - STABW.N
• 9 - SUMME
• 10 - VAR.S
• 11 - VAR.P
• 12 - MEDIAN
• 13 - MODUS.EINF
• 14 - KGRÖSSTE
• 15 - KKLEINSTE
• 16 - QUANTIL.INKL
• 17 - QUARTILE.INKL
• 18 - QUANTIL.EXKL
• 19 - QUARTILE.EXKL

Im Beispiel muss der Wert 9 für Summe erfasst werden.

Optionen

Eine Zahl zwischen 0 und 7, die bestimmt, welche Werte ignoriert werden sollen:

• 0 oder nicht angegeben - Geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
• 1 - Ausgeblendete Zeilen, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
• 2 - Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
• 3 - Ausgeblendete Zeilen, Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
• 4 - Nichts ignorieren
• 5 - Ausgeblendete Zeilen ignorieren
• 6 - Fehlerwerte ignorieren
• 7 - Ausgeblendete Zeilen und Fehlerwerte ignorieren

Im Beispiel muss der Wert 6 für Fehlerwerte ignorieren angegeben werden.

Bereich

Hier erfassen Sie den Bereich, in dem die zu summierenden Werte stehen. Im Beispiel ist dies der Bereich A2:C11.