Microsoft Excel

Herbers Excel/VBA-Archiv

Summen/Dropdown Menüs mit Datum

Betrifft: Summen/Dropdown Menüs mit Datum von: narishma one
Geschrieben am: 04.10.2020 16:07:55

Seid herzlich gegrüßt liebes Excel Forum,

ich wende mich an euch, weil ich mich schon seit zwei Tagen in ein Problem verrant habe. Ich möchte eine Summewenns Funktion mit sieben Kriterien füllen, die allesamt aus Dropdown Menüs gespeist werden. Bis dahin scheint das auch zu funktionieren, bis auf eine Ausnahme. Das Kriterium, mit dem ich die Jahreszahl angebe. Wähle ich z.B. 2019 aus, soll mir die Funktion aus einer festgelegten Spalte in einem anderen Registerblatt alle Werte aus 2019 aufsummieren. Die Werte sind im Format 01.01.2019 usw. notiert.

Zwei Lösungsansätze habe ich probiert:

Erster Ansatz)


=SUMMEWENNS(DoA_BetragHauswährung;DoA_Konto;[@Konto];

DoA_Buchungsdatum;WENN(Debitorenreporting!$J$2="";"<>";Debitorenreporting!$J$2);

DoA_Belegart;WENN(Debitorenreporting!$J$3="";"<>";Debitorenreporting!$J$3);

DoA_Topkunde;WENN(Debitorenreporting!$J$4="";"<>";Debitorenreporting!$J$4);

DoA_Geschäftsbereich;WENN(Debitorenreporting!$J$5="";"<>";Debitorenreporting!$J$5);

DoA_Mahnbereich;WENN(Debitorenreporting!$J$6="";"<>";Debitorenreporting!$J$6);

DoA_Mahnstufe;WENN(Debitorenreporting!$J$7="";"<>";Debitorenreporting!$J$7))


Zweiter Ansatz)

=SUMMENPRODUKT(DoA_Konto*DBR[@Konto];

WENN($J$2="";"<>";(JAHR(DoA_Buchungsdatum)=$J$2));

WENN($J$3="";"<>";DoA_Belegart=$J$3);

WENN($J$4="";"<>";DoA_Topkunde=$J$4);

WENN($J$5="";"<>";DoA_Geschäftsbereich=$J$5);

WENN($J$6="";"<>";DoA_Mahnbereich=$J$6);

WENN($J$7="";"<>";DoA_Mahnstufe=$J$7))


Die wenn Funktion brauche ich in beiden Formeln, damit die Summenfunktion auch dann funktioniert, wenn eines der Argumente "leer" ist. Bislang bekomme ich für beide Funktionen nur #Wert! zurück.


Um es zusammen zu fassen:

I) Drop down Menü Eingabe: Jahreszahl

II) Prüfung von DoA_Buchungsdatum und Ausgabe der Zellen, welche innerhalb des genannten Jahres liegen

III) Integration der Angaben in die Hauptfunktion

IV) Abstimmung mit den restlichen Kriterien

V) Summierung der passenden Ergebnisse


Vielen Dank für eure Hilfe

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: Hajo_Zi
Geschrieben am: 04.10.2020 16:09:18

frage jemand der neben Dir sitzt der sieht die Datei.
Die meisten hier sehen Sie nicht.
Da Sie nicht auf Deinen Rechner schauen.
Vielleicht sollte die Datei verlinkt werden?

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten ändern. Schaue Datei
http://hajo-excel.de/gepackt/fremd/Datei_verschluesseln.zip

Das ist nur meine Meinung zu dem Thema.

GrußformelHomepage

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: narishma one
Geschrieben am: 04.10.2020 17:32:13

Eine Beispieldatei finden Sie hier:

https://www.herber.de/bbs/user/140642.xlsx

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: Hajo_Zi
Geschrieben am: 04.10.2020 17:37:38

ich kann nicht helfen da ich die Formel
WENN($J$2="";"<>";(JAHR(DoA_Buchungsdatum)=$J$2))
nicht finden konnte.
Vielleich sollte man mitteilen wo die steht?

Gruß Hajo

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: narishma one
Geschrieben am: 04.10.2020 18:39:19

Die Formel ist in der Dummydatei noch nicht drin. Ich habe sie nur in einer einzelnen Zelle getestet. Die vollständige Formel ist in meinem ursprünglichen Beitrag enthalten unter "zweiter Ansatz".

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: Hajo_Zi
Geschrieben am: 04.10.2020 18:47:51

Gut, warum sollte ich Deine Datei verändern. Du machst es nicht.
Das mache ich nicht und bin darum raus.

