Summe bilden bei zwei zu berücksichtigenden Variablen (EXCEL)?
Hallo, ich brauche Eure Hilfe :)
Ich habe eine Tabelle mit folgenden Spalten: Buchungstag, Zahlungsempfänger, Betrag. Mein Ziel ist es in einem separaten Tab eine Übersicht zu erstellen, in dem die Zahlungen an die jeweiligen Zahlungsempfänger nach Monaten summiert werden. Beispielsweise, wenn ich nur drei verschiedene Zahlungsempfänger hätte (Amazon EU, Amazon US, Thalia), wie kann ich dann sagen, dass Excel alle Beträge, die in der gleichen Zeile, wie das Wort "Amazon" stehen und z.B. im Oktober 2020 bezahlt wurden, summiert werden? Wie müsste die Excel-Formel dafür lauten?
4 Antworten
Wenn du so was öfter machst, lohnt es sich, dass du dich in "Pivot-Tabellen" einarbeitest.
Pivot-Tabellen sind eine Art Tabelle, die für genau diesen Typ Aufgabe entwickelt worden ist.
Ansonsten schreib die Kriterien untereinander in eine weitere Spalte und verwende die Funktion SUMMEWENN.
Könntest Du mir die SUMMEWENNS-Formel einmal geben, wie Du sie bilden würdest. Daran bin ich nämlich gescheitert, was mich dazu geführt hat, hier diese Frage zu stellen :D
Buchungstag, Valuta, Zahlungsempfänger, Konto IBAN, Betrag, Saldo, Kategorie.. Mein Ziel ist es, für den jeweiligen Monat die Summen der jeweiligen Zahlungsempfänger automatisch berechnen zu lassen
Vermutlich sind nur Monat und Zahlungsempfänger Filterkriterien, stimmt das?
Und ausgegeben werden sollen Zahlungsempfänger und Summe über den Monat, oder noch mehr?
Ausgegeben werden soll die Summe, die gezahlt wurde an den jeweiligen Zahlungsempfängern gefiltert nach einzelnen Monaten und Jahren
Ich hoffe, das hilft.. falls nicht, wie kann ich hier Screenshots reisenden? Vllt kann ich es dann etwas besser verdeutlichen
Screenshots an Kommentare anhängen wäre ein sinnvolles Feature. Aber das geht anscheinend nur bei Fragen - die kann man auch noch nach Tagen ändern.
Sollen die Monate und die Zahlungsempfänger automatisch gesammelt werden? Dann würde ich vermutlich den Pivottabellen-Assistenten empfehlen. Hoffentlich ist der übersichtlich genug.
Ich will es ja in einem separaten Tab in eine Übersicht einfügen, deshalb wäre für mich eine Formel sinnvoll
Pivot-Tabellen sollten sich ohne Weiteres auf eine andere Tabelle beziehen können. (Sogar auf mehrere andere Tabellen: https://support.microsoft.com/en-us/office/use-multiple-tables-to-create-a-pivottable-b5e3ff48-2921-4e29-be15-511e09b5cf2d)
Aber wenn es Formeln sein sollen, würde ich ein paar Hilfsspalten verwenden.
(Formeln für Zeile 10 nach unten bzw. ggf. auch nach oben kopieren/ziehen)
Wenn ich Spalte C als erste Hilfsspalte verwende:
Spalte C übernimmt Zahlungsempfänger aus der Datensammlung (wenn es stört, dass eine leere Zelle in 0 umgesetzt wird: ein Leerzeichen an die Zeichenkette anhäängen:
=Originaltabelle!Empfängerspaltenzelle&""
oder
=WENN(Originaltabelle!Empfängerspaltenzelle="";"";Originaltabelle!Empfängerspaltenzelle)
Spalte D übernimmt Datum bzw. Jahr und Monat:
=JAHR(Originaltabelle!Datumzelle)&"-"&MONAT(Originaltabelle!Datumzelle)
bzw.
=WENN(Originaltabelle!Datumzelle)="";"";JAHR(Originaltabelle!Datumzelle)&"-"&MONAT(Originaltabelle!Datumzelle))
Spalte E übernimmt den Betrag (nicht unbedingt nötig, würde ich aber übersichtlicher finden)
Spalte F bekommt das Suchkriterium; Formeln für F10
Falls zunächst nach Empfängern sortiert (in der Reihenfolge der Originaltabelle):
=WENN(C10="";"";C10&":"&D10)
(oder irgendein anderes Zeichen außer einer Ziffer statt des Doppelpunkts)
Falls zunächst nach Datum sortiert (in der Reihenfolge der Originaltabelle):
=WENN(C10="";"";D10&ZEICHEN(9)&C10)
(ZEICHEN(9) ist ein Zeichen, das nie ohne diesen Trick in einem Text in einer Zelle auftreten kann, dadurch sind alle eingebbaren Zeichen im Namen möglich, insbesondere Ziffern)
In Spalte G werden die erstmaligen Auftritte der Einträge in Spalte F durchnummeriert; G10:
=WENN(F10="";"";WENN(VERGLEICH(F10;F:F;0)<ZEILE();"";MAX(G$1:G9)+1)
In Spalte H wird ermittelt, in welcher Zeile von G die passende Nummer steht; wenn die Einträge ab Zeile 10 da stehen sollen:
H7 (oder irgendeine andere Zelle oberhalb von H10):
=MAX(G:G)
(nicht unbedingt nötig, m. E. aber etwas einfacher zu handhaben)
H10:
=WENN(ZEILE()-ZEILE(G$9)>H$7;"";VERGLEICH(ZEILE()-ZEILE(G$9);G:G;0))
In Spalte I kommen die Empfänger:
=WENN($H10="";"";C10)
In Spalte J kommen die Daten/Monate:
=WENN($H10="";"";D10)
(mit dem "$" vor dem "H" kann man die Formel von Spalte I nach rechts kopieren/ziehen, wenn sich die Anordnung der Spalten nicht ändert)
In Spalte K kommt die Summe der Beträge
=SUMMEWENNS(E:E;F:F;INDEX(F:F;$H10))
-----
So was habe ich schon so oft gemacht, dass es Routine ist, allerdings habe ich es für diesen Fall noch nicht ausprobiert.
Hi,
Du könntest die Tabelle nach Namen sortieren lassen dann ist die Summenbildung ganz einfach
LG
Harry
Hallo Harry,
danke für die schnelle Rückmeldung. Wie wäre dann die Formel dafür?
AM besten ganze Tabelle in neues Blatt kopieren, sonst bleibt sie so verändert nach dem Sortieren.
Keine Formel,
sondern Tabellenbereich markieren und unter DATEN auf soertieren gehen. Dann wählst du die zu sortierenden Spalten aus usw.
Das hilft mir leider nicht weiter, weil ich die Buchungstage nur nach Jahre filtern kann und nicht, wie gewünscht nach Monaten.
dass Sie das nicht können glaube ich Ihnen, es ist aber prinzipiell möglich. Schauen Sie sich mal die monat() funktion an
z.B. so ... oder wie
Antwortvon PWolff 06.12.22, 12:00:20 per PIVOT-Tabelle
Ich halte es mit PWolff, viele Leute schrecken sich vor der Pivot-Tabelle, aber mit ein paar Klicks hast du eine solche aufgebaut. Es zahlt sich aus, sich damit ein bisschen zu beschäftigen:
Ich habe tatsächlich so eine Tabelle gebaut, wie komme ich dadurch an mein Ziel?
Korrektur: nimm besser SUMMEWENNS.
(SUMMEWENN erfordert mindestens eine zusätzliche Hilfsspalte, wenn mehrere Kriterien berücksichtigt werden sollen.)