nur gefilterte Werte über anderes Tabellenblatt summieren

[bosse82]

Nachdem ich vorher schon kompetent beraten wurde hätte ich noch eine letzte Frage

Ich summiere Werte über einzelne Tabellenblätter und gebe diese dann auf einem Ergebnis-Tabellenblatt anzeigen. Funktioniert nun wunderbar.
=SUMMEWENN(Lieferungen!$B$2:$B$9999;[B.APLNR];Lieferungen!$C$2:$C$9999)

Problem: ich will die Tabellenblätter filtern und nur das gefilterte dann in das Ergebnis-Tabellenblatt übertragen.

Als Beispiel:
Tabellenblatt Lieferung: dort stehen alle Einzelsätze aller Lieferungen mit Artikel, Datum und Menge
4711 am 08.01.14 mit 1000 Stück
4711 am 01.01.15 mit 1200 Stück
4711 am 13.01.14 mit 1500 Stück
4712 am 12.01.14 mit 1200 Stück
4712 am 20.01.14 mit 1400 Stück
4712 am 04.01.15 mit 1800 Stück
usw.

Ohne Filterung habe ich per SUMMEWENN()-Formel folgendes Ergebnis:
4711: 3700 Stück
4712: 4400Stück

Nun kommen die Filter zum Einsatz. z.B. Filterung auf das Jahr 2014. Es sollte nun nur noch die gefilterten Werte in der Formel berücksichtigen. Also im Bsp. wie folgt:
4711: 2700 Stück
4712: 2600 Stück

Ich hoffe es gibt hierfür auch eine Lösung...

[Kl@us-M.]

Hi,

schau Dir mal die Funktion =TEILERGEBNIS() an ...

[Peter]

Hallo,

nach meinem Verständnis des Postings könnte man auch mit Summenprodut

Ungestetet: =SUMMENPRODUKT((Lieferungen!$B$2:$B$9999="[B.APLNR]")*(JAHR(Lieferungen!$A$2:$A$9999)=2014)*(Lieferungen!C2:C9999))

oder mit einer Pivottabelle arbeiten.

[Rabe]

Hi,

Ungestetet: =SUMMENPRODUKT((Lieferungen!$B$2:$B$9999="[B.APLNR]")*(JAHR(Lieferungen!$A$2:$A$9999)=2014)*(Lieferungen!C2:C9999))

oder mit einer Pivottabelle arbeiten.

bei SUMMENPRODUKT denke ich immer sofort an Pivot-Tabelle.

Das dürfte hier auch besser sein, denn wenn das die Statistik über die Produktion ist, werden das einige Zeilen werden und mit der Matrixformel SUMMENPRODUKT könnte das langsam werden.

[Peter]

Hallo,

bei SUMMENPRODUKT denke ich immer sofort an Pivot-Tabelle.

Nicht nur du. :19

[bosse82]

Danke für die Antworten. Eine Pivot hilft nachher nicht weiter weil es nachher eine Tabelle mit mehreren Werten ergibt.

Art, Bezeichnung, Index, Maschine, Material (Menge), Material (Wert) uvm.... Liefermenge (Stück), Liefermenge (Wert), Gutmenge (Stück), Gutmenge (Wert), Auschussmenge (Stück), Ausschuss (Wert)

Weiterhelfen würde mir die Funktion TEILERGEBNIS() allerdings nur in Kombination mit der SUMMEWENN()-Funktion.

=SUMMEWENN(Tabelle_lieferungen_einzel[P.ARTNR];[B.APLNR];Tabelle_lieferungen_einzel[P.MENGE])
Ich habe nun an allen Stellen versucht TEILERGEBNIS() einzubauen - erfolglos.
Meines Erachtens müsste diese Funktion im 3. Teil (Summenbereich) untergebracht werden, also folgendermaßen:
=SUMMEWENN(Tabelle_lieferungen_einzel[P.ARTNR];[B.APLNR];TEILERGEBNIS(9;Tabelle_lieferungen_einzel[P.MENGE])).

Das mag Excel aber nicht :@

[bosse82]

Noch ein Anmerkung: SUMMEPRODUKT() will ich nicht unbedingt verwenden weil der Endanwender dann in der Funktion das Datum eingeben müsste was zum einen nicht anwenderfreundlich und zum anderen auch nicht einfach ist. Bsp. alle Datensätze des Jahres von 2014 würde bedeuten man müsste darin auf von und bis eingrenzen weil jeder Datensatz ein Datenstempel beinhaltet (2014-05-18 12:20) wobei das Jahr nur ein Teil davon ist.

[Peter]

Hallo,

Code:

Datenstempel beinhaltet (2014-05-18 12:20)

wenn das von XL als Text erkannt wird dann könnte man in der Summenproduktformel z.B. mit der Funktion Links() arbeiten:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	2014-05-18 12:20	5	2014	10
2	2015-05-18 12:20	6
3	2014-12-18 12:20	5
4

Zelle	Formel
D1	=SUMMENPRODUKT((LINKS(A1:A10;4)=C1)*(B1:B10))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Den gesuchten Parameter kann man in eine Zelle schreiben, hier C1. Das funktioniert sowohl wenn das Datum von XL als Text angesehen wird, als auch als Zahl. Man muss nur darauf achten, dass der Suchparameter in C1 das gleiche Format hat.

[Rabe]

wenn das von XL als Text erkannt wird dann könnte man in der Summenproduktformel z.B. mit der Funktion Links() arbeiten:

oder einfach mit JAHR():

Tabelle1

	A	B	C	D	E
1	18.05.2014 12:20	5	2014	0	10
2	18.05.2015 12:20	6
3	18.12.2014 12:20	5
4

verwendete Formeln
Zelle	Formel	Bereich	N/A
D1	=SUMMENPRODUKT((LINKS(A1:A10;4)=C1)*(B1:B10))
E1	=SUMMENPRODUKT((JAHR(A1:A10)=C1)*(B1:B10))

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.19 einschl. 64 Bit

[Peter]

Hallo Ralf,

stimmt, in dem von mir angedachten Fall ist die Funktion Links() gar nicht notwendig, da reicht die Funktion Jahr().