Gruß Hajo

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: narishma one
Geschrieben am: 04.10.2020 19:51:09

Ich bin mir nicht ganz sicher wo das Problem liegt. Ich lade die Datei einfach nochmal hoch und füge die zweite Formel mit ein.

https://www.herber.de/bbs/user/140647.xlsx

Betrifft: AW: nachgefragt ...
von: neopa C
Geschrieben am: 05.10.2020 11:29:37

Hallo,

... warum hast Du nicht meine Hinweise im Beitrag von gestern 18:19 reagiert?

Deine jetzt in Spalte D aufgezeigte Formel greift wieder auf Deine unnötig und ungünstig (weil sie den gesamten Zeilenbereich beinhalten) definierten benannten Formeln zurück und nicht auf die Spaltennamen der formatierten Tabelle. Hinzu kommt, dass Deine SUMMENPRODUKT()-Formel auch wieder falsch definiert ist.

Erkläre doch mal verbal (ohne Formel) und lediglich für die Prüfperiode 2019, was Du in Spalte D genau berechnen willst und gib dafür mal das von Dir erwartete/ händisch ermittelte Ergebnis für zwei Konten an. Dann sehen wir weiter.

Gruß Werner
.. , - ...

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: narishma one
Geschrieben am: 04.10.2020 18:43:32

Die Formel ist in der Dummydatei noch nicht drin. Ich habe sie nur in einer einzelnen Zelle getestet. Die vollständige Formel ist in meinem ursprünglichen Beitrag enthalten unter "zweiter Ansatz".

Betrifft: AW: hier ist einiges zu korrigieren ...
von: neopa C
Geschrieben am: 04.10.2020 19:19:41

Hallo,

... wenn Du eine korrekte Auswertung vornehmen willst. Ich hab mich nachfolgend momentan lediglich Deiner Formel in I18 angenommen.

Zunächst: korrekt als "formatierte Tabellen" umgewandelte Listen benötigen nicht zusätzlich der Definition von benannten Namen ihrer Spalten, die mit ihren "Feldnamen" (vorhandene Listenspalten-Überschriften) definiert wurden.

Dann ist Deine SUMMEWENNS()-Formel in I18 mit ... ;$J$2;2019) auch falsch definiert und wertet auch nur den Wert für das Konto aus, welches in der gleichen Zeile in Spalte A steht.

Ich habe im Namensmanager Deine benannten Formeln alle gelöscht und Deine Formel in I18 nun wie folgt definiert, womit die Gesamtsumme für alle in DBR[Konto] stehenden Konten ausgewertet werden.

=SUMMENPRODUKT(SUMMEWENNS(DoA[Betrag in Hauswährung];DoA[Konto];DBR[Konto];DoA[Buchungsdatum];">="&"1.19"+0;DoA[Buchungsdatum];"<="&"1.20"-1;DoA[Belegart];J3))

Soll nur der Wert für das in gleicher Zeile stehende Kontonummer ausgewertet werden, dann bedarf es das zusätzliche SUMMENPRODUKT() nicht, dann so:

=SUMMEWENNS(DoA[Betrag in Hauswährung];DoA[Konto];DBR[@Konto];DoA[Buchungsdatum];">="&"1.19"+0;DoA[Buchungsdatum];"<="&"1.20"-1;DoA[Belegart];J$3) und diese nach unten und oben kopierbar (wenn da natürlich nicht schon etwas steht).

Infolge der von mir gelöschten benannten Formeln müsstest Du natürlich die Formel in =DBR[Forderungen] entsprechend anpassen. Dies sollte aber nun für Dich kein Problem sein.


Gruß Werner
.. , - ...

Betrifft: AW: Summen/Dropdown Menüs mit Datum
von: narishma_one
Geschrieben am: 06.10.2020 16:38:30

Hallo Werner,

erst einmal, vielen Dank für deine Hilfe. Ich habe deinen Lösungsansatz ausprobiert, das ist bei der Anpassung rausgekommen.

"=SUMMEWENNS(DoA[[#Kopfzeilen];[Betrag in Hauswährung]];DoA[[#Kopfzeilen];[Konto]];[@Konto];DoA[[#Kopfzeilen];[Buchungsdatum]];">="&"1.19"+0;DoA[[#Kopfzeilen];[Buchungsdatum]];"<="&"1.20"-1;DoA[[#Kopfzeilen];[Belegart]];$J$2)"

Leider liefert mir diese Formel überhaupt keine Ergebnisse, nicht einmal die die ich mit meiner ersten Formel bekommen habe. Ich habe den Eindruck, mein Problem vielleicht nicht ausführlich genug geschildert zu haben, deshalb möchte ich einen neuen Anlauf unternehmen. Ich fange am besten mit meiner ursprünglichen Formel an:

