Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
212to216
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
212to216
212to216
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Spaltensumme wenn

Spaltensumme wenn
02.02.2003 17:29:39
Andreas
Hallo Forum,
Ich habe folgendes Problem. Ich möchte aus einer Spalte(B9:B60) die Summe bilden wenn:
- Zahlenwerte drin stehen,
- wenn Wort1 in der Spalte auftaucht nur bis Wort1(von unten bis Wort1)
- wenn Wort2 in der Spalte auftaucht dann erst wieder wenn Wort1 auftaucht(von unten bis Wort1)
Und das alles in einer Spalte.Ich hoffe Ihr habt es einigermaßen verstanden und könnt mir helfen.
Bis bald




22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Re: Spaltensumme wenn
02.02.2003 17:36:43
Ramses
Hallo Andreas,

Das sollte ungefähr so gehen:

Re: Spaltensumme wenn
02.02.2003 17:45:32
Boris
Hi Andreas,

ich hab´s etwas anders als Rainer verstanden, da ich denke, dass es sich nur um EINE Spalte handelt.
Aber erklär mir den 2. teil doch nochmal oder noch besser, gib mal ein kurzes Beispiel.
Mit BEREICH.VERSCHIEBEN wird es dann funktionieren.

Gruß Boris

Ich muss weg,
02.02.2003 17:54:21
Boris
daher noch ein kleiner Ansatz für Punkt 1:

=SUMME(INDIREKT("B"&VERGLEICH("Hallo";B9:B60;0)+8&":B60"))

Gehe davon aus, dass das Wort - wenn überhaupt - nur einmal vorkommt. Formel ist bei weitem noch nicht ausgegoren, aber wie gesagt, erstmal ein kleiner Ansatz.

Geht auch mit BEREICH.VERSCHIEBEN, aber so is einfacher.

Das Wort ist hier "Hallo".

Gruß Boris

Anzeige
Re: Spaltensumme wenn
02.02.2003 19:24:45
Andreas
Hallo Rainer,
es spielt sich alles in einer Spalte ab.Wenn kein Wort vorkommt soll die Summe ganz normal gebildet werden(B60:B9).Wenn Wort1 vorkommt dann Summe von B60 bis Wort1.Wenn Wort2 vorkommt,dann keine Summe oder Wort1 kommt dannach nochmal,dann wieder Summe von B60 bis Wort1.Die Wörter können beliebig oft vorkommen,es zählt immer das letzte.
Gruß Andreas
Würde ja gern mal ein Beispiel schicken,bekomm das mit der Tabelle anfügen aber nicht hin.

Tut mir leid...
02.02.2003 19:29:22
Ramses
...aber das ist eher etwas für die Array-Spezialisten.

Mal schauen ob Boris da noch was draus machen kann.

Gruss Rainer

Anzeige
Re: Ich muss weg,
02.02.2003 19:39:52
Andreas
Hallo Boris,Rainer konnte leider nicht helfen.
Es spielt sich alles in einer Spalte ab.Wenn kein Wort vorkommt soll die Summe ganz normal gebildet werden(B60:B9).Wenn Wort1 vorkommt dann Summe von B60 bis Wort1.Wenn Wort2 vorkommt,dann keine Summe oder Wort1 kommt dannach nochmal,dann wieder Summe von B60 bis Wort1.Die Wörter können beliebig oft vorkommen,es zählt immer das letzte.
Gruß Andreas
Würde ja gern mal ein Beispiel schicken,bekomm das mit der Tabelle anfügen aber nicht hin.

Re: Spaltensumme wenn
02.02.2003 19:44:17
Boris
Hallo Andreas,

"Wenn Wort2 vorkommt,dann keine Summe oder Wort1 kommt danach nochmal,dann wieder Summe von B60 bis Wort1

Was heisst DANACH: Gehst du jetzt von oben nach unten oder von unten nach oben?

Was ist bei folgendem Beispiel:

B60 bis B50: Jeweils die Zahl 1
B49: Wort2
B48: Wort1

Was soll passieren?

Gruß Boris

Anzeige
Re: Ich muss weg,
02.02.2003 20:00:52
Boris
Hallo Andreas,

ich glaub, ich seh den Wald grad mal wieder vor lauter Bäumen nicht mehr, aber ich grad ne Monsterformel gebastelt, die IMHO zwar funktioniert, aber vielleicht total umständlich ist...

