Autor: Gary Knott

Ich hoffe, Sie sind auch ein Fan von SUMMEWENNS... diese großartige Funktion kann sehr nützlich sein, um Teilmengen von Daten zu summieren. Aber sie hat zwei Einschränkungen:

1. Die Durchführung von Berechnungen mit Daten in anderen Dateien ist nicht die beste Praxis, kann aber in einigen Fällen eine praktische Lösung sein. In solchen Fällen kann jedoch eine SUMIFS-Formel Fehler erzeugen, wenn die andere Datei geschlossen ist.
2. Sie können nicht Kriterien in zwei Dimensionen (Zeilen UND Spalten) berücksichtigen, nur die eine oder die andere.

Es gibt einen Weg, diese beiden Probleme mit der Funktion SUMMENPRODUKT zu lösen.

1. SUMMENPRODUKT in einer Dimension

Hier ist ein Beispiel mit einer kleinen Datentabelle.

Wie Sie sehen, ist die Formel wie die äquivalente SUMMEWENNS-Formel. Hier ist jedoch nicht jeder Suchbereich von seinen zugehörigen Suchkriterien durch ein Trennzeichen getrennt (normalerweise ein Komma oder Semikolon, je nach Ihren regionalen Einstellungen). Stattdessen werden sie durch ein Gleichheitszeichen getrennt, was ich leichter lesbar finde. Jeder (Suchbereich = Suchkriterien) Test wird von Klammern umgeben und mit dem nächsten (Suchbereich = Suchkriterien) Test multipliziert. Am Ende müssen Sie mit 1 multiplizieren, um alle WAHR und FALSCH Ergebnisse in 1 und 0 umzuwandeln, und schließlich multiplizieren Sie mit dem Datenbereich, der summiert werden soll. Bei SUMMEWENN (1 Suchkriterium) muss dieser Datenbereich das erste Argument in den Klammern sein, bei SUMMEWENNS (1+ Suchkriterien) muss er das letzte Argument sein. Bei SUMPRODUCT kann er entweder das erste oder das letzte Argument sein.

Externe Dateien

Alle Funktionsargumente (die Elemente in den Klammern) können auf eine externe Datei verweisen.

how2tipp: Ich finde es einfacher, eine solche Formel in der Datei mit den Quelldaten zu schreiben und zu testen und sie dann auszuschneiden und an der gewünschten Stelle einzufügen. Dies ist einfacher, weil Sie (a) besser sehen können, worauf Sie sich beziehen, und (b) der Pfad und der Dateiname erst in der Formel erscheinen, wenn Sie sie ausschneiden und einfügen, so dass sie leichter zu schreiben, zu verstehen und zu testen ist.

2. SUMMENPRODUKT in zwei Dimensionen

Die oben genannten Prinzipien können nun, wie hier gezeigt, auf zwei Dimensionen erweitert werden, wobei dieselbe Datentabelle verwendet wird.

Hier ist es wichtig, dass (ähnlich wie im eindimensionalen Beispiel) die Kriterienbereiche mit der Höhe bzw. Breite der Datentabelle übereinstimmen, wie Sie im obigen F2 ("Bearbeitungsmodus") Screenshot sehen können.

3. Bedingte Formatierung

Um das Tüpfelchen auf dem i zu setzen, entschied ich mich für eine bedingte Formatierung, um die sich addierenden Datenzellen automatisch (in grün) hervorzuheben. Diese variieren, je nachdem, welche Kriterien in den lachsfarbenen Eingabezellen ausgewählt werden.

Das bedingte Format muss auf alle Datenzellen in der Tabelle angewendet werden, da jede von ihnen die ausgewählten Bedingungen erfüllen könnte.

Das Format muss Zellen hervorheben (in diesem Fall in grün), wenn alle Bedingungen in der SUMPRODUCT-Formel erfüllt sind, wie unten dargestellt. Hier ist es wichtig, auf die Dollar-(Fixierungs-)Symbole zu achten. Schreiben Sie die Formel immer aus der Sicht der Zelle in der linken oberen Ecke des Datenbereichs, hier C3.  Die Formel muss mit einem Gleichheitszeichen beginnen (wie in allen Excel-Formeln) und muss ein WAHR oder FALSCH als Ergebnis liefern. Sie ist das Äquivalent zum Schreiben einer WENN-Formel, aber Sie benötigen die WENN-Funktion nicht. Schreiben Sie einfach den logischen Test, je nach Bedarf mit "ODER" bzw. "UND".

Das war's, viel Spaß!