Summieren trotz #NV Fehler
Beim Erstellen von Berichten hat man manchmal die Situation dass man Daten mit einem SVERWEIS anreichern will, aber schon im Voraus weiss, dass gewisse Werte nicht vorhanden sein werden und die Formel dann #N/A als Resultat liefert.
Will man nachher eine Summe über die Spalte bilden, funktioniert die Formel SUMME nicht, sondern gibt den Fehler der SVERWEIS Formel weiter.
Wie man den #N/A Fehler Summiert
Wer die Lösungen auch in Excel anschauen will kann das mit dieser Datei tun:
Lösung 1: SUMMEWENNS
Mit der SUMMEWENNS Formel kann man das Problem umgehen, indem man Excel erklärt dass es Zellen die den Wert #N/A haben nicht summieren soll. Dies müssen wir aber explizit tun in dem wir den #N/A Wert in der Formel festhalten:
=SUMMEWENNS(C4:C11;C4:C11;"<>#NV")
Andere Fehler können so ebenfalls umgangen werden.
Lösung 2: MATRIX Formel
Ein weiterer Lösung Ansatz, der jedoch meines Erachtens nicht ganz so elegant ist wie die Lösung 1, ist das Erstellen einer Matrix Funktion. Matrix Formeln sind weit Flexibler als normale gewöhnliche Formeln, aber auch komplizierter zum Erstellen, und heikler in der Bearbeitung/Anpassung.
Mit der folgenden Matrix Formeln kann der #NV Fehler ebenfalls umschifft werden:
{=SUMME(WENN(ISTNV(C4:C11)=FALSCH;C4:C11))}
Hier prüfen wir zuerst ob der Wert NV ist mit der eigens dafür vorgesehenen ISTNV() Formel und summieren dann nur die Werte welche nicht einen NV Fehler liefern.
Hinweis: Matrix Formeln müssen mit STRG+SHIFT+RETURN eingegeben werden! Die geschweiften Klammern werden von Excel nicht erkannt wenn Sie manuell eingegeben werden.
Sehr schön.
Ich habe es bisher immer mit wennfehler(formel;0) gelöst, da die summierende Spalte eh meist eine formel beinhaltet.
Werde es mal so probieren…
Hallo Mättu,
Wennfehler ist eine tolle Option für die SVERWEIS Formeln. Problematisch ist nur, wenn man eine Tabelle erhält und hier nicht alle #NV ausgleichen möchte sondern einfach nur die Summe ziehen mag, hier gefällt mir die Lösung mit SUMMEWENN sehr gut :-)
Teilweise sollte man aber auch darauf achten, dass WENNFEHLER erst ab Excel 2010 (oder allenfalls 2007) vorhanden ist und man aus Kompatibilitätsgründen oft die Kombination mit ISTNV vorziehen sollte…
Viele Grüße
Andreas
Hallo zusammen,
eine Alternative ist auch die Funktion Aggregat. In diesem Beispiel =AGGREGAT(9;6;C4:C11)
Funktion =9=Berechnet die Summe
Option =6=Ignoriert die Fehlerwerte
Array =C4:C11 =Bereich der summiert wird.
Grüße
Hallo Oliver,
AGGREGAT ist eine interessante Funktion. Bei Macro freien Modellen sehr zu empfehlen. Eingebunden in VBA habe ich damit aber schon weniger gute Erfahrungen gemacht; es scheint ein Bug zu geben in Excel der die Applikation in einen unendlichen Berechnungsmodus laufen läst wenn man die Formel braucht.
Evtl. ein zukünftiger Beitrag. Danke für die Anregung!
noch viel einfacher
ich stelle einfach generel “iferror(….) ;0)
dann werden N.V, sofort mit 0 ersetzt.
Dies sollte man je nachdem erst tuen, wenn man sich sicher ist, dass der Sverweis funktioniert.
kommt das “N.V.” durch fehler im Sv-weit steht überall “0” , aber das sollten dann auch schon so auffallen.
Warum denn unbedingt SummeWennS?
Es geht auch mit =SummeWenn(C4:C11;”>=0″)
Ich empfehle immer die SUMMEWENNS Formel anstelle der SUMMEWENN zu benutzen.
Dies weil es oft passiert dass man den Bericht oder die Analyse erweitern will und neue Kriterien hinzukommen. Mit der SUMMEWENNS ist es ein Leichtes diese zusätzlichen Kritieren einzufügen. Mit der anderen, muss alles umgeschrieben werden. Deshalb: immer SUMMEWENNS benutzen!
Welche Kriterien sollten hier dazu kommen?
Ich habe so viele Beispiele wo feststeht, dass keine weiteren Kriterien dazu kommen, typische Rechnungssummen, warum da nicht auf Summewenn zurückgreifen?
Wenn mehrere Kriterien kommen, dann ist das Umschreiben bei so einfachen Dingen doch schnell gemacht.
Dagegen würde ich nicht mit “#NV” arbeiten, denn wenn ein anderer Fehler auftaucht, geht es wieder nicht. Besser mit Istfehler()
Es gibt hier zwei Formeln welche das selbe machen. Eine der beiden kann man ergänzen und erweitern, die andere nicht. Macht doch Sinn dass ich den Leuten empfehle die flexiblere zu lernen. Umschreiben ist Mehrarbeit.
Vielen, vielen Dank! Variante 1 klappt super!