In Spalten suchen, Werte auswählen und Summe bilde

Bild

Betrifft: In Spalten suchen, Werte auswählen und Summe bilde von: Mathias
Geschrieben am: 03.03.2005 10:26:21

Hallo,

ich hab mal ein kleines Problem, ich hoffe ihr könnt mir helfen, ist aber sehr knifflig.
In meiner Excel Datei befinden sich 2 Tabellenblätter.
In TB1 in Zelle E9 und E10 muss jeweils eine Summe gebildet werden.
und zwar anhand von folgenden Kriterien:
In TB2 im Bereich B29:B274 steht entweder eine 10 oder eine 20
In TB2 im Bereich F29:F274 stehen die Referenzzahlen.

Nun soll der Bereich B29 bis B274 durchsucht werden und für jede Zelle wo eine 10 steht, soll die Zahl der gleichen Zeile aus Spalte F summiert werden, bis auf den letzten Wert im Bereich B29 bis B274 wo eine 10 steht, diese Zahl darf aus Spalte F nicht mit in der Summe berechnet werden.

Die selbe gilt für die Zelle E10 auf TB1 nur steht hier halt eine 20 im TB2 im Bereich B29:B274.

vielen Dank im vorraus
Mathias

PS: Die Positionen der Zahlen 10 und 20 im TB2 ändern sich des öfteren.

Bild


Betrifft: Summe bei 10 bis auf die letzte Zelle von: WF
Geschrieben am: 03.03.2005 10:49:07

Hi Mathias,

folgende Array-Formel:
{=SUMMEWENN(Tabelle2!B29:B274;10;Tabelle2!F29:F274)-INDIREKT("F"&MAX((Tabelle2!B29:B274=10)*ZEILE(29:274)))}

ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluß der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.

trapp, trapp, trapp, trapp, trapp, brrrrr
WF

http://www.excelformeln.de/
die ultimative Formelseite


Bild


Betrifft: oder ohne Array von: WF
Geschrieben am: 03.03.2005 11:02:32

=SUMMEWENN(Tabelle2!B29:B274;10;Tabelle2!F29:F274)-INDIREKT("F"&VERWEIS(2;1/(B29:B274=10);ZEILE(B29:B274)))


Bild


Betrifft: oder so... von: IngoG
Geschrieben am: 03.03.2005 12:19:14

Hallo zusammen,

nur der Vollständigkeit halber ;-)

=SUMMENPRODUKT((Tabelle2!B29:B274=10)*(ZEILE(Tabelle2!B29:B274)<MAX((Tabelle2!B29:B274=10)*ZEILE(Tabelle2!B29:B274)))*Tabelle2!F29:F274)



Gruß Ingo


Bild


Betrifft: ohne Array und ohne INDIREKT von: WF
Geschrieben am: 03.03.2005 12:16:50

=SUMMEWENN(Tabelle2!B29:B274;10;Tabelle2!F29:F274)-VERWEIS(2;1/(Tabelle2!B29:B274=10);Tabelle2!F29:F274)

WF


Bild


Betrifft: AW: ohne Array und ohne INDIREKT von: Mathias
Geschrieben am: 03.03.2005 12:46:26

Hallo WF super vielen Dank hat 100%tig geklappt,

leider hab ich einen Fehler gemacht, der erste Werte muss abgezogen werden und nicht der letzte in Spalte F ist das auch machbar?

herzlichen Dank
Mathias



Bild


Betrifft: Summe bei 10 ohne den ersten von: WF
Geschrieben am: 03.03.2005 13:05:37

Hi Mathias,

da muß dann aber wieder eine Array-Formel herhalten:
{=SUMMEWENN(Tabelle2!B29:B274;10;Tabelle2!F29:F274)-INDIREKT("F"&MIN(WENN(Tabelle2!B27:B274=10;ZEILE(27:274))))}

Salut WF


Bild


Betrifft: AW: Summe bei 10 ohne den ersten von: Mathias
Geschrieben am: 03.03.2005 13:10:52

Hi WF,

echt klasse funktioniert natürlich einwandfrei, schönes WE und vielen Dank
Mathias :-)


Bild


Betrifft: AW: Summe bei 10 ohne den ersten von: Mathias
Geschrieben am: 03.03.2005 14:06:10

HI WF,

