Bedingte Summen in Excel (Teil 2) 4

Artikelbild-64
Summenbildung unter bestimmten Bedingungen kann manchmal etwas vertrackt sein. Die Lösung gibt's hier.
 

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

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

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

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

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

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

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!

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

4 Gedanken zu “Bedingte Summen in Excel (Teil 2)