Beitrag aus SmartTools Excel Weekly
Summe mit ODER-Verknüpfungen in mehreren Spalten einer Excel-Tabelle
Excel 365 2024 2021 2019 2016 2013
Vor kurzem haben wir einen Tipp zur Funktion SUMMENPRODUKT veröffentlicht. Wir beschreiben darin unter anderem, wie man Summen in Abhängigkeit von mehreren Kriterien bildet, wobei entweder das eine oder das andere Kriterium zutrifft. Dabei suchen wir aber in derselben Spalte nach den Kriterien. Einige Leser haben nach einer ODER-Verknüpfung über mehrere Spalten gefragt - also etwa: Summiere alle Werte aus Spalte D, wenn entweder in Spalte B der Wert "x" steht oder in Spalte C der Wert "y". Die vorgestellten SUMMENPRODUKT-Formeln scheitern, wenn per Zufall beide Kriterien zutreffen. Dann wird der entsprechende Wert aus Spalte D doppelt addiert.
Um das Problem zu lösen, müssen Sie die einzelnen Vergleichskriterien mit einer Addition verketten. Diese Summe schließen Sie danach aber noch in einen weiteren Vergleich ein, in dem Sie abfragen, ob die Summe der Einzelkriterien größer als "0" ist. Die daraus resultierende Matrix können Sie schließlich mittels SUMMENPRODUKT mit der Wertespalte multiplizieren, um das gewünschte Endresultat zu erhalten.
Nehmen Sie als Beispiel eine Bestellliste mit Bestellnummern in Spalte A, den Datumsangaben in Spalte B, Kundennamen in Spalte C und den jeweiligen Bestellbeträgen in Spalte D. Die gesamte Liste erstreckt sich von Zeile 4 bis Zeile 800 - also von A4:D800.
Nun wollen Sie die Summe aller Einzelbestellungen ermitteln, die entweder im Jahr 2024 aufgegeben wurden oder die einem Kunden namens "Ernst Handel" zugeordnet sind. Dabei dürfen die Bestellungen von "Ernst Handel" aus dem Jahr 2020 nicht doppelt berechnet werden.
Sie erhalten Ihr Ergebnis im genannten Beispiel mit folgender Formel:
=SUMMENPRODUKT(--((--(JAHR(B4:B800)=2024))+
(--(C4:C800="Ernst Handel"))>0);D4:D800)
Die Formel prüft zunächst, ob die Bestellungen aus 2024 stammen, erstellt daraus über alle Datensätze eine Matrix mit WAHR- und FALSCH-Werten, die mit Hilfe des doppelten Minuszeichens in die Werte "1" (für WAHR) oder "0" (für FALSCH) umgewandelt werden.
Danach erstellt die Formel nach demselben Prinzip eine zweite Matrix aus Einsen und Nullen, die aussagt, ob als Kunde "Ernst Handel" eingetragen ist.
Als nächstes addiert die Formel die beiden Matrizen, woraus sich wieder eine Matrix ergibt. Sie enthält an der Stelle eine "1", an der entweder das erste oder das zweite Kriterium zutrifft ("1 + 0 = 1" oder "0 + 1 = 1"). Wenn keines der Kriterien zutrifft, weist die Matrix den Wert "0" aus. Problematisch nur, wenn beide Kriterien zutreffen - dann ergibt sich eine "2" ("1 + 1 = 2").
Darum stellt die Formel einen weiteren Vergleich an, in dem sie prüft, ob die Werte der Summenmatrix größer als "0" sind. Das trifft nur dann NICHT zu, wenn ein Datensatz keines der Kriterien erfüllt. In allen anderen Fällen ist der Vergleich WAHR und entspricht nach der Umwandlung per doppeltem Minuszeichen dem Zahlenwert "1". Auf diese Weise schließen Sie die mehrfache Wertung desselben Bestellbetrages aus.
SUMMENPRODUKT muss jetzt nur noch diese letzte Matrix mit den Bestellsummen multiplizieren, um in der abschließenden Summe die Werte zu berücksichtigen, für die entweder das eine oder das andere Kriterium zutrifft.
Die Formel lässt sich sogar auf mehr als zwei Spalten mit ODER-Verknüpfungen ausdehnen. Die generelle Syntax lautet:
=SUMMENPRODUKT(--((--(<Kriterium 1>))+
(--(<Kriterium 2>))+(...)>0);<zu addierender Wertebereich>)