Erläuterung für Summenprodukt-Formel gesucht

[Rabe]

Hallo zusammen,

ich habe eine Summenproduktformel und verstehe sie nicht mehr:

=SUMMENPRODUKT(([abgeschlossen]abgeschlossen!$H$4:$H$65535="anlegen")*([abgeschlossen]abgeschlossen!$I$4:$I$65535="ja")*([abgeschlossen]abgeschlossen!$L$4:$L$65535=Y$2)*([abgeschlossen]abgeschlossen!$M$4:$M$65535>$V274)*([abgeschlossen]abgeschlossen!$M$4:$M$65535<=$V275))

In Spalte H steht pro Zeile ein Text: "anlegen", "in Bearbeitung", ..., "fertig" oder "abgeschlossen".
Zu jedem Text gibt es in Spalte I ein "ja" oder "nein" oder "offen" und in Spalte M jeweils ein Datum (bei "ja" oder "nein") oder nix (bei "offen").

In Spalte V steht das Anfangs- und Enddatum einer KW.


Ich suche nun mit der Formel die Anzahl der Vorgänge, bei denen zu einem Anlegedatum auch ein Abgeschlossen-Datum in derselben KW liegt.

Macht das die obige Formel?
Ich habe das Gefühl: nein!

Geht das überhaupt?

[BoskoBiati]

Hallo Ralf,

Macht das die obige Formel?

Jein.
Wenn in V274 das Datum vom Wochenanfang steht, dann nein, da das Datum nicht in der Formel enthalten ist.
Allerdings sind auch L und Y2 nicht klar.

[Rabe]

Hi Edgar,

Jein.
Wenn in V274 das Datum vom Wochenanfang steht, dann nein, da das Datum nicht in der Formel enthalten ist.
Allerdings sind auch L und Y2 nicht klar.

dazu:

• V274 ist das Datum des Freitags der Vorwoche und V275 Freitag der zu prüfenden Woche in der Auswertung
  
• Spalte L ist der Bearbeiter in der Übersicht
  
• Y2 ist der Bearbeiter in der Auswertung
  

Ich habe für die verschiedenen Schritte in der Übersicht ja 7 Zeilen. In Zeile 5 steht das Datum für "angelegt" und in Zeile 11 das für "abgeschlossen". Diese 7er Blöcke stehen nun x mal untereinander.

Hier mal alles in einer Mappe, damit es kürzer wird:

Code:

=SUMMENPRODUKT((abgeschlossen!$H$4:$H$65535="anlegen")*(abgeschlossen!$I$4:$I$65535="ja")*(abgeschlossen!$L$4:$L$65535=Y$2)*(abgeschlossen!$M$4:$M$65535>$V274)*(abgeschlossen!$M$4:$M$65535<=$V275))

Wie kann ich nun rauskriegen, ob ein Anlegedatum und das dazugehörige Abschlußdatum in derselben KW liegen (bei gleichem Bearbeiter)?
Sprich: daß Excel bei jedem Anlegedatum in der KW schaut, ob 7 Zeilen drunter das Datum ebenfalls innerhalb dieser KW liegt?


Ich habe jetzt diese Übersicht, weil sie sehr groß und langsam ist, transponiert und in dieser neuen Liste "transponiert" gibt es die Spalten H und I nicht mehr. Dort steht in K der Bearbeiter, in M das Anlegedatum und in N das Abschlußdatum.
KW ist V274 + 1 bis V275

Ich habe dann die Formel folgendermaßen umgestrickt:

Code:

=SUMMENPRODUKT((transponiert!$K$4:$K$65535=Y$2)*(transponiert!$M$4:$M$65535>$V274)*(transponiert!$N$4:$N$65535<=$V275))

da kommen aber andere Zahlen raus als mit der obigen Formel. Deswegen bin ich verunsichert und ich weiß nicht, welche überhaupt richtig zählt. Ich vermute, die erste zählt nicht richtig.
Außerdem fehlt mir noch die Abfrage, ob N leer ist: Geht das so: *(transponiert!$N$4:$N$65535>0)  ?

