Office: Summenprodukt / Zählenwenn, ausser bestimmter Zeichen

Hallo zusammen :-)

In einem Dienstplanprogramm auf Basis von Excel möchte ich an den jeweiligen Arbeitstagen die Dienste zählen, um zu ermitteln, wie viele Mitarbeiter an dem Tag insgesamt im Haus sind.
Dazu verwende ich folgende Formel: =SUMMENPRODUKT(ZÄHLENWENN(D4:D80;'Legende und Wegleitung'!$A$6:$B$55)*SPALTE($A:$B)/2)
D4:D80 entspricht einem Tag, für den alle Dienste gezählt werden sollen. Die zu zählenden Dienste werden aus einem anderen Blatt aus einem Bereich abgeglichen. In diesem Bereich 'Legende und Wegleitung'!$A$6:$B$55) sind allerdings auch Zeichen enthalten, die an anderer Stelle mitgezählt werden müssen, weil sie bezahlten Tagen entsprechen, konkret sind das F (Ferien), K (Krank) und U (Unfall). Für die Zählung der tatsächlich an einem Tag im Haus befindlichen Mitarbeiter "stören" sie aber. Wie muss ich die bestehende Formel ergänzen, damit ich die drei genannten Zeichen explizit von der Zählung ausschliessen kann?
Ich hab eine anonymisierte und kastrierte Version der Tabelle, damit es nicht zu gross wird. Hoffentlich könnt ihr damit was anfangen.

Merci vielmals im Voraus :-)

 

Hi,

Crosspost: Summenprodukt / Zählenwenn, ausser bestimmter Zeichen

 

Hallo,

da du nirgends deine Excel-Version angegeben hast, nehme ich an, dass du mindestens Excel2021 hast.

Folgende Formel in die Formeleditierzeile von Zelle D81 (im Arbeitsblatt "Woche") kopieren und diese dann in die restlichen Zellen E81:J81 kopieren.

=LET(Bereich; D$4:D$80;
WerteHalbGanz; 'Legende und Wegleitung'!$A$6:$B$55;
WerteNicht; 'Legende und Wegleitung'!$C$6:$C$8;
WerteNV; VERGLEICH(WerteHalbGanz; WerteNicht; 0);
WerteZählg; WENN(ISTFEHLER(WerteNV); WerteHalbGanz; "x-x-x");
BereichWerteZählg; WENNFEHLER(ZÄHLENWENN(Bereich; WerteZählg); 0);
SUMME(MMULT(BereichWerteZählg; {0,5; 1})) )

Diese Formel benötigt naturgemäß 3 Bereiche, die ich dir fett dargestellt habe. Ihre Bedeutung dürften dir eh klar sein.
Sie funktioniert so, dass die Codes, die weder halb noch ganz gezählt werden sollen, formelintern durch den nicht existierenden Code "x-x-x" ersetzt werden. Dadurch erhalten diese Codes das Zählergebnis 0 in der Variablen "BereichWerteZählg".