Die SUMME-Formel gehört zu den Standardwerkzeugen in Excel. In größeren Tabellenblättern aber ist das manuelle Auswählen der Wertebereiche für eine Summe bald nicht mehr praktikabel. Andererseits werden in Excel-Mappen sehr häufig Spaltentitel verwendet. Ich möchte euch deshalb am folgenden Beispiel erläutern, wie die Summenbildung anhand von Spaltentiteln automatisiert werden kann.

Das Beispiel

Das folgende Beispiel steht hier als Download zur Verfügung:

Ausgangspunkt ist die exemplarische Umsatzübersicht einer KFZ-Werkstatt:

Wie in der Abbildung zu sehen, wurde im Beispiel in Zelle F19 die Summe der Spalten D und E gebildet, also der Spalten, denen der Spaltentitel “Ersatzteile” zugeordnet ist. Dieses Ergebnis wurde nicht manuell ermittelt, sondern mit einer Formel. Der Suchbegriff wird in Zelle E19 geschrieben, und Excel wird immer die Summe aller Spalten bilden, deren Spaltentitel dem Textstring in E19 entspricht. Wie genau lässt sich diese Automatisierung erstellen?

Vorgehensweise

1. Die richtige Funktion in Excel

Excel hat kein Bordmittel für diese Aufgabe, aber das ist auch nicht notwendig. Die Funktion SUMMENPRODUKT bietet eine passende Lösung. SUMMENPRODUKT multipliziert die vorgegebenen Matrizen (Arrays) und summiert anschließend die erhaltenen Produkte. Der Syntax:

SUMMENPRODUKT(Array1;[Array2];[Array3];…)

Nun müssen wir noch einen mathematischen Ausdruck finden, der den Abgleich eines bestimmten Textstrings ermöglicht. Dazu notieren wir den Bereich in unser Array, der die zu prüfenden Überschriften enthält (im Screenshot: B3:F3), und unterziehen diesen Bereich einer Wahrheitsprüfung:

B3:F3=“Ersatzteile“

Diese Gleichung prüft, ob eine Zelle die Zeichenkette „Ersatzteile“ enthält, und gibt WAHR oder FALSCH zurück. Die Werte WAHR oder FALSCH wiegen in einer Multiplikation wie 1 und 0 und Spalten, deren Überschrift nicht auf „Ersatzteile“ lautet, werden im SUMMENPRODUKT zu null. Hier die Formel für Zelle F19:

=SUMMENPRODUKT((B3:F3=”Ersatzteile”)*(B5:F16))

2. Zellenbezüge verwenden

Die Formel kann jetzt noch durch einen Zellenbezug flexibler gestaltet werden. Die Überschrift, für die die Summe gebildet werden soll, kann dann einfach in Zelle E19 geschrieben werden:

=SUMMENPRODUKT((B3:F3=E19)*(B5:F16))

3. Suche nach Teilbegriffen in Spaltentiteln

Um noch flexibler zu werden, sollte es auch möglich sein, nach Teilbegriffen in Spaltentitel zu suchen. Im folgenden Beispiel setzen sich die Spaltentitel aus Kategorie- und Typenbezeichnungen zusammen. Excel bietet hier die Funktion SUCHEN, die einen vorgegebenen Text nach einer vorgegebenen Zeichenfolge durchsucht. Der Syntax:

SUCHEN(Suchtext;Text;[Erstes_Zeichen])

SUCHEN gibt die Stelle in einem vorgegebenen Text an, an der der Suchtext beginnt. Die Formel SUCHEN(“x”;”Excel”) beispielsweise gibt das Ergebnis 2 zurück. Das Argument [Erstes_Zeichen] ist optional und weist Excel an, die Suche im Text erst ab einer bestimmten Stelle zu beginnen. SUCHEN gibt entweder eine ganze Zahl größer Null aus oder meldet „kein Ergebnis”. Diesen Umstand können wir uns mit ISTZAHL zunutze machen:

ISTZAHL(SUCHEN(Suchtext;Text))

Der Formelabschnitt ISTZAHL(SUCHEN(E19;B3:F3)) gibt für jede Zelle, in der der Suchtext gefunden wurde, WAHR zurück, und andernfalls FALSCH.

=SUMMENPRODUKT(ISTZAHL(SUCHEN(E19;B3:F3))*(B5:F16))

4. Mehrzeilige Spaltentitel auswerten

Es können auch noch mehr Argumente in das SUMMENPRODUKT geschrieben werden. Das bietet sich an, wenn ein Datenblatt mehrere Titel pro Spalte hat. Mit der folgenden Formel in Zelle F19 werden die Spaltentitel in Zeile 3 nach dem Suchbegriff in Zelle E19 durchsucht und die Spaltentitel in Zeile 4 nach dem Suchbegriff in Zelle E20. Eine Beispielrechnung: wird „Comfort-Line“ in E19 geschrieben und „Ersatzteile“ in E20, summiert Excel nur die Ersatzteile auf, die im Geschäftsbereich Comfort-Line verwendet wurden.

=SUMMENPRODUKT(ISTZAHL(SUCHEN(E19;B3:F3))*ISTZAHL(SUCHEN(E20;B4:F4))*(B5:F16))

In diesem Zusammenhang wird auch die Verwendung von Platzhaltern interessant. Platzhalter sind Variablen, die für beliebige Zeichen stehen können. Excel betrachtet (*) als Platzhalter für beliebig lange Zeichenketten, (?) als Platzhalter für ein Zeichen und kontextabhängig, wie in diesem Fall, wird (), also eine leere Zelle, als Platzhalter für ein beliebiges Zeichen behandelt. Tragen wir jetzt z.B. „Service“ in E19 ein und lassen E20 leer (oder tragen (?) oder (*) ein), bildet Excel die Gesamtsumme aus allen Spalten, denen in Zeile 3 der Spaltentitel „Service“ zugeordnet ist. Und umgekehrt: Zelle E19 ohne Inhalt und „Ersatzteile“ in E20 summiert alle Umsätze aus der Kategorie Ersatzteile.

Ich hoffe ich konnte euch mit meinem Beitrag ein paar interessante Ideen für zukünftige Excel-Vorhaben vorstellen. Und ich bin natürlich gespannt, eure Kommentare zu lesen. Hattet Ihr schon Aufgaben wie im Beispiel und wie habt Ihr sie gelöst? Hattet Ihr noch einen anderen Lösungsweg?