Weiter:
Statt 65535 hätte ich gerne die letzte belegte.

[Jockel]

Hallo Ralf, du schreibst:" Statt 65535 hätte ich gerne die letzte belegte... " da solltest du mit Namen arbeiten oder mit einer Tabelle (Strg+T). Ich würde das auch in der eigentlichen Datei auswerten und die Ergebnisse holen... ... aber das nur nebenbei... Mir fällt es schwer Beispieldaten zu erfinden, deswegen kann ich dir hier nicht weiterhelfen...

[BoskoBiati]

Hallo,

dann würde ich die Formel etwas umbauen:

Code:

=SUMMENPRODUKT(([abgeschlossen]abgeschlossen!$H$5:$H$65500="anlegen")*([abgeschlossen]abgeschlossen!$I$5:$I$65500="ja")*([abgeschlossen]abgeschlossen!$L$5:$L$65500=Y$2)*([abgeschlossen]abgeschlossen!$M$5:$M$65500>$V274)*([abgeschlossen]abgeschlossen!$M$5:$M$65500<=$V275)*([abgeschlossen]abgeschlossen!$H$11:$H$65507="abgeschlossen"))

Ohne Muster ist das allerdings kaum nachvollziehbar. Für Deine Formel bedarf es allerdings keines "abgeschlossen", da Du das nicht abfragst. Außerdem müssen alle Bedingungen in einer Zeile erfüllt sein!
Bei meiner Formel uß in der ersten Zeile das Datum passen, das "anlegen", das "ja" und der richtige Sachbearbeiter zu finden sein und in der 7.Zeile das Wort "abgeschlossen"

[Rabe]

Hi,

Mir fällt es schwer Beispieldaten zu erfinden, deswegen kann ich dir hier nicht weiterhelfen...

ich habe es mir gedacht, hier ist eine Beispieldatei:

  Report-Datei Forum.xlsb (Größe: 33,76 KB / Downloads: 8)

Das Ergebnis ist nun in beiden Übersichten (zufällig?) gleich. Die Frage ist nur, rechnet die "Übersicht (abgeschlossen)" wirklich mit dem Abschlußdatum 7 Zeilen unter dem Anlegedatum oder nicht?

[BoskoBiati]

Hallo Ralf,

die Formel kann nicht richtig rechnen, da die Eintragungen unter abgeschlossen nicht ausgewertet werden!

Code:

=SUMMENPRODUKT((abgeschlossen!$M$4:$M$40>$V4)*(abgeschlossen!$M$4:$M$40<=$V5)*(abgeschlossen!$H$4:$H$40="anlegen")*(abgeschlossen!$I$4:$I$40="ja")*(abgeschlossen!$L$4:$L$40=AB$2)*(abgeschlossen!$M$10:$M$46>$V4)*(abgeschlossen!$M$10:$M$46<=$V5)*(abgeschlossen!$I$10:$I$46="ja"))

[Jockel]

Hi,
ich habe es mir gedacht, hier ist eine Beispieldatei:
...

Hallo Ralf, ich komme jetzt nicht dazu mir das genauer anzusehen, aber das Erste was ich sehe, ist die falsche Formel für die KALENDERWOCHE()...

[Rabe]

Hi Edgar,

die Formel kann nicht richtig rechnen, da die Eintragungen unter abgeschlossen nicht ausgewertet werden!

ja, genau, darum wundert es mich ja auch, daß zufällig das Ergebnis trotzdem stimmt.

Aber nun mit Deiner Formeländerung passt es, ich mußte nur die V4 und V5 auf V3 und V4 ändern, damit das Datum mit der tatsächlichen KW passt.

[edit]
der letzte Satz war natürlich Mist: Ich muß ja nur die Formel in die Zeile drunter reinkopieren.

[Rabe]

Hi Jörg,

aber das Erste was ich sehe, ist die falsche Formel für die KALENDERWOCHE()...

ja, das ist mir später auch eingefallen, ich habe es nun mit ",21" erweitert.