Quick Tipp: Summieren trotz #NV Fehler

Summe mit Fehler

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: Summe mit Fehler

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.

Wenn Dir dieser Excel Tipp geholfen hat, dann wird Dir mein Buch 101 Excel Tipps für Power User noch viel mehr helfen!

Comments

  • 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…

    MättuOctober 13, 2015
  • 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

    aunkelbachOctober 13, 2015
  • 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

    OliverAugust 24, 2016
    • 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!

      Lukas RohrAugust 26, 2016
  • 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.

    AnonymousNovember 4, 2016

Leave a Reply