Das Aufsummieren von Werten in Excel-Tabellen unter bestimmten Bedingungen ist manchmal nicht ganz so trivial, wie es auf den ersten Blick aussieht.
Im Artikel „Alle Kriterien erfüllt? Bedingte Summen in Excel“ habe ich schon verschiedene Möglichkeiten aufgezeigt.
Manchmal ist die Aufgabenstellung aber etwas anders, so dass die gezeigten Wege möglicherweise nicht zum Ergebnis führen. Wie bildet man z.B. eine Summe über mehrere Spalten, wenn die jeweilige Zeile variabel ist, also von einem bestimmten Kriterium abhängt?
Keine Angst, auch für dieses Problem gibt es eine Lösung!
So geht’s:
Die Aufgabe
Als Beispiel habe ich eine Tabelle vorbereitet, welche die monatlichen Umsätze für verschiedenen Verkäufer enthält (wenn du willst, kannst du die Datei hier herunterladen):
Die Beispiel-Tabelle
Dabei möchte ich in einem separaten Feld einen beliebigen Verkäufer auswählen können (Zelle B13) und Excel soll mir nun zu diesem Verkäufer den Gesamtumsatz anzeigen (Zelle B15).
Die Problemstellung ist also, eine Summe über alle Monate zu bilden, wobei die jeweilige Zeile in der Summen-Funktion jedoch vom gewählten Verkäufer abhängig ist.
Die vermeintlich naheliegende Lösung
Eine Summe, die von einer Bedingung abhängt? Dazu fällt einem wahrscheinlich zuerst die SUMMEWENN-Funktion ein:
=SUMMEWENN(Suchbereich; Suchkriterium; Summenbereich)
Der Ansatz ist verführerisch: Als Suchbereich gibt man die Spalte A mit den Verkäufern an, das Suchkriterium ist der gewählte Verkäufer in Zelle B13 und als Summenbereich wählt man den Bereich mit den Umsätzen.
Das Ergebnis ist aber leider nicht so wie erwartet:
SUMMEWENN hilft hier nicht weiter
Die SUMMEWENN-Funktion findet zwar die richtige Zeile, nimmt aber nur den Wert in der ersten Spalte des Summenbereichs, hier also 5.185 Euro.
Die Natur dieser Funktion ist eben, dass alle Werte in einer Spalte summiert werden, und nicht in mehreren Spalten. Die Funktion wäre also dann hilfreich, wenn es mehrere Zeilen mit dem gewählten Verkäufer gäbe und nun alle Werte in einer Spalte summiert werden sollen
Es muss also eine andere Lösung her.
Die Lösung
Was wir brauchen: Die ganz normale SUMME-Funktion. Nur muss der Summenbereich um eine bestimme Anzahl von Zeilen verschoben werden. Nämlich in die Zeile, in der sich der gewählte Verkäufer befindet. Und genau dazu gibt es die Funktion mit dem schönen Namen BEREICH.VERSCHIEBEN
=BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])
Wenn wir z.B. den Verkäufer „Meyer“ gewählt haben, wollen wir also den Summenbereich (= B2:M10) um drei Zeilen nach unten verschieben. Und da uns nur die Umsätze von Meyer interessieren, wollen wir den Bereich auch gleich noch auf eine einzige Zeile reduzieren:
Der Summenbereich muss verschoben werden
Damit unsere Formel nicht gleich zu unübersichtlich wird, ermitteln wir zunächst in Zelle C13 mit Hilfe der VERGLEICH-Funktion, in welcher Zeile der Verkäufer Meyer steht:
Ein Zwischenschritt mit der VERGLEICH-Funktion
In Zelle B15 schreiben wir nun unsere SUMME-Funktion, geben jedoch keinen festen Summenbereich an, sondern verwenden stattdessen die BEREICH.VERSCHIEBEN-Funktion:
BEREICH.VERSCHIEBEN hilft weiter
Zur Erläuterung:
In der BEREICH.VERSCHIEBEN-Funktion haben wir als ersten Parameter den ursprünglichen Summenbereich, also alle einzelnen Umsätze (B2:M10).
Diesen Bereich wollen wir jetzt um einige Zeilen nach unten verschieben. In Zelle C13 haben wir berechnet, dass der Verkäufer Meyer in Zeile 4 steht. Wir müssen den Summenbereich also um 3 Zeilen nach unten verschieben. Im zweiten Parameter ziehen wir daher vom Wert in Zelle C13 noch 1 ab (4 – 1 = 3).
Die Spalten brauchen wir nicht zu verschieben, also geben wir für den dritten Parameter den Wert 0 an.
Allerdings soll der Summenbereich nur eine Zeile hoch sein, also ist der vierte Parameter = 1.
Die Breite des Summenbereichs brauchen wir nicht zu verändern und können daher den fünften Parameter ganz weglassen.
Und somit erhalten wir auch das gewünschte Ergebnis:
Die Kombination macht’s: SUMME und BEREICH.VERSCHIEBEN
Ich gebe zu, vielleicht nicht ganz naheliegend, aber letztendlich doch nicht ganz so kompliziert, wie es ursprünglich ausgesehen hat.
Im nächsten Artikel werden wir noch ein bisschen mehr Flexibilität einbauen und nicht nur die Zeilen, sondern auch die Anzahl der summierenden Spalten variabel gestalten. Ich hoffe, du bist wieder dabei!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Pingback: Bedingte Summen in Excel (Teil 3) | Der Tabellen-Experte
Pingback: Jetzt kommt Bewegung rein: Dynamische Bereichsnamen | Der Tabellen-Experte
Hallo Martin,
nach langer Stöberei im Internet bin ich eindeutig zu dem Schluss gekommen, dass Deine Erklärungen zu Excel-Problemen so ziemlich die Besten sind, die ich im Netz gefunden habe. Du erklärst sowas von gut – das kann nur jemand, der die Materie komplett selber kapiert hat. Daumen hoch! Bin selber Mathe/Physik/Technik-Lehrer. Viieelen Dank für Deine Hilfe! Wolfgang Schläger aus Lindenberg im Allgäu.
Hallo Wolfgang,
vielen Dank für das tolle Feedback, und noch dazu von einem Lehrer, das freut mich wirklich sehr!
Schöne Grüße,
Martin