=SUMMEWENNS(DoA_BetragHauswährung;
Der Bereich der summiert werden soll.

DoA_Konto;[@Konto];
Kontonummer der Zeile, die summiert werden soll.

DoA_Buchungsdatum;WENN(Debitorenreporting!$J$2="";"<>";Debitorenreporting!$J$2);
Wenn J2 leer ist, überspringe dieses Kriterium ("<>"),sonst übergebe J2 und suche in Buchungsdatum.

DoA_Belegart;WENN(Debitorenreporting!$J$3="";"<>";Debitorenreporting!$J$3);
Wenn J3 leer ist, überspringe dieses Kriterium ("<>"),sonst übergebe J3 und suche in Belegart.

DoA_Topkunde;WENN(Debitorenreporting!$J$4="";"<>";Debitorenreporting!$J$4);
Wenn J4 leer ist, überspringe dieses Kriterium ("<>"), sonst übergebe J4 und suche in Topkunde.

DoA_Geschäftsbereich;WENN(Debitorenreporting!$J$5="";"<>";Debitorenreporting!$J$5);
Wenn J5 leer ist, überspringe dieses Kriterium ("<>"), sonst übergebe J5 und suche in Geschäftsbereich.

DoA_Mahnbereich;WENN(Debitorenreporting!$J$6="";"<>";Debitorenreporting!$J$6);
Wenn J6 leer ist, überspringe dieses Kriterium ("<>"), sonst übergebe J6 und suche in Mahnbereich.

DoA_Mahnstufe;WENN(Debitorenreporting!$J$7="";"<>";Debitorenreporting!$J$7))
Wenn J7 leer ist, überspringe dieses Kriterium ("<>"), sonst übergebe J7 und suche in Mahnstufe.

Was funktioniert an der Formel nicht? Das Kriterium Buchungsdatum, welches das Jahr aus Zelle J2 übernehmen soll (das wähle ich dann aus einem Dropdown Menü aus). Auswählen kann ich aus den Jahren 2009 bis 2020.

Wenn ich die Kriterien ">="&"1.19"+0 und "<="&"1.20"-1 in deiner an Anfang genannten Formel richtig verstehe, soll hier zwischen 2019 und 2020 eingegrenzt werden, auch wenn ich zugeben muss, dass ich den Aufbau der Kritierien nicht ganz verstehe.

Der Sinn hinter dem ursprünglichen Aufbau meiner Formel ist, jedes Kriterium mit unterschiedlichen Dropdown Menüs frei wählen zu können, und die Summewenns Formel alles aufsummieren zu lassen, was zu den gewählten Kriterien passt.

Ich erläutere mal an einem Beispiel: Wenn man im Reiter "Daten ohne Anzahlung" für das Konto 51001737 die Kriterien RV und 2019 auswählt, müsste ein Wert von 4355,57 rauskommen, für 2020 ein Wert von 38.677,67.

Ich hänge die Datei nochmal an, in den Spalten C bis E sind drei Formeln, die alle nicht so recht funktionieren, deine letzte inklusive.

Vielen Dank auf jeden Fall, unabhängig davon ob du oder irgendwer sonst noch eine Idee haben.

Viele Grüße















https://www.herber.de/bbs/user/140702.xlsx

Betrifft: AW: hatte schon geschrieben...
von: neopa C
Geschrieben am: 06.10.2020 20:04:18

Hallo narishma_one,

... was ich als Voraussetzung betrachte. Hast dies nur noch nicht entsprechend umsetzen können. Lösche Deine benannten Formeln und beziehe Dich auf die "formatierte Tabelle"!

Nachfolgend mal nur die korrigierte Formel für C2, die anderen analog, sollten Dir dann auch so gelingen.

=SUMMEWENNS(DoA[Betrag in Hauswährung];DoA[Konto];[@Konto];DoA[Belegdatum];WENN(J$2="";"<>"; ">="&("1.1."&J$2)+0);DoA[Belegdatum];WENN(J$2="";"<>";"<="&("1.1."&J$2+1)-1);DoA[Belegart]; WENN(J$3="";"<>";J$3);DoA[Topkunde ?];WENN(J$4="";"<>";J$4);DoA[Geschäftsbereich];WENN(J$5="";"<>"; J$5);DoA[Mahnbereich];WENN(J$6="";"<>";J$6);DoA[Mahnstufe];WENN(J$7="";"<>";J$7))


Gruß Werner
.. , - ...