habe gerade alles nochmal getestet, funktioniert aber irgendwie doch nicht richtig. :-(
habe die Datei mal hochgeladen. Vielleicht kannst du dir das ja mal anschauen, währe nett. Erreichen muss ich eigentlich, das im TB1 das Ergebnis aus TB2 (habe ich rot markiert und grün hinterlegt) in TB1 auftaucht.

vielen Dank für die vielen Mühen
Mathias
https://www.herber.de/bbs/user/19157.xls


Bild


Betrifft: falsches tabellenblatt!!! von: IngoG
Geschrieben am: 03.03.2005 14:21:00

Hallo Ihr beiden

Dz Mußt in der Indirekt Funktion auch auf das Tabellenblatt 2 zugreifen, also:

{=SUMMEWENN(Tabelle2!B29:B274;10;Tabelle2!F29:F274)-INDIREKT("Tabelle2!F"&MIN(WENN(Tabelle2!B27:B274=10;ZEILE(27:274))))}

Das sollte es schon sein...

Gruß Ingo


Bild


Betrifft: AW: Array?? von: Mathias
Geschrieben am: 03.03.2005 15:00:31

Hallo Ingo, das wars leider nicht, hast du mal die Datei angeschaut??


Bild


Betrifft: Ingo hat schon recht ! von: WF
Geschrieben am: 03.03.2005 15:19:32

so muß es lauten:

{=SUMMEWENN(MaterialIST!$B$29:$B$3000;A9;MaterialIST!$F$29:$F$3000)-INDIREKT("MaterialIST!F"&MIN(WENN(MaterialIST!$B$29:$B$3000=A9;ZEILE(29:3000))))}

WF


Bild


Betrifft: AW: Ingo hat schon recht ! von: Mathias
Geschrieben am: 03.03.2005 15:30:39

Hallo Ihr beiden,

erstmal vielen Dank für die Unterstützung,
aber warum erhalte ich dann als Ergebnis in TB1 ein anderes als das gewünschte aus TB2 in Zelle B29?
oder liegt das an meiner Excel Version 97?

nochmal Danke


Bild


Betrifft: Als Ergebnis kommt 141.171,36 raus und ... von: WF
Geschrieben am: 03.03.2005 15:49:22

das ist auch richtig so.

WF


Bild


Betrifft: AW: Als Ergebnis kommt 141.171,36 raus und ... von: Mathias
Geschrieben am: 03.03.2005 16:04:03

Hallo, sorry ich war wohl etwas neben mir,
ich hatte ja noch einen wichtigen Punkt vergessen für die Berechnung und zwar die Spalte C in TB2
das heisst die Formel müsste ungefähr so aussehen:
=SUMMEWENN(MaterialIST!$B$29:$B$3000 UND####;A9;MaterialIST!$F$29:$F$3000)}
für ### sollte stehen das nur berechnet wird wenn in der Spalte C zusätzlich das Wort "Ergebnis" steht, dann kann die minus Berechnung am Ende auch weggelassen werden.
was für eine schwere Geburt
https://www.herber.de/bbs/user/19157.xls

Ich hoffe Ihr könnt mir das nachsehen
vielen herzlichen Dank
Mathias


Bild


Betrifft: AW: Ingo hat schon recht ! von: ingoG
Geschrieben am: 03.03.2005 15:52:15

Hallo Mathias,

die anforderung, alle Zahlen in spalte F ausser der ersten zu summieren, die den angegeben Schlüssel in spalte B haben ist durch die formel erfüllt.
einfach mit summewenn nachrechnen und den ersten wert abziehen...;-)

ob das aber der wert ist, den du sehen möchtest ist eine andere Frage.
uU sind ja weitere summierungen in der spalte F, die nicht berücksichtigt werden dürfen.

dann müsstest du dich aber nochmal melden.

Gruß Ingo


Bild


Betrifft: AW: Ingo hat schon recht ! von: Ibe
Geschrieben am: 03.03.2005 16:50:01

Hallo, sorry ich war wohl etwas neben mir,
ich hatte ja noch einen wichtigen Punkt vergessen für die Berechnung und zwar die Spalte C in TB2
das heisst die Formel müsste ungefähr so aussehen:
=SUMMEWENN(MaterialIST!$B$29:$B$3000 UND####;A9;MaterialIST!$F$29:$F$3000)}
für ### sollte stehen das nur berechnet wird wenn in der Spalte C zusätzlich das Wort "Ergebnis" steht, dann kann die minus Berechnung am Ende auch weggelassen werden.
was für eine schwere Geburt
https://www.herber.de/bbs/user/19157.xls

Ich hoffe Ihr könnt mir das nachsehen
vielen herzlichen Dank
Mathias


Bild


Betrifft: Frage beendet !!!??? von: IngoG
Geschrieben am: 04.03.2005 10:29:29

Hallo Mathias,

ich glaube du solltest zu dem Thema eine neue Frage stellen,
schildere nochmal genau dein Problem und gib eine Solllösung für deine Beispieldatei an.
Mittlerweile ist es ja ein ganz anderes Problem als am Anfang des Threats.

Gruß Ingo


Bild


Betrifft: Ja ist beendet, diese Lösung habe ich gesucht, von: Mathias
Geschrieben am: 04.03.2005 13:11:42


{=SUMME((MaterialIST!$B$29:$B$3000=LZBericht!A9)*(MaterialIST!$C$29:$C$3000="Ergebnis")*(MaterialIST!$F$29:$F$3000))}

vielen herzlichen Dank an alle die mir geholfen haben, besonders an Ingo und WF


 Bild

Beiträge aus den Excel-Beispielen zum Thema "In Spalten suchen, Werte auswählen und Summe bilde"