{=(MAX(WENN($B$9:$B$60="Wort1";ZEILE($9:$60)))>MAX(WENN($B$9:$B$60="Wort2";ZEILE($9:$60))))*SUMME(INDIREKT("B"&WENN(ZÄHLENWENN(B9:B60;"Wort1")>0;MAX(WENN($B$9:$B$60="Wort1";ZEILE($9:$60)));9)&":B60"))+(UND(ISTZAHL($B$9:$B$60))*SUMME($B$9:$B$60))}

Array-Formel; die geschweiften Klammern nicht mit eingeben, sondern die Formeleingabe mit Strg-Shift-Enter abschließen. So werden sie automatisch erzeugt.

Probier sie mal aus, aber ich schau noch weiter, denn irgendwie kann ich noch nicht glauben, dass es so lang sein muss...

Gruß Boris

Anzeige
Crazy,... aber genial :-))
02.02.2003 20:08:00
Ramses
Hallo Boris,

da bleibe ich mal lieber bei VBA,... das ist irgendwie übersichtlicher ;-)).

Aber schön, dass es solche Cracks wie dich gibt.

Gruss Rainer

Re: Crazy,... aber genial :-))
02.02.2003 20:16:36
Boris
Hi Rainer,

danke für das Kompliment 8-))
Hab auch nochmal über die Formel nachgedacht:
Da ja doch einige Bedingungen geprüft werden müssen, muss es wohl tatsächlich etwas länger werden...

Aber du hast wohl recht: Mit VBA wär das ne kleine Schleife mit ein paar If-Abfragen - sicherlich übersichtlicher, aber von der Überlegung wahrscheinlich genau gleich wie die Formel.

Bis dahin

Gruß Boris

Re: Spaltensumme wenn
02.02.2003 20:27:41
Andreas
Hallo Boris,
wenn Wort2 kommt dann soll keine summe gebildet werden. Erst wieder wenn Wort1 kommt.
Es soll immer die Summe von unten nach oben gebildet werden, also immer nach dem Wort.
Ich Probiere mal deine Formel aus.
Gruß Andreas
Anzeige
Re: Spaltensumme wenn
02.02.2003 20:36:20
Boris
Hi Andreas,

dann hab ich dein Problem richtig verstanden und die Formel müsste einwandfrei funktionieren.
Denk daran, dass es eine Array-Formel ist...

Gruß Boris

Re: Ich muss weg,
02.02.2003 20:46:56
Andreas
Hallo Boris,
hatte leider kein erfol mit deiner Formel.Ich habe schon mal versucht es über noch zwei spalten zu verknüpfen.(also wennb60="WORT1";0;B60)Wäre bei mir kein problem die zusätzlichen zwei spalten.Bekomm aber nicht hin das er nach wort2 und wort1 wieder zählt.
Andreas
Re: Ich muss weg,
02.02.2003 20:59:53
Boris
Hi Andreas,

dann hast du entweder bei de rEingabe was falsch gemacht oder ich hab dein Prob immer noch nicht ganz verstanden.

Hast du die Formel asl Array-Formel eingegeben?
Die {geschweiften Klammern} werden nicht per Hand eingegeben, sondern die Formeleingabe wird mit Strg-Shift-Enter abgeschlossen. So werden außenrum die Klammern automatisch erzeugt.

Was entspricht denn nicht dem gewünschten Ergebnis? Gib doch einfach mal ein Beispiel an...
In B9 steht,
in B10 steht...
Gewünschtes Ergebnis = ....
etc.

Gruß Boris

Anzeige
Re: Array
02.02.2003 21:35:34
Andreas
Hallo Boris,
sorry hatte fehler bei der Eingabe.Die Formel funktioniert soweit,bis auf das wenn keines der Wörter vorkommt müssen alle zeilen mit werten belegt sein. Ist bei mir aber leider nicht gegeben und überall eine 0 wollte ich vermeiden.
Gruß Andreas
was ist eine Array-Formel???
Re: Array
02.02.2003 21:53:38
Boris
Hi Andreas,

ja deine Anmerkung ist richtig. Daher nimm folgende abgeänderte Version:

{=(MAX(WENN($B$9:$B$60="Wort1";ZEILE($9:$60)))>MAX(WENN($B$9:$B$60="Wort2";ZEILE($9:$60))))*SUMME(INDIREKT("B"&WENN(ZÄHLENWENN($B$9:$B$60;"Wort1")>0;MAX(WENN($B$9:$B$60="Wort1";ZEILE($9:$60)));9)&":B60"))+(UND(NICHT(ISTTEXT($B$9:$B$60)))*SUMME($B$9:$B$60))}

Jetzt kann der Bereich auch leere Zellen enthalten.

Eine Array-Formel wird auch Matrixformel genannt. Viele Probleme lassen sich formelmäßig nicht mit "herkömmlichen" Formeln lösen.
Beispiele dazu findest du in der Recherche und vor allen Dingen bei www.excelformeln.de

Gruß Boris


Anzeige
Re: Funktioniert
02.02.2003 22:09:13
Andreas
Hi Boris,
die Formel ist zwar Hammerlang,aber sie funktioniert einwandfrei.
Ich bin begeistert.
Vielen Dank Andreas
Re: Ich muss weg,
02.02.2003 22:49:41
Panicman
Hallo Andreas, Hallo Sir Boris,

An Boris: da anscheinend deine Formel angeblich nicht richtig rechnet, kann es nur ein Verständnis-Problem sein. Vielleicht muß man "danach Wort1" so interpretieren, das Wort1 (von oben) vor Wort2 stehen (oder nochmals stehen) muß:
Beispiele:
1: keine Worte = Summe(B9:B60)
2: nur Wort2 = keine Summe (0)
3: nur Wort1 in B10;B20;B50 = Summe(B50:B60)
4: Wort2 B40; Wort1 B50 = keine Summe (weil danach (also vor Wort2 keine Wort1 ist)
5: Wort1 B30; Wort2 B40 =Summe(B30:B60)
6: Wort1 B30; Wort2 B40; Wort1 B50 =Summe(B50:B60)

Falls das genau so gemeint war dann versucht mal folgende Monsterformel:

{=WENN(ZÄHLENWENN(B9:B60;"Wort*")=0;SUMME(B9:B60);WENN(UND(ZÄHLENWENN(B9:B60;"Wort1")=0;ZÄHLENWENN(B9:B60;"Wort2")>0);0;WENN(MAX((B9:B60="Wort2")*ZEILE(9:60))>(VERGLEICH("Wort1";B9:B60;0)+8);SUMME(INDIREKT("B"&MAX((B9:B60="Wort1")*ZEILE(9:60))&":B60"));WENN(ZÄHLENWENN(B9:B60;"Wort2")=0;SUMME(INDIREKT("B"&MAX((B9:B60="Wort1")*ZEILE(9:60))&":B60"));"k.A."))))}

Array-Formel; die geschweiften Klammern nicht mit eingeben, sondern die Formeleingabe mit Strg-Shift-Enter abschließen. So werden sie automatisch erzeugt.

Gruß
Holger

P.S. wenns so nicht ist, verstehe ich überhaupt nichts mehr.
Wenns so ist, wer verkürzt bitte die Formel ?

Anzeige
bin wiedermal zu spät uns falsch ;-((( _o.T.
02.02.2003 22:52:10
Panicman

Re: bin wiedermal zu spät uns falsch ;-((( _o.T.
03.02.2003 09:06:00
Boris
Hi Holger,

is doch nicht schlimm, und ich hatte ja auch zunächst Bedenken, ob ich die Aufgabe richtig verstanden habe.
Ich bin aber jetz zu dem Entschluss gekommen, dass man die Formel nicht (oder wenn überhaupt nur unwesentlich) verkürzen kann.
Auf jeden Fall war es mal wieder eine Aufgabe, die Spass gemacht hat, weil man mal wieder richtig überlegen musste;-))

Gruß Boris


Re: bin wiedermal zu spät uns falsch ;-((( _o.T.
03.02.2003 09:28:25
Panicman
Hallo Boris,

danke das du mich wieder aufbaust. Es hat wiklich Spaß gemacht. Das einzige worüber ich mich geärgert habe war, daß ich wiedermal vergessen habe F5 zu drücken. Dann hätte ich gemerkt, daß in der Zeit, in der ich gerechnet habe, die Aufgabe schon gelößt war und mein Ansatz falsch war.

Aber nochmals
Es hat mir auch richtig Spaß gemacht. War eine echte Herausforderung.

Gruß
Holger

Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige