Beitrag aus SmartTools Excel Weekly
Spaltensumme bei Übereinstimmung in mehreren Spalten ermitteln
Excel 365 2024 2021 2019 2016 2013
FRAGE In einer Arbeitsmappe habe ich zwei Tabellen in verschiedenen Tabellenblättern. Ich möchte bestimmte Spalten aus den Tabellen vergleichen und, wenn in einer Zeile die entsprechenden Spalteninhalte identisch sind, die Summe der Werte einer anderen Spalte aus der ersten Tabelle bilden. Bei den zu vergleichenden Werten handelt es sich zum Teil um Zahlen, zum Teil aber auch um Text. Ich habe schon verschiedene SUMMEWENN-Varianten ausprobiert, komme damit aber nicht zum Ziel. Können Sie mir weiterhelfen?
H. O. Stampfli
ANTWORT Mit einer SUMMENPRODUKT-Formel sollte das Problem zu lösen sein.
In der Formel vergleichen Sie spaltenweise die Werte aus der ersten Tabelle mit den korrespondierenden Werten aus der zweiten Tabelle. Dadurch erhalten Sie Arrays aus Wahrheitswerten, die Sie in Einsen (Wahr = Inhalte stimmen überein) oder Nullen (Falsch = keine Übereinstimmung) umwandeln.
Per SUMMENPRODUKT multiplizieren Sie die Werte, was nur dann das Ergebnis "1" liefert, wenn die Inhalte einer Zeile in beiden Tabellen identisch sind. Sonst führt eine Multiplikation mit "0" immer zu "0".
Als letzten Parameter der SUMMENPRODUKT-Funktion setzen Sie dann die Tabellenspalte ein, aus der Werte summiert werden sollen.
Angenommen, Sie hätten eine Tabelle namens "tblBasis" mit den Spalten "Datum", "Vergleich1", "Vergleich2", "Vergleich3" und "Wert". Außerdem haben Sie eine Tabelle namens "tblReferenz", die neben anderen ebenfalls die Spalten "Vergleich1", "Vergleich2" und "Vergleich3" enthält.
Wenn eine Zeile in beiden Tabellen dieselben Werte in den drei Vergleichsspalten aufweist, soll der Wert in der Wertespalte der ersten Tabelle aufsummiert werden.
Das erreichen Sie mit folgender Formel, die Sie in eine beliebige Zelle eingeben können:
=SUMMENPRODUKT(--(tblBasis[Vergleich1]=
tblReferenz[Vergleich1]);--(tblBasis[Vergleich2]=
tblReferenz[Vergleich2]);--(tblBasis[Vergleich3]=
tblReferenz[Vergleich3]);tblBasis[Wert])
Da sich die Formel auf Bereiche in Tabellen bezieht, verwendet sie strukturierte Verweise. "tblBasis[Vergleich1]" verweist zum Beispiel auf alle Werte in der Spalte "Vergleich1" aus der Tabelle "tblBasis".
Wenn Sie nicht mit Tabellen arbeiten, müssten Sie stattdessen mit herkömmlichen Zellbezügen arbeiten – etwa "B4:B8" oder "Jan!D4:D8" (für einen Bereichsbezug im Tabellenblatt "Jan").
Die Funktionsweise der Formel hatten wir eingangs beschrieben. Die dort angesprochene Umwandlung der Wahrheitswerte in Nullen und Einsen erreichen Sie mit dem Ausdruck "--". Bei den Vergleichen spielt es ansonsten keine Rolle, ob es sich um Text- oder Zahlenwerte handelt.
Wenn Sie mehr als drei Spalten in den Tabellen vergleichen wollen, fügen Sie einfach weitere Parameter in die SUMMENPRODUKT-Funktion ein.