In diesem Artikel lernst du, wie du Werte automatisch nach Datumsbereichen mit einer SUMMENPRODUKT Formel berechnest und zusätzlich noch eine Dropdown Liste verwendest.

Dazu brauchen wir nicht nur die Funktion SUMMENPRODUKT sondern auch die Funktion INDIREKT.

Also lade dir die Übungsdatei herunter und schon kann es losgehen.

Das ist die Funktion SUMMENPRODUKT

Wie die Funktion aufgebaut ist und wie sie im Detail funktioniert, habe ich in diesem Artikel ausführlich behandelt, daher gehe ich nur noch ganz grob auf ihre Funktion ein.

Die Funktion SUMMENPRODUKT multipliziert jeden Wert eines Arrays mit einem Wert eines anderen Arrays und addiert diese, bildet also die Summe. Das sieht vereinfacht dann so aus:

=SUMMENPRODUKT(B7:B11;C7:C11)

Hier wird jeder Wert aus dem Array B7:B11 mit den Werten aus dem Array C7:C11 mit einander multipliziert und anschließend addiert.

Wie bereits erwähnt, wenn du mehr Infos zur Funktion SUMMENPRODUKT haben möchtest, dann ist dieser Artikel der richtige für dich.

Was wollen wir erreichen

Unsere Ausgangsdaten sehen so aus:

Ziel soll es nun sein, ohne den Einsatz von VBA in folgender Übersicht Daten auszuwerten und gegenüber zu stellen.

Dabei sollen um die Übersicht zu behalten, maximal zwei Werte angezeigt werden können und die Auswertung soll Monatsweise erfolgen.

Die zu berechnenden Werte sollen mit der Dropdown Liste bestimmt werden.

Was benötigen wir dazu

Wir möchten Werte summieren, anhand der Daten, die wir auswählen und die zusätzlich von einem Datumsbereich abhängig sind.

Zur Auswahl benötigen wir als erstes unsere Dropdown Liste, mit der wir auswählen, welche Daten wir auswerten möchten.

Zusätzlich benötigen wir dann noch die monatsweise Auflistung der Werte.

Dann fehlt noch das Kernstück, eine Formel die uns anhand der beiden Daten eine Summe erstellt und diese in unsere Auswertung überträgt.

Um Trends und Verläufe besser zu erkennen, soll zusätzlich noch ein Diagramm in die Übersicht integriert werden.

Wie lösen wir das Problem einer SUMMENPRODUKT Funktion in Verbindung mit einer Dropdown Liste

Am besten Schritt für Schritt.

Dropdown Liste erstellen

Zuerst, erstellen wir unsere Dropdown Liste. Dazu markierst du die beiden Zellen in welchen die Dropdown Liste erscheinen soll und wählst die Überschrift unserer Ausgangsdaten als Quelle.

Wenn du mehr über den Einsatz und die Erstellung von Dropdown Listen erfahren möchtest, darfst du dir meinen Artikel darüber nicht entgehen lassen. Diesen findest du hier: Alles, was du über Dropdown Listen wissen musst.

Datumswerte monatsweise auflisten

Als nächstes brauchen wir die Monate nach denen wir die Daten summieren möchten.

Das könnten wir einfach machen, indem wir das Datum in die einzelnen Zellen eintragen, ich möchte aber dass diese automatisch auf Basis unserer Ausgangsdaten berechnet werden. Somit müssen wir das Datum nicht jedes Mal ändern, wenn sich unsere Ausgangsdaten ändern.

Dazu nutzt du am besten die Funktion MONATSENDE mit der du den ersten Tag des Monats ermitteln kannst. Ich weiß das klingt kompliziert aber eine Funktion in Excel für den Monatsanfang gibt es nicht.

Du könntest natürlich mit jedem beliebigen Tag in einem Monat rechnen, ich verwende aber gerne den Monatsersten.

Warum ich ausgerechnet den Monatsersten verwende und weitere hilfreiche Details zum Rechnen mit Datumsfunktionen habe ich dir in diesem Artikel beschreiben: Datumsfunktionen in Excel richtig einsetzen

Und wie immer findest du hier alle Informationen zu Formeln und Funktionen.

Jetzt aber zur fertigen Formel:

=MONATSENDE(Ausgangsdaten!$D3;-1)+1

Die Funktion berechnet das Monatsende des Datums aus der Zelle D3 im Tabellenblatt „Ausgangsdaten“ und subtrahiert dabei mit der Angabe von -1 einen Monat. Das +1 addiert zu dem Datum nun einen Tag und das Gesamtergebnis ist nun das Monatsende des vorhergehenden Monats plus einem Tag, also der erste Tag des aktuellen Monats.

Das Ganze vielleicht noch mal etwas anschaulicher Schritt für Schritt:

=MONATSENDE(Ausgangsdaten!$D3;-1)+1

Monatsende vom 10.10.2019 wäre der 31.10.2019. Das (-1) in der Funktion subtrahiert davon einen Monat.

Also ist das Ergebnis der 30.09.2019.

Wenn du jetzt wieder einen Tag dazu addiert, das (+1) in der Formel, erhältst  du den 01.10.2019.

Den ersten Monat hast du damit berechnet. Jetzt fehlen noch die folgenden.

Folgemonate auflisten

Um nun die Folgemonate aufzulisten, müssen wir lediglich immer einen Monat dazu zählen.

Dazu nutzen wir die Formel =DATUM(Jahr;Monat;Tag).

Mit dieser Formel kannst du mit Hilfe von Zahlen ein Datum erstellen.

Zum Beispiel gibt die Formel =DATUM(2020;02;14) als Ergebnis das Datum 14.02.2020 aus.

Nun wieder zur Formel, die wir in Zelle B4 einfügen.

=DATUM(JAHR(B3);MONAT(B3)+1;1)

Die Formel nach unten erweitert, ergibt folgendes:

Nähere Infos zu dieser Funktion findest du in diesem Artikel: Die Funktion DATUM

Die Rahmenbedingungen für die SUMMENPRODUKT Formel haben wir nun geschaffen.

Jetzt kommen wir zum Kern unserer Auswertung.

Die SUMMENPRODUKT Formel

Ich möchte euch gar nicht länger auf die Folter spannen, daher hier die Formel.

Da ist sie, die Schönheit:

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))*(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))))

Wenn du jetzt denkst „Was labert der da, Schönheit? Das ist ein Monster!“ Dann wundert mich das nicht. Aber keine Sorgen, wir bauen diese Formel Schritt für Schritt miteinander auf.

Willst du darauf nicht warten, kannst du sie ganz einfach hier aus dem Beitrag oder aus der Übungsdatei kopieren.

Wenn du aber zu denen gehörst, die Interesse an der Formel oder sogar Spaß an Excel haben, dann möchte ich dir in folgenden Absätzen den Aufbau und die Funktionsweise der Formel erklären.

Welche Bestandteile braucht unsere fertige SUMMENPRODUKT Formel

Wenn du dich erinnerst, brauchen wir in der Funktion SUMMENPRODUKT Arrays, die miteinander multipliziert werden.

Was sind nun unsere Arrays?

Einerseits die einzelnen Tage des jeweiligen Monats.

Und andererseits die Werte in den Spalten unserer Ausgangsdaten welche wir über die Dropdown Liste bestimmen.

Beginnen wir mit den Datumswerten.

Das Datums Array

In unseren Ausgangsdaten ist für jeden Tag ein Eintrag vorhanden. Wir wollen aber nach Monaten auswerten.

Also müssen wir alle Datumswerte in unseren Ausgangsdaten wählen, die größer als der Monatsanfang sind gleichzeitig aber kleiner als das Monatsende.

Jede dieser Bedingungen ist ein Array unserer Formel.

Der erste Teil sieht also so aus:

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))

Keine Sorge, was die Formel genau macht erkläre ich dir weiter unten. Falls du es gleich wissen möchtest, folge diesem Link.

Als zweiten Schritt müssen wir die Datumswerte nun mit jenen aus den anderen Spalten multiplizieren.

