Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Summenprodukt mit Jahresabfrage
#1
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
ABCDLMSTUVW
1NRWE- DatumAbschlußAbschluß- Datummit "fertig"BearbeitungsdauerAuswertung2012
21000100.01.19001,0012.03.2010abgeschlossen5746,9
31000200.01.19001,0015.02.2010abgeschlossen5743,3Mittelwert30,9
41000300.01.19001,0015.02.2012abgeschlossen5847,6Maximal- Dauer5847,6
51000418.12.20091,0026.02.2012abgeschlossen111,3Summe:687
61000516.02.20101,0025.02.2010abgeschlossen1,3Anzahl%
71000618.12.20091,0010.03.2013abgeschlossen165,3unbekannt34,36681222707424E-03
81000710.01.20101,0013.03.2010abgeschlossen5,9in Bearbeitung81,16448326055313E-02
91000801.03.20101,0017.03.2014abgeschlossen208fertig0
101000904.03.20101,0017.03.2014abgeschlossen207,6<=4Wo4810,700145560407569
111001025.01.20101,0019.03.2010abgeschlossen4,6<=5Wo243,49344978165939E-02
121001119.01.20101,0022.03.2012abgeschlossen110,3<=6Wo284,07569141193595E-02
131001221.01.20101,0022.03.2010abgeschlossen5,6<=7Wo182,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

Antworten Top
#2
Hallo,

ungetestet:

=SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(JAHR($D$2:$D$694=U1)*(1=1)))
Gruß
Peter
Antworten Top
#3
Hi Peter,

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

Die Jahr-Klammern sind falsch Wink

(JAHR($D$2:$D$694)=U1)
Antworten Top
#4
Hallo Boris,

stimmt, danke.
Gruß
Peter
Antworten Top
#5
Hallo,


wie wäre es mit MITTELWERTWENNS:

Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDEFGHIJ
1NR 8D ReportWE- DatumAbschlußAbschluß- Datummit "fertig"BearbeitungsdauerAuswertung 2012 
21000100.01.1900112.03.2010abgeschlossen5746,9    
31000200.01.1900115.02.2010abgeschlossen5743,3  Mittelwert2023,06667
41000300.01.1900115.02.2012abgeschlossen5847,6  Maximal- Dauer5847,6
51000418.12.2009126.02.2012abgeschlossen111,3  Summe:687
61000516.02.2010125.02.2010abgeschlossen1,3   Anzahl
71000618.12.2009110.03.2013abgeschlossen165,3  unbekannt3
81000710.01.2010113.03.2010abgeschlossen5,9  in Bearbeitung8
91000801.03.2010117.03.2014abgeschlossen208  fertig0
101000904.03.2010117.03.2014abgeschlossen207,6<=4Wo481
111001025.01.2010119.03.2010abgeschlossen4,6<=5Wo24
121001119.01.2010122.03.2012abgeschlossen110,3<=6Wo28
131001221.01.2010122.03.2010abgeschlossen5,6<=7Wo18

ZelleFormel
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
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#6
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:

.xlsb   Bearbeitungsdauer.xlsb (Größe: 177,4 KB / Downloads: 5)
Antworten Top
#7
Hallo Ralf,

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

LG Ingo


.xlsb   Bearbeitungsdauer.xlsb (Größe: 144,44 KB / Downloads: 5)
Antworten Top
#8
Hallo,

hier mal eine Formellösung ohne SUMMENPRODUKT:

Arbeitsblatt mit dem Namen 'Datenbank'
 STUVWXYZAAABAC
10<=2Wo5337,86% <=4Wo48369,80%
11> 2 -3Wo1812,86% <=5Wo243,47%
12<=4Wo1410,00% <=6Wo294,19%
13<=5Wo75,00% <=7Wo192,75%

ZelleFormel
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
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#9
Hi Ingo,

(13.11.2014, 11:12)Superingo schrieb: 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!
Antworten Top
#10
Hi Edgar,

(13.11.2014, 11:56)BoskoBiati schrieb: hier mal eine Formellösung ohne SUMMENPRODUKT:

danke, diese Formeln probiere ich morgen auch aus.

Ist das SUMMEWENNS resourcenschonender als das SUMMENPRODUKT?
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste