In Excel Werte über Zelle x bist zu einer bestimmten anderen (variablen) Zelle adieren?

...komplette Frage anzeigen

4 Antworten

Hallo,

angenommen, die zu beachtenden Zeilen sind 2-5 und das Ergebnis kommt in B6, würde ich so vorgehen,

=SUMMENPRODUKT((A$2:A5>=DATUM(JAHR(A5);MONAT(A5);1))*(A$2:A5<=DATUM(JAHR(A5);MONAT(A5);MONATSENDE(A5;0)));(B$2:B5))

Ganz generell würde ich dir aber empfehlen, den Aufbau der Tabelle zu ändern. Du hast Tageswerte und summierte Monatswerte in einer Spalte und das ist eigentlich suboptimal.

Man erfasst alle Einträge in einer Tabelle und macht die Auswertungen in einer Separaten.

LG, Chris

Kopfsalat321 09.08.2017, 11:57

Danke für die Antwort!

Ich weiß aber doch nie, wie viele Zellen es sind, die ich addieren muss, daher kann ich nicht sagen A2:A5.

Ich hätte es auch lieber in einem separaten Tabellenblatt, aber das erschien mir noch komplizierter. Dann muss ich ja einen Anfangs- und Endpunkt bestimmen. Mit der Variante nur den Anfangspunkt. Das Ende steht ja schon fest (Wenn "Summe" in Zeile A steht).

Mein ursprünglicher Gedanke, den ich nicht umsetzen konnte, war folgender:

WENN "Summe" in A, DANN (in B alles addieren von Monat x, bis zur letzten Zeile, in der SUMME in A stand)

Ist das hilfreicher, um meine Problematik zu verstehen?

0
Suboptimierer 09.08.2017, 12:12

Gut, dass du daran gedacht hast, dass auf den Monat abzufragen nicht ausreicht, weil das dann jahresübergreifend nicht funktioniert.

0
Kopfsalat321 09.08.2017, 12:33
@Suboptimierer

Ich habe auch schon versucht Monat und Jahr zu kombinieren, um dem entgegenzuwirken, konnte aber leider keinen Weg finden.

Ich habe auch überlegt, irgendwie jeder Monats und Jahreskombination eine Art Hashwert zur eindeutigen Identifizierung zuzuordnen, aber das übersteht ebenfalls meinen Excel-Horizont. :D

0
Funfroc 09.08.2017, 13:01
@Kopfsalat321

Meine Formel fragt Monat und Jahr ab.

Und meine Formel fragt immer von Zeile 2 bis zur Zeile über der Formel ab. Daher auch A$2

Wenn die Formel in B6 eingefügt wird fragt sie A2:A5 ab, wenn sie nach B15 kopiert wird, fragt sie A2:A14 ab

1
Funfroc 09.08.2017, 13:06
@Funfroc

Und wenn du die Auswertung in einer separaten Tabelle machen würden, könntest du wie folgt vorgehen.

A: Jahr

B: Monat

C: Formel...

=SUMMEWENNS(Tabelle1!B:B;Tabelle1!A:A;">="&Datum(A2;B2;1);Tabelle1!A:A;"<="&Monatsende(Datum(A2;B2;1);0))

0

Klingt nach einem Fall für SUMMEWENN, kombiniert mit MONAT(<Zelle darüber>).

Kopfsalat321 09.08.2017, 12:07

Danke für die Antwort.

Kannst du das etwas genauer ausführen? :)

0
Suboptimierer 09.08.2017, 12:09
@Kopfsalat321

Hab gerade Excel nicht zur Hand, aber dass du es auf Anhieb nicht hin bekommst erinnert mich daran, dass es Probleme damit gab, in der Bedingung sich auf Datumsteile zu beziehen. 

Wahrscheinlich musst du auf etwas anderes ausweichen, z. B. SUMMENPRODUKT, dann 

=SUMMENPRODUKT((MONAT(<Datumsspalte>)=8);<Wertespalte>)
1
Kopfsalat321 09.08.2017, 12:54
@Suboptimierer

Deine Formel hat leider nicht funktioniert oder ich habe es falsch gemacht.

Ich habe es jetzt geschafft, jeder Monat und Jahreskombination einen eindeutigen <Datum-Wert> zuzuordnen:

=MONAT(A2)+JAHR(A2)*3,85

Diesen habe ich jetzt in folgende Formel eingebaut:

=WENN(A20="Summe";SUMMEWENN(<Hilfszeile mit den "Datum-werten">;<Datum-wert X>;<Zahlen-Wertespalte>))

Jetzt muss ich nurnoch hinbekommen, dass immer der richtige <Datum-wert> für den jeweiligen Monat genommen wird.

0
Suboptimierer 09.08.2017, 12:57
@Kopfsalat321

Du kannst dich getrost auf den Datum-Wert der vorherigen Zeile beziehen, da die Spalte ja aufsteigend sortiert ist.

Jahr mit Monat zu kombinieren ist eine gute Idee. Ich verstehe nur die Formel nicht. Wieso 3,85? Ich hätte vermutlich einfach Jahr und Monat aneinandergekettet: JAHR(A2)&"-"&MONAT(A2).

0
Kopfsalat321 09.08.2017, 13:09
@Suboptimierer

Ich wollte einen Zahlenwert der eindeutig für jeden Monat von Jedem Jahr ist. Monat + Jahr ergibt spätestens nächstes Jahr die gleiche Summe wieder. (Bsp. Februar 2017 = 2019, Januar 2018 = 2019)

Wenn ich das Jahr aber * irgend eine Zahl, z.B. 3,85 nehme, gibt es auch in 10 Jahren noch keine doppelten Werte.

Ich habe jetzt eine "kleine" Tabelle mit allen Kombinationen der nächsten 10 Jahre und versuche die gerade über SVERWEIS in die o.g. Formel an der Stelle von <Datum-wert X> einzubauen.

 

/edit: Das aneinanderketten finde ich schwierig weiter in Formeln zu verwenden. Aber klär mich gerne auf :)

0
Suboptimierer 09.08.2017, 13:12
@Kopfsalat321

Ich würde es mit der Verkettung probieren. Ist eindeutig und erfordert keinen SVERWEIS.

0
Kopfsalat321 09.08.2017, 13:14
@Kopfsalat321

Ok, Excelmäßig viel zu kompliziert gedacht.

Ich mache keine SVERWEIS sondern einfach die letzte angegebene <Datum-wert> Zahl. Sobald ich händisch "Summe" eintrage, ist ja keine solche Zahl mehr da. :) Ich glaube ich habe es hinbekommen, vielen Dank für die Hilfe!

1
Kopfsalat321 09.08.2017, 14:57
@Funfroc

Danke für das produktive Kommentar.

Ich sehe es dir mal nach, da du meine Excel-Tabelle nicht kennst und daher auch nicht wissen kannst, dass es in den weiteren Formel wesentlich einfacher ist, mit einer Zahl zu rechnen.

0
Funfroc 10.08.2017, 08:38
@Kopfsalat321

Hallo,

nimm es mir nicht übel, aber ich brauche deine restliche Tabelle auch nicht zu kennen, um dir zu sagen, dass der Aufbau dieses kleinen Bruchstückes schon quatsch ist. Aber das habe ich ja bereits vorher getan. 

Genauso habe ich dir eine Lösung gezeigt, die Auswertung zu separieren, wie man es tun sollte, den unterschiedlich geartete Daten gehören nicht in der gleichen Spalte untereinander gelistet.

mit einer Zahl zu rechnen

ja natürlich... das ist genau das, was ein Summenprodukt oder eine Summewenn ausgibt.

LG, Chris

0

Hab ich es richtig verstanden, dass du mehrere Einträge in einer Spalte hast und diese durch Monat trennst. Dann für den jeweiligen Monat die Summe haben möchtest?

Kopfsalat321 09.08.2017, 12:00

Erstmal danke für die Antwort!

Ja, ich habe einen Haufen Werte in Spalte B, die ich alle addieren will, sobald der Monat rum ist.

Wenn der Monat rum ist, schreibe ich händisch "Summe" in Spalte A unter die jeweiligen Buchungsdaten und möchte dadurch auslösen, dass alles in B von eben diesem Monat addiert wird.

0
Bergtroll1994 09.08.2017, 13:05
@Kopfsalat321

Also ich hatte eine ähnliche Frage vorgestern noch :D
Da wurde mir einmal Teilergebnis von jemandem vorgeschlagen und von Suboptimierer (der dir hier ebenfalls geantwortet hat) auch eine Formel.

Denke die Summewenn Formel wäre praktischer.

Ansonsten könnte vielleicht Teilergebnis auch funktionieren. Hier ist mal der Link, wenn du etwas scrollst siehst du ein Kommentar von TimeTraveler68, er gab mir dazu eine Erklärung und Bilder.
https://www.gutefrage.net/frage/excel--wie-viele-verschiedene-zahlen-nach-2-kriterien?foundIn=user-profile-question-listing

Teilergebnis findest du in Excel unter:
Daten > Gliederung > Teilergebnis

1

Schau dir mal die Funktion SUMMEWENN an.

Was möchtest Du wissen?