Summenprodukt mit Jahresabfrage

[Rabe]

Hi zusammen,

wie kann ich in die Formel V10-V76 noch die Auswahl des Jahres einbauen, daß also nur die gezählt werden, deren Abschlußdatum z.B. im Jahr 2012 (U1) liegt:

Datenbank

	A	B	C	D	L	M	S	T	U	V	W
1	NR	WE- Datum	Abschluß	Abschluß- Datum	mit "fertig"	Bearbeitungsdauer	Auswertung		2012
2	10001	00.01.1900	1,00	12.03.2010	abgeschlossen	5746,9
3	10002	00.01.1900	1,00	15.02.2010	abgeschlossen	5743,3			Mittelwert	30,9
4	10003	00.01.1900	1,00	15.02.2012	abgeschlossen	5847,6			Maximal- Dauer	5847,6
5	10004	18.12.2009	1,00	26.02.2012	abgeschlossen	111,3			Summe:	687
6	10005	16.02.2010	1,00	25.02.2010	abgeschlossen	1,3				Anzahl	%
7	10006	18.12.2009	1,00	10.03.2013	abgeschlossen	165,3			unbekannt	3	4,36681222707424E-03
8	10007	10.01.2010	1,00	13.03.2010	abgeschlossen	5,9			in Bearbeitung	8	1,16448326055313E-02
9	10008	01.03.2010	1,00	17.03.2014	abgeschlossen	208			fertig	0
10	10009	04.03.2010	1,00	17.03.2014	abgeschlossen	207,6	<=	4	Wo	481	0,700145560407569
11	10010	25.01.2010	1,00	19.03.2010	abgeschlossen	4,6	<=	5	Wo	24	3,49344978165939E-02
12	10011	19.01.2010	1,00	22.03.2012	abgeschlossen	110,3	<=	6	Wo	28	4,07569141193595E-02
13	10012	21.01.2010	1,00	22.03.2010	abgeschlossen	5,6	<=	7	Wo	18	2,62008733624454E-02

verwendete Formeln
Zelle	Formel	Bereich	N/A
V3	=MITTELWERT($M$2:$M$694)
V4	=MAX($M$2:$M$694)
V5	=SUMME(V7:V76)
V8	=SUMMENPRODUKT(($L$2:$L$694="offen")*(1=1))
V9	=SUMMENPRODUKT(($L$2:$L$694="fertig")*(1=1))
V10	=SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(1=1))
L2:L13	=WENN(C2="";"";WENN(C2=1;"abgeschlossen";WENN(C2=0,875;"fertig";"offen")))
M2:M13	=P2
V11:V13	=SUMMENPRODUKT(($M$2:$M$694>T10)*($M$2:$M$694<=T11))
W7:W13	=WENN(V7/$V$5>0;V7/$V$5;"")

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,

ungetestet:

=SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(JAHR($D$2:$D$694=U1)*(1=1)))

[{Boris}]

Hi Peter,

ungetestet: =SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(JAHR($D$2:$D$694=U1)*(1=1)))

Die Jahr-Klammern sind falsch

(JAHR($D$2:$D$694)=U1)

[Peter]

Hallo Boris,

stimmt, danke.

[BoskoBiati]

Hallo,


wie wäre es mit MITTELWERTWENNS:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G	H	I	J
1	NR 8D Report	WE- Datum	Abschluß	Abschluß- Datum	mit "fertig"	Bearbeitungsdauer	Auswertung		2012
2	10001	00.01.1900	1	12.03.2010	abgeschlossen	5746,9
3	10002	00.01.1900	1	15.02.2010	abgeschlossen	5743,3			Mittelwert	2023,06667
4	10003	00.01.1900	1	15.02.2012	abgeschlossen	5847,6			Maximal- Dauer	5847,6
5	10004	18.12.2009	1	26.02.2012	abgeschlossen	111,3			Summe:	687
6	10005	16.02.2010	1	25.02.2010	abgeschlossen	1,3				Anzahl
7	10006	18.12.2009	1	10.03.2013	abgeschlossen	165,3			unbekannt	3
8	10007	10.01.2010	1	13.03.2010	abgeschlossen	5,9			in Bearbeitung	8
9	10008	01.03.2010	1	17.03.2014	abgeschlossen	208			fertig	0
10	10009	04.03.2010	1	17.03.2014	abgeschlossen	207,6	<=	4	Wo	481
11	10010	25.01.2010	1	19.03.2010	abgeschlossen	4,6	<=	5	Wo	24
12	10011	19.01.2010	1	22.03.2012	abgeschlossen	110,3	<=	6	Wo	28
13	10012	21.01.2010	1	22.03.2010	abgeschlossen	5,6	<=	7	Wo	18

Zelle	Formel
J3	=MITTELWERTWENNS($F$2:$F$13;$D$2:$D$13;">=1.1."&I1;$D$2:$D$13;"<=31.12."&I1)

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

[Rabe]

Hi Peter und Boris,

danke.
Die Formel hatte ich auch schon, aber durch kopieren nach unten wurden die Zahlen immer größer.
Ich hatte übersehen, daß ja ab V11 nach unten eine andere Formel verwendet war. Nun passt es!

Könnte man das auch per Pivot machen? Wie bekäme ich da den Vergleich mit den Wochen rein?

Hier die Datei:

  Bearbeitungsdauer.xlsb (Größe: 177,4 KB / Downloads: 5)

[Superingo]

Hallo Ralf,

anbei mein Lösungsvorschlag mit Pivot.
Dazu habe ich in der Datenbank die Spalten G und R mit Formeln versehen.

LG Ingo


  Bearbeitungsdauer.xlsb (Größe: 144,44 KB / Downloads: 5)

[BoskoBiati]

Hallo,

hier mal eine Formellösung ohne SUMMENPRODUKT:

Arbeitsblatt mit dem Namen 'Datenbank'
	S	T	U	V	W	X	Y	Z	AA	AB	AC
10	<=	2	Wo	53	37,86%		<=	4	Wo	483	69,80%
11	> 2 -	3	Wo	18	12,86%		<=	5	Wo	24	3,47%
12	<=	4	Wo	14	10,00%		<=	6	Wo	29	4,19%
13	<=	5	Wo	7	5,00%		<=	7	Wo	19	2,75%

Zelle	Formel
V10	=ZÄHLENWENNS(Datenbank!$M$2:$M$694;"<="&T10;Datenbank!$M$2:$M$694;"<>"&"";Datenbank!$D$2:$D$694;">=1.1."&$V$1;Datenbank!$D$2:$D$694;"<=31.12."&$V$1)
V11	=ZÄHLENWENNS(Datenbank!$M$2:$M$694;"<="&T11;Datenbank!$M$2:$M$694;">"&T10;Datenbank!$M$2:$M$694;"<>"&"";Datenbank!$D$2:$D$694;">=1.1."&$V$1;Datenbank!$D$2:$D$694;"<=31.12."&$V$1)
W10	=WENN(V10>0;V10/$V$5;"")
AB10	=ZÄHLENWENNS($M$2:$M$694;"<="&Z10;$M$2:$M$694;"<>"&"")
AB11	=ZÄHLENWENNS($M$2:$M$694;"<="&Z11;$M$2:$M$694;"<>"&"")-SUMME($AB$10:AB10)
AC10	=WENN(AB10>0;AB10/$AB$5;"")

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

[Rabe]

Hi Ingo,

anbei mein Lösungsvorschlag mit Pivot.
Dazu habe ich in der Datenbank die Spalten G und R mit Formeln versehen.

das sieht sehr gut aus mit Pivot. Ich habe sie mir dann noch erweitert, indem ich die Jahre noch in die Spaltenbeschriftung gezogen habe.

Danke!

[Rabe]

Hi Edgar,

hier mal eine Formellösung ohne SUMMENPRODUKT:

danke, diese Formeln probiere ich morgen auch aus.

Ist das SUMMEWENNS resourcenschonender als das SUMMENPRODUKT?