Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 08:41:49
VBA: Probleme mit pdftk zum Zusammenfügen
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1780to1784
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1780to1784
1780to1784
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bedingtes summieren

Bedingtes summieren
07.09.2020 09:18:17
Basti
Hallo alle zusammen,
anbei hab ich euch eine Beispieldatei hochgeladen bei der ich einfach nicht weiter komme. Die Logik im Kopf funktioniert so:
In Zelle F1 soll die Order No. aus Spalte A ausgegeben werden wenn ein bestimmter summierter Wert in Spalte E überschritten wird. Wie so oft funktioniert es im Kopf ganz einfach er sollte in Spalte E einfach immer weiter addieren bis der Grenzwert überschritten wird und dann die dazugehörige Order No. aus Spalte A auslesen und in Zelle F1 ausgeben. Aber in der Praxis sieht die Sache dann leider nicht so einfach aus. Am liebsten wäre mir eine Lösung rein in Excel, sollte sich sowas aber nur mit VBA realisieren lassen würde ich da auch nicht Nein sagen.
Ich hoffe Ihr könnt verstehen was ich meine und mir vielleicht etwas weiter helfen
https://www.herber.de/bbs/user/140081.xlsx
Liebe Grüße Basti

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Summe bis ein Betrag überschritten wird
07.09.2020 10:07:17
WF
Hi,
Arrayformel - Dein "rot" beginnt in dieser Zeile:
{=MAX((SUMMEWENN(A3:A99;"<="&A3:A99;E3:E99)<=B1)*A3:A99)}
Salut WF
AW: Summe bis ein Betrag überschritten wird
07.09.2020 10:29:42
Basti
Hi,
schon mal danke für den Ansatz WF, leider nimmt er die Arrayformel die ich in F1 eingefügt habe nicht als Dynamisch, denn wenn sich nun die Werte in Spalte E ändern hat er immer noch den zuletzt ermittelten Wert der Formel und behält diesen. Mein "rot" ist ja nur als kleine Hilfestellung über eine bedingte Formatierung entstanden. Oder stelle ich mich nun grade einfach zu blöd an und habe das sprichwörtliche "Brett vorm Kopf". Kurze Aufschlüsselung: in F1 möchte ich die Bestellnummer aus Spalte A eingetragen bekommen wenn mit der Wertsumme der Aufträge aus Spalte E der Grenzwert in diesem Beispiel 10.000,00 überschritten wird. Die Arrayformel gibt mir allerdings nur die Zeile in der der Wert ermittelt war wieder als 20 als der Wert in Zeile 20 erreicht wurde.
Ich hoffe man kann verstehen was ich hier zu Bildschirm gebracht habe.
LG Basti
Anzeige
Da hast Du Recht
07.09.2020 10:50:17
WF
Hi,
die Crux sind die Leerzellen in Spalte A
Mal auf die Schnelle: schreib in Hilfsspalte R ab R3 die Zahlen 3 - 4 - 5 usw. runter
{=MAX((SUMMEWENN(R3:R99;"<="&R3:R99;E3:E99)<=B1)*R3:R99)}
WF
AW: Da hast Du Recht
07.09.2020 11:00:48
Basti
Hi WF,
gut mit der Hilfspalte klappt es nun schon mal das er es aktualisiert allerdings wäre es jetzt noch schön wenn er mir in F1 nicht die Zeilennummer wenn es erreicht ist ausgibt sondern die Bestellnummer die in Spalte A der jeweiligen Zeile steht. Damit wäre es dann perfekt.
Aber das hat mir schon mal sehr geholfen Ich tüftle auch schon die ganze Zeit daran rum aber es ist einfach schon zu lange her das ich mich mit Excel so intensiv beschäftigt habe, in der heutigen Zeit wird einem das meiste ja schon von sämtlichen anderen Programmen abgenommen.
LG Basti
Anzeige
das ist ja nun einfach
07.09.2020 11:25:44
WF
{=MAX(A1:INDIREKT("A"&MAX((SUMMEWENN(R3:R99;"<="&R3:R99;E3:E99)<=B1)*R3:R99)))}
WF
AW: eine Lösungsformel ohne Hilfsspalte ...
07.09.2020 11:49:15
neopa
Hallo Basti,
... wäre dann z.B. mit Matrixformeleingabeabschluss wie folgt möglich
{=VERWEIS(999;A1:INDEX(A:A;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(E3;;;ZEILE(A1:A99)))&lt=B1);ZEILE(A3:A99)))))}
Gruß Werner
.. , - ...
AW: Bedingtes summieren
08.09.2020 09:18:54
Basti
Hallo alle zusammen,
da bin Ich auch schon wieder, die Lösungen gestern waren schon überragend und ich wollte mich dafür nochmal bedanken aber nach dem ersten Gestern hab ich festgestellt das ich in meiner Überlegung etwas entscheidendes vergessen habe. und zwar Spalte "M" die "received Payments" gegen zu rechnen um das "Creditlimit" zu beeinflussen. Vielleicht mag mir ja jemand noch mal helfen das einzubauen in die Formel von
SF: 

{=MAX(A1:INDIREKT("A"&MAX((SUMMEWENN(R3:R99;"

oder von
Neopa: 

{=VERWEIS(999;A1:INDEX(A:A;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(E3;;;ZEILE(A1:A99)))
Und in Zelle H1 sollte nach Möglichkeit der ausgelesene Wert um den das "Creditlimit" mit der "Order" überstiegen wird eingetragen werden.
anbei stelle ich euch nochmal die Excel zur Verfügung.
https://www.herber.de/bbs/user/140111.xlsx
Liebe Grüße und noch Mal ganz Viel Dank
Basti
Anzeige
AW: Dein neues Anliegen interpretiere ich ...
08.09.2020 10:18:28
neopa
Hallo Basti,
... so, dass in meiner Formel lediglich der Wert aus M4 dem Wert in B1 dazu addiert werden muss.
Also so: =VERWEIS(... &lt=B1+M4)...)
Oder gibt es mehrere Werte in Spalte M?
Gruß Werner
.. , - ...
AW: Dein neues Anliegen interpretiere ich ...
08.09.2020 10:22:56
Basti
Hallo Werner,
Der Grundgedanke ist richtig aber natürlich wird es in Spalte M wohl mehrere Werte geben. bzgl. Teilzahlung usw. mit nur einem Wert wäre es relativ einfach und die Werte sollten dann nicht dazu addiert werden sondern subtrahiert, da sich durch eine beglichene Rechnung natürlich das Kreditlimit wieder erhöht.
trotzdem schon mal danke.
LG Basti
Anzeige
AW: dazu nachgefragt ...
08.09.2020 10:33:48
neopa
Hallo Basti
... wieso hast Du Deine bedingte Formatierung nach Order Nr 18 anzeigen lassen. Dies wäre nur der Fall, wenn Du den Wert in Spalte M zu B1 addierst.
Wenn es aber subtrahiert werden soll, dann ist jeder Wert in Spalte M als Minus-Wert zu berücksichtigen?
Gruß Werner
.. , - ...
AW: dazu nachgefragt ...
08.09.2020 10:40:10
Basti
Hallo Werner,
Ja der Wert in Spalte M ist grundlegend negativ zu betrachten da es sich um eingehende Zahlungen zu den in Spalte J befindlichen Rechnungen handelt und somit natürlich die Differenz bis zum erreichen des Limits wieder erhöhen.
Und ja ich bin im Moment wie gesagt noch im Aufbau und tüfteln war im ersten Moment froh das ich mich überhaupt wieder an halbwegs etwas zu den Möglichkeiten von Excel erinnert habe.
Gruß Basti
Anzeige
AW: in dem Fall ...
08.09.2020 10:54:20
neopa
Hallo Basti,
... würde ich der Einfachheit halber doch eine Hilfsspalte einführen.
In S3: =[@[Total Orders]]-[@[received payments]]
Und dann in H1 einfach nachfolgende kleine Formeländerung
=VERWEIS(999;A1:INDEX(A:A;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(S3;;;ZEILE(A1:A99))) &lt=B1); ZEILE(A3:A99))))) }
Gruß Werner
.. , - ...
AW: in dem Fall ...
08.09.2020 11:44:38
Basti
Hallo Werner,
da hab ich mich glaube ich etwas unklar Ausgedrückt, in Zelle H1 hätte ich gerne den Betrag ausgegeben um den Das Kreditlimit mit der vorher ermittelten Bestellung überschritten wurde.
anbei nochmal die Excel Datei mit einem laienhaften Beispiel.
https://www.herber.de/bbs/user/140117.xlsx
Liebe Grüße Basti
Anzeige
AW: das ist schon was anderes, wenn auch ...
08.09.2020 13:16:20
neopa
Hallo Basti,
... mit einem gleichen Lösungsansatz realisierbar. Nachfolgend hab ich in der Datei die Hilfsspalte R gelöscht, so dass meine Hilfsspalte nunmehr die Spalte R ist.
Dann so:
Arbeitsblatt mit dem Namen 'Tabelle1'
 H
1832,75 €
20,01 €

ZelleFormel
H1{=INDEX(E:E;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(R4;;;ZEILE(A1:A99))) &lt=B2); ZEILE(A4:A99)))+1)}
H2{=SUMME(E4:INDEX(E:E;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(R4;;;ZEILE(A1:A99))) &lt=B2); ZEILE(A4:A99)))+1))-B2}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: Ergänzung ...
08.09.2020 13:29:22
neopa
Hallo nochmal,
... die Formel in H1: sollte so definiert werden {=WENN(R64&ltB2;"";...)} und dementsprechend auch in H2: {=WENN(H1="";"";...)}
Gruß Werner
.. , - ...
AW: Ergänzung ...
08.09.2020 13:47:09
Basti
Hallo Werner,
ganz ganz lieben Dank für Die Lösung. Das ist wirklich Spitze was da aufs Papier gebracht wurde und funktioniert Super.
Liebe Grüße Basti
AW: danke für die "Blumen" owT
08.09.2020 13:51:06
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige