Beitrag aus SmartTools Excel Weekly
SUMMEWENN mit Werten aus externen Arbeitsmappen
Excel 365 2024 2021 2019 2016 2013
FRAGE Ich werte in einer Arbeitsmappe die Daten aus einer anderen Arbeitsmappe aus. Dabei setze ich auch Formeln mit der SUMMEWENN-Funktion ein. Die Ergebnisse der SUMMEWENN-Formeln sind korrekt, solange die Arbeitsmappe mit den Basisdaten geöffnet ist. Wenn ich aber nur die Mappe mit den Auswertungen öffne, liefern die SUMMEWENN-Formeln den Fehler #WERT!. Es nützt auch nichts, die Werte beim Öffnen der Datei zu aktualisieren. Gibt es sonst eine Einstellung, mit der die Formeln korrekte Ergebnisse liefern, ohne jedes Mal die andere Datei öffnen zu müssen?
O. Lehmann
ANTWORT Sie können leider nichts daran ändern, dass SUMMEWENN nicht auf Daten aus externen Arbeitsmappen zugreifen kann, solange diese geschlossen sind. Das gilt übrigens genauso für die Funktionen ZÄHLENWENN und ANZAHLLEEREZELLEN.
Somit bleibt Ihnen nur, einen anderen Formelansatz auszuprobieren. Verwenden Sie die Funktionen SUMME und WENN separat und verpacken Sie sie in eine Arrayformel. Dazu ein Beispiel:
Angenommen, Sie wollen mit der Funktion SUMMEWENN Werte aus dem Bereich D5:D200 der Tabelle "Ausgaben" in der Arbeitsmappe KASSENBUCH.XLSX addieren, sofern im Bereich B5:B200 derselben Tabelle ein bestimmter Wert steht. Den zu suchenden Wert haben Sie als Suchkriterium in Zelle A5 der aktuellen Auswertungstabelle eingegeben. Die Formel dazu würde dann lauten:
=SUMMEWENN('C:\Daten\Excel\[Kassenbuch.xlsx]Ausgaben'!
$B$5:$B$200;A5;'C:\Daten\Excel\[Kassenbuch.xlsx]
Ausgaben'!$D$5:$D$200)
Mit wenigen Änderungen passen Sie die Formel so an, dass sie auch dann ein Ergebnis zurückgibt, wenn die externe Mappe geschlossen ist. Dazu bearbeiten Sie die bestehende Formel und ändern sie in:
=SUMME(WENN('C:\Daten\Excel\[Kassenbuch.xlsx]Ausgaben'!
$B$5:$B$200=A5;'C:\Daten\Excel\[Kassenbuch.xlsx]
Ausgaben'!$D$5:$D$200;0))
Wichtig! Schließen Sie die Formeleingabe aber nicht einfach mit Return ab. Drücken Sie vielmehr Strg + Umschalt + Return, um die Formel einzugeben. So fügen Sie die Formel als Arrayformel ein und Excel umgibt sie automatisch mit geschweiften Klammern.
In der Arrayformel wertet Excel zuerst die WENN-Funktion aus, wodurch sich eine Folge von Zahlen ergibt: bei Übereinstimmung mit dem Suchkriterium der passende Wert aus dem Bereich D5:D200, ansonsten eine Null. Die Funktion SUMME bildet daraus anschließend das gesuchte Ergebnis.