Anzahl Zellen mit "0" innerhalb eines dynamischen Bereiches

[Daniel242]

Hallo zusammen,

ich baue gerade an einer kleine Arbeitszeitübersicht von Personal für ein Projekt.
Hierbei werden Werte aus einem Schichtplan übernommen. Nun möchte ich wissen, wie viele Tage die Kolleg*innen innerhalb ihres Einsatzzeitraumes frei haben.

In Spalte D hätte ich das gern hinterlegt. So wie ich es jetzt habe, zählt er leider den gesamten Zeitraum - nicht den Einsatzzeitraum.

Max Mustermann hat aber seinen 1. Tag am 25.08. und den letzten Tag am 27.08. - hier möchte ich nur den Einsatzzeitraum betrachtet haben, also hätte er 0 freie Tage.
Ute Musterfrau soll 3 freie Tage in D6 angezeigt bekommen. Sie fängt am 24.08. an und hört am 09.09. auf.
Bernd Schmidt hätte entsprechend 2 Tage frei, bei einem Einsatz vom 30.08.-05.09.

Ich hoffe das ist verständlich?

Kann mir hier jemand mit einer Formel helfen?

Danke! Daniel

[Ego]

Hallo Daniel ,

zB. so:

ZÄHLENWENNS(F7:W7;0;$F$3:$W$3;">="&E7)

[BoskoBiati]

Hallo Daniel,

und wo finde ich den letzten Einsatztag?

@helmut,

guter Ansatz, aber falsch.

[Daniel242]

@BoskoBiati: das müsste leider berechnet werden - sofern das möglich ist. Im Prinzip der letzte Tag in dem ein Wert drin steht.
Den ersten Tag in habe ich auch durch eine Formel anzeigen lassen - habe ich nur in dem Beispiel nicht drin gehabt: 
=INDEX(F$3:W$3;VERGLEICH(WAHR;F5:W5>0;0))
Vielleicht hast du ne Idee, wie der letzte Tag auszurechnen ist?

[BoskoBiati]

Hi,

eine Möglichkeit:

Code:

=LET(xa;F5:W5;xb;AGGREGAT(15;6;SPALTE(xa)/(xa>0);1)-5;xc;AGGREGAT(14;6;SPALTE(xa)/(xa>0);1)-5;ZÄHLENWENN(INDEX(xa;xb):INDEX(xa;xc);0))

[Daniel242]

Wahnsinn - da denkt man immer, es kann doch nicht so kompliziert sein und dann sieht der Code aus, als könnte man damit Raketen zum Mars fliegen.
An Aggregat hatte ich auch schon gedacht und kam nicht weiter.
Die Formel passt perfekt - VIELEN DANK! Du hast mir sehr geholfen!

[{Boris}]

Hi,

die LET-Konstruktion macht die Formel im konkreten Beispiel nur länger.
Zudem ist die Nachvollziehbarkeit nicht gegeben, da man Teile der Formel nicht mit F9 auswerten kann.

Ohne LET - zudem ein wenig angepasst ohne Subtraktion von 5:

=ZÄHLENWENN(INDEX(F5:W5;AGGREGAT(15;6;SPALTE(A:R)/(F5:W5>0);1)):INDEX(F5:W5;AGGREGAT(14;6;SPALTE(A:R)/(F5:W5>0);1));0)

AGGREGAT(15;6;SPALTE(A:R)/(F5:W5>0);1)
findet die erste Spalte im Bereich F5:W5, in der der Wert größer Null ist (15 als erster Parameter = KKLEINSTE)

AGGREGAT(14;6;SPALTE(A:R)/(F5:W5>0);1)
findet die letzte Spalte im Bereich F5:W5, in der der Wert größer Null ist (14 als erster Parameter = KGRÖSSTE)

Beide Werte werden jeweils an INDEX übergeben - und aus beiden INDEX ein zusammenhängender Bereich erstellt, der mittels ZÄHLENWENN auf Null geprüft wird.

Das nur für Dich zur besseren Nachvollziehbarkeit.

[ws-53]

Die Formel ist zwar 5 Zeichen länger als die von Boris, aber es geht auch mit:

Code:

=ZÄHLENWENNS(
F5:W5;"="&0;
$F$3:$W$3;">="&MINWENNS($F$3:$W$3;$F5:$W5;">"&0);
$F$3:$W$3;"<="&MAXWENNS($F$3:$W$3;$F5:$W5;">"&0))

[BoskoBiati]

Hi,

Die 5Zeichen gehen auch noch weg:

Code:

=ZÄHLENWENNS( F5:W5;0;$F$3:$W$3;">="&MINWENNS($F$3:$W$3;$F5:$W5;">0");$F$3:$W$3;"<="&MAXWENNS($F$3:$W$3;$F5:$W5;">0"))

[ws-53]

Ich hatte mich bei einer ganz einfachen Aufgabe verrechnetz, denn die Formel war 8 anstatt 5 Zeichen länger.

Aber noch etwas weiter optimiert, ist diese nun mit einer Länge von 112 noch 6 Zeichen kürzer.

Code:

=ZÄHLENWENNS(
F5:W5;0;
F$3:W$3;">="&MINWENNS(F$3:W$3;$F5:$W5;">0");
F$3:W$3;"<="&MAXWENNS(F$3:W$3;$F5:$W5;">0"))