Das Werte Array

Quick and dirty würde es funktionieren, wenn du die Formel folgendermaßen ergänzt.

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))*Ausgangsdaten!E3:E472)

Das ist aber sehr unflexibel außerdem nützt uns da die Dropdown Liste nichts. Also müssen wir die Formel nun etwas abändern und erweitern.

Das Problem ist, dass wir den Text aus einer Dropdown Liste in eine Formel verknüpfen müssen.

Dazu ist am Besten die INDIREKT Funktion geeignet.

Wir müssen also den Verweis Ausgangsdaten!E3:E472 mittels Formel zusammensetzen, sodass sich dieser mit Änderung des Textes in der Dropdown Liste auch ändert.

Was sind denn die festen Bestandteile der Formel? Naja der Blattname Ausgangsdaten! und die Zeilennummern sind immer gleich, egal welche Spalte wir auslesen wollen. Lediglich der Spaltenbuchstabe soll sich ändern.

Die Formeln die uns da weiter helfen sind erstens die Funktoin ADRESSE und zweitens die Funktion VERGLEICH.

Die Funktion VERGLEICH gibt die Stelle in einem Bereich an, an der sich das Suchkriterium befindet und die Funktion ADRESSE setzt zwei Zahlen zu einer Zelladeresse zusammen.

Ohne Umschweife, hier der fertige zweite Teil der SUMMENPRODUKT Formel, keine Angst, wie bereits erwähnt folgt weiter unten eine ausführliche Erklärung.

=INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))))

Haben wir nun die drei Arrays definiert, müssen wir diese nur noch zusammen führen.

So sieht die fertige SUMMENPRODUKT Formel aus

=SUMMENPRODUKT(((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))*((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))*(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))))

Wenn wir die Formel nun bis in die letzte Zelle erweitern, erhalten wir folgendes Ergebnis:

Was macht diese Formel nun genau

Nun aber zum spannendsten Teil, wie funktioniert die Formel und was machen die einzelnen Teile davon genau?

Zur Formelanalyse kann ich dir auf jeden Fall die Taste F9 ans Herz legen um die einzelnen Bestandteile der Formel auszuwerten.

Was genau du mit der Taste F9 machen kannst und noch weitere Tipps zur Formelanalyse findest du in diesem Artikel: So gelingt dir jede Formel Analyse

Übergeordnet haben wir unsere SUMMENPRODUKT-Formel. Diese besteht in unserem Fall aus drei einzelnen Arrays, welche miteinander multipliziert und dann summiert werden.

Das erste Array der SUMMENPRODUKT Formel

Das erste Array schließt alle Werte aus, welche kleiner als der Monatsanfang des jeweiligen Monats sind.

((Ausgangsdaten!$D$3:$D$472)>=(MONATSENDE($B3;-1)+1))

Würdest du das Array ausschreiben, sehe es so aus. Hier wird jeder Wert einer jeden Zelle aufgelistet.

{FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR;WAHR}

Auf den ersten Blick sehr verwirrend, ist aber einfach erklärt.

Die Formel überprüft jeden Wert in dem gewählten Bereich Ausgangsdaten!$D$3:$D$472 mit der Bedingung (MONATSENDE($B3;-1)+1) und gibt für alle Werte, die größer oder gleich der Bedingung sind WAHR aus und für alle die kleiner sind, FALSCH aus.

Das zweite Array der SUMMENPRODUKT Formel

Genau das Gleiche passiert nun auch im zweiten Array, hier prüft die Formel jedoch alle Werte, ob diese kleiner oder gleich dem Monatsende des gewählten Monats sind.

((Ausgangsdaten!$D$3:$D$472)<=(MONATSENDE($B3;0)))

Das dritte Array der SUMMENPRODUKT Formel

Abschließend dann noch das dritte Array, in welchem bestimmt wird, mit welcher Spalte die Ergebnisse aus dem ersen und zweiten Array multiplizietrt werden.

Wie macht die Formel das nun genau? Dazu zerlegen wir diese in die einzelnen Bestandteile.

(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))):INDIREKT(„Ausgangsdaten!„&ADRESSE(472;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0))))

Das Array besteht aus zwei INDIREKT Funktionen, eine vor dem „:“ und eine danach. Die INDIREKT Funktion davor bestimmt, wo der Bereich beginnt.

(INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))

Also wie du weißt wandelt die Funktion INDIREKT einen Text in einen Zellenbezug oder Adresse um, wie das funktioniert, habe ich dir in diesem Artikel erklärt: Funktion INDIREKT für jedermann einfach erklärt

Wie setzt sich unsere Adresse nun zusammen.

Eine Zellenadresse auf einem anderen Arbeitsblatt muss in folgendem Format vorhanden sein. Ausgangsdaten!C3

Also erst der Blattname gefolgt von einem „!“ und danach von der eigentlichen Zellenadresse.

Diese Adresse müssen wir mit unserer INDIREKT Funktion nun irgendwie zusammen basteln.

Was sind die festen Bestandteile der Adresse?

Einerseits der Blattname und andererseits die Zeilennummer.

Somit können wir den Beginn der Adresse als Text in die Formel einfügen.

INDIREKT(„Ausgangsdaten!

Gefolgt vom Spaltenbuchstaben. Wir wollen jedoch die jeweilige Spalte auf Basis der Dropdown Liste auswählen. Und nicht fest kodiert in der Formel hinterlegen.

Dazu nutzen wir den Umweg über die Funktion VERGLEICH, diese gibt die Stelle wieder, an der ein Wert in einem Bereich steht.

Steht der gesuchte Wert beispielsweise in Spalte E so gibt die Formel die Zahl 5 zurück.

VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)

Wir brauch aber den Buchstaben der Spalte für unsere INDIREKT Funktion. Also ist eine zusätzliche Formel erforderlich, die eine Zahl in einen Spalten Buchstaben umwandelt.

Das geht ganz einfach mit der Funktion ADRESSE.

Also packen wir unsere erhaltene Spaltenzahl in die Funktion ADRESSE. Diese Funktion wandelt zwei Zahlen in eine Zellenadresse um. Die Zahlen 4 und 7 ergeben beispielsweise die Zelle G4.

ACHTUNG: In der Funktion ADRESSE muss die Zeile vor der Spalte angeführt werden.

Außerdem kannst du noch definieren, ob es sich um einen absoluten oder relativen Zellbezug handeln soll und du kannst noch viele weitere nützliche Parameter festlegen, dazu aber in diesem Artikel mehr: Funktion ADRESSE

Wenn wir die beiden Formeln nun zusammen führen, sieht das so aus.

ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))

Die Zeile wird als feste Zahl ausgeführt, da diese ja in jeder Spalte gleich ist.

Die Spaltennummer wird über die Dropdown Liste mit der Funktion VERGLEICH aus dem Bereich Ausgangsdaten!$2:$2 automatisiert bestimmt.

Verbinden wir dies nun wieder mit unserer begonnen INDIREKT Funktion ergibt sich folgende Formel.

INDIREKT(„Ausgangsdaten!„&ADRESSE(3;VERGLEICH(C$2;Ausgangsdaten!$2:$2;0)))

Die einzelnen Bestandteile berechnet ergeben folgendes:

  • Die Funktion VERGLEICH gibt die Position unseres Textes aus der Dropdown Liste (C$2) in dem Bereich Ausgangsdaten!$2:$2

INDIREKT(„Ausgangsdaten!„&ADRESSE(3;5))

  • Die Funktion ADRESSE erzeugt aus der Zahl 3 und dem Ergebnis der VERGLEICH Funktion eine Zellenadresse.

INDIREKT(„Ausgangsdaten!„&“$E$3„)

  • Diese Zellenadresse wird in der Funktion INDIREKT mit dem Text kombiniert und erzeugt so eine finale Zellenreferenz auf ein anderes Arbeitsblatt.

INDIREKT(„Ausgangsdaten!$E$3„)

Der erste Teil des Arrays gibt nun also die erste Zelle des Bereichs an und der zweite Teil, die letzte Zelle des Bereiches.

(INDIREKT(„Ausgangsdaten!$E$3„): INDIREKT(„Ausgangsdaten!$E$472„))

Berechnung der gesamten Formel

Jetzt haben wir sehr ausführlich geklärt, was die einzelnen Arrays in der Funktion SUMMENPRODUKT machen. Was machen diese aber nun in Verbindung miteinander.

Ganz einfach.

Das erste Array bestimmt alle Werte die größer oder gleich dem Monatsanfang sind und gibt für jedes Datum auf das diese Bedingung zutrifft den Wert WAHR aus.

Das zweite bestimmt alle die kleiner oder gleich dem Monatsende sind und gibt für jedes Datum auf das diese Bedingung zutrifft ebenfalls den Wert WAHR aus.

Die Schnittmenge der beiden wird mit dem erstellten Bereich des dritten Arrays multipliziert.

Im dritten Array stehen Werte wie Umsatz oder Bestellmenge als Zahl, multipliziert man nun eine Zahl mit dem Wert WAHR, wird der Wert WAHR als 1 interpretiert.

Somit erhalten wir als Ergebnis die Summe aller Werte die mit dem Datumsbereich und der Auswahl in der Dropdown Liste übereinstimmen.

Puh.

Erstmal durchatmen. Ich weiß, dass ist nicht ganz einfach nachzuvollziehen, ich hoffe trotzdem ich konnte es dir einigermaßen verständlich erklären.

Ich finde es aber einfach genial, wenn sich einzelne Teile der SUMMENPRODUKT Formel zu einem großen Ganzen verbinden.

Eine Formel zu analysieren kann manchmal sehr schwierig sein und man sollte es unbedingt einige Male üben. Wenn du dich dafür interessierst, kann ich dir auf jeden Fall diesen Artikel dazu empfehlen, dort habe ich dir den Ablauf einer Formelanalyse im Detail erklärt.

Wenn du zu den einzelnen Funktionen noch Fragen hast, kannst du mich jederzeit kontaktieren, benutze dazu einfach das Kontaktformular.

Alternativ kannst du dich auch im Abschnitt Funktionen in Blog umsehen.

So sieht das Ergebnis der SUMMENPRODUKT Formel aus

Abschließend möchte ich die berechneten Daten auch noch optisch ansprechend aufbereiten. Dadurch sind Trends und Zusammenhänge viel besser zu erkennen.

Dazu kopieren wir einfach den Inhalt der Spalte C in die Spalte D. Somit können wir gleichzeitig zwei unterschiedliche Daten auswerten.

Nun wandeln wir unsere Liste mit der Tastenkombination STRG+T noch in eine Tabelle um und fügen ein Diagramm ein.

Und fertig ist die Übersicht über die Verkäufe.

Ich habe dir die fertige Datei als Vorlage zur Verfügung gestellt, solltest du daran Interesse haben, kannst du diese ganz einfach hier herunterladen:

Auswertung Verkaufszahlen.xlsx

Wie kann das Problem alternativ gelöst werden

Ich gebe zu, diese Vorgehensweise mit der SUMMENPRODUKT Formel ist etwas umständlich, aber meines Wissens nach die einzige Möglichkeit Daten anhand einer Dropdown Liste zu wählen und gleichzeitig zu berechnen.

Es gibt aber eine alternative, die zwar nicht mit Dropdown Listen arbeitet, aber um ein Vielfaches schneller zu erstellen ist.

Das Zauberwort lautet Pivot!!!

Dazu wählst du eine beliebige Zelle innerhalb unserer Ausgangsdaten.

Als nächsten Schritt fügst du über Einfügen–>Pivot Tabelle eine Pivot Tabelle ein.

Du wählst neues Arbeitsblatt und bestätigst mit OK.

In dem neu erstellten Arbeitsblatt kannst du nun dieselbe Auswertung mittels Pivot erstellen wie zuvor mit der SUMMENPRODUKT Funktion.

Aber nun Schritt für Schritt.

  • Ziehe die Spaltenüberschrift „Datum“ in das Feld „Spalten“ und die Überschriften „Verkäufe“ und „Umsatz“ ins Feld „Werte“.
  • Klicke mit der rechten Maustaste auf einen Datumswert, wähle gruppieren, gruppiere nach Monaten und Jahren und bestätige mit „Ok“.
  • Formatiere die Spalte Umsatz als Währung und du erhältst folgendes Ergebnis
  • Abschließend fügst du wieder das Diagramm ein und fertig ist die Auswertung.

Der Vorteil dieser Lösung liegt ganz klar in der Geschwindigkeit des Erstellens und in den weiteren Möglichkeiten der Pivot Tabelle.

Der Nachteil, dass du die auszuwertenden Spalten nicht anhand einer Dropdown Liste wählen kannst.

Wenn du die unzähligen Möglichkeiten der Pivot Tabelle nutzen möchtest, dann kannst du dir hier einen kleinen Vorgeschmack holen. Mit dieser Schritt-für-Schritt Anleitung gelingt dir jede Pivot Tabelle

Wenn du zu einzelnen Funktionen noch Fragen hast, melde dich gerne bei mir, das kannst du ganz einfach hier tun: Kontakt aufnehmen

Oder du besuchst einfach die Formelsammlung auf meinem Blog. Formeln

Fazit

Vergleiche ich das Ergebnis nun mit den Anforderungen, würde ich sagen Mission erfüllt.

Der Einsatz der Funktion SUMMENPRODUKT ist zwar nicht ganz einfach aber durchaus machbar. Ich bin mir sicher, dass du diese Funktion mit ein bisschen Übung sicher und effizient einsetzen kannst.

Sollte das zu umständlich sein, kannst du alternativ eine Pivot Tabelle einsetzen, die hat zwar nicht den gleichen Funktionsumfang ist aber dafür um einiges schneller und einfacher.

Als abschließenden Tipp kann ich dir folgendes mitgeben.

Excel hat viel mit logischer Denkweise zu tun, ich selbst musste hunderte Formeln aufbauen, bis ich mir die richtige Denkweise dazu angeeignet habe.

Am effizientesten gelingt das, indem du einfach loslegst. Schnapp dir ein Tabellenblatt und fang an, dann wirst du schnell dahinter kommen.

Alternativ kannst du dich gerne hier noch weiter in das Thema einlesen.

Baue Formeln immer Stück für Stück auf, versuche nicht von Beginn an alles perfekt zu machen. Wenn du dir zu Beginn Gedanken darüber machst, was dein Ergebnis sein soll und deine Formel Stück für Stück aufbaust, wirst du schneller dein Ziel erreichen als wenn du versuchst die gesamte Formel in einem Schwung zu erstellen.

Wenn du Blut geleckt hast und nicht mehr ohne Excel leben willst 

laughing
 oder einfach nur hin und wieder neugierig auf Updates bist, dann trag dich doch in meinen kostenlosen Newsletter ein. Das kannst hier tun.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps mit denen du in kurzer Zeit dein Excel Können auf das nächste Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Dein Michael, alias Doktor Excel der smarte Excel Problem Löser

PS.: Wenn der Artikel hilfreich für dich war, teil ihn doch mit der Welt da draußen damit auch andere davon profitieren können.

PPS.: Vergiss nicht, solltest du spezifische Probleme mit Excel haben, schreib mir eine Nachricht und wir lösen sie gemeinsam. Hier kannst du mich kontaktieren.

Doktor Excel

Doktor Excel

Heißt eigentlich Michael und arbeitet seit über 10 Jahren privat und beruflich mit Excel. Er hat dabei viele Zeitsparende Arbeitsweisen kennen gelernt und teilweise selbst entwickelt.

Hier findest du seine Geschichte: Wer ist dieser Doktor Excel und was hat ein Arzt mit Excel zu tun?

Du kannst ihm auch jederzeit über die Kontakseite eine Nachricht zukommen lassen.