Teilergebnis kombiniert mit Zählenwenn

[Näd]

Hallo zusammen

Ich würde gerne eine Formel erzeugen, welche sowohl die Funktion Teilergebnis wie auch die Funktion Zählenwenn kombiniert.

Ich gestalte einen Dienstplan bei welchem ich zur Statistik zum Beispiel alle Dienste «2», aber nur der eingeblendeten Zellen summieren möchte. 

Im Spalte A habe ich jeweils das Datum, in Spalte B die Dienste der Person B, in Spalte C die Dienste der Person C etc. 

Für Person B würde ich also beispielsweise gerne sämtliche Dienste 2 zusammenzählen welche in der ganzen Spalte B zu finden sind, aber nur in den eingeblendeten Zellen.

Optimalerweise könnte ich das jeweils auch noch nur für die spezifischen Wochentage der Spalte A machen: 
Das heisst: zähle für Person B alle Dienste "2" die in den eingeblendeten Zellen an einem Montag vorkommen. 

Dann könnte ich mittels simpler Summenbildung auch die Anzahl Wochendienste und Wochenenddienste der eingeblendeten Zellen berechnen.

Da das ganze etwas kompliziert zum beschreiben ist, habe ich gleich mal eine Beispieldatei hochgeladen.

Wenn mir jemand da weiter helfen könnte, wäre ich unglaublich dankbar.

Herzlichen Dank und liebe Grüsse
Näd

[echo]

Hallo
vielleicht hilft das: 
LINK: Zählenwenn in gefilterter Liste und MEDIAN 
Gruß Holger

[steve1da]

Hola,

zur Info...

https://www.ms-office-forum.net/forum/sh...p?t=360167
http://www.office-loesung.de/p/viewtopic.php?f=166&t=804127

Gruß,
steve1da

[Mase]

Hi,

ausgeblendete Zellen spielen ein Rolle, da du sie aufgrund Vergangenheit nicht anzeigen willst, oder?
Sprich: In 2020 willste nicht erst noch durch 2019 scrollen müssen?

Wenn ja,
was spricht dagegen, diesen Plan pro Jahr zu erstellen und die Summen aus der Mappe 2019 in der Mappe 2020 anzuzeigen?
Somit kannst dich nur auf die eingeblendeten Zellen konzentrieren, oder lieg ich komplett falsch?

[Näd]

Vielen Dank Holger
Anhand von Deinem Link und noch einem weiteren Tipp hab ich's vorerst geschafft.

=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("B"&ZEILE(18:748)))*(B18:B748=2)))
Danke Zoxxy!

Danke für Deine Antwort Mase
Ich möchte die Zeilen eben ausblenden können, damit sich die Statistik jeweils auch für gewünschte Zeitspannen anpasst. Zum Beispiel: wie war die Diensthäufigkeit in den letzten drei Monaten, im folgenden Monat bei der Planung etc. Ich kann dann immer die Zellen die ich nicht gebrauche ausblenden und dann zeigt es mir gleich an wie die Dienste in der Häufigkeit verteilt sind.

Hallo Steve1da und an alle:
Ich bin ein neuer Forenbenutzer und mir war nicht bewusst, dass ich möglicherweise Leute verärgere, wenn ich in verschiedene Foren gleichzeitig schreibe. Das tut mir Leid! Hatte nach einem vorherigen Versuch und fehlender Antwort gedacht die Chance steigt wenn ich mehrere Foren gleichzeitig anschreibe.

Was meine Tabelle nun noch nicht kann ist die das Teilergebnis der Dienste jeweils nur auf einzelne Wochentage also Montag, Dienstag etc. bezogen. Vielleicht hat ja da auch noch jemand einen guten Tipp!

Herzlichen Dank und liebe Grüsse
Näd

[echo]

=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("B"&ZEILE(18:748)))*(B18:B748=2)))
Was meine Tabelle nun noch nicht kann ist die das Teilergebnis der Dienste jeweils nur auf einzelne Wochentage also Montag, Dienstag etc. bezogen. Vielleicht hat ja da auch noch jemand einen guten Tipp! 

Hallo
=WOCHENTAG(A18;2) Prüft das Datum in A18 und gibt eine Zahl zwischen 1 und 7 zurück.(Mo.-So.) 
=WOCHENTAG(A18;2)=1 ist WAHR wenn das Datum auf einen Montag fällt
=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("B"&ZEILE(18:748)))*(WOCHENTAG($A$18:$A$748;2)=1)*($B$18:$B$748=2)))

Anzahl der Sichtbaren Zeilen * Wochentag = Montag * Spalte B = 2
Alle Prüfungen müssen WAHR sein, ist eine Prüfung in der Zeile FALSCH wird diese ignoriert

Ich bin ein neuer Forenbenutzer und mir war nicht bewusst, dass ich möglicherweise Leute verärgere, wenn ich in verschiedene Foren gleichzeitig schreibe. Das tut mir Leid! Hatte nach einem vorherigen Versuch und fehlender Antwort gedacht die Chance steigt wenn ich mehrere Foren gleichzeitig anschreibe.

Es ist einfach so das alle gerne Helfen, 
Wenn man sich aber um Hilfe bemüht, eine Lösung findet und anschließend feststellen muss das das ganze Thema woanders schon abgefrühstückt wurde dann ist das für uns einfach nur sch...ße. Man investiert unnötige Zeit und hat keinen Überblick von Stand der Antworten. Daher wird es (auch von mir) nicht gerne gesehen.
Ich kann dir sagen, das ich mich dann aus dem Thema zurück zeihe wenn es da keine vernünftige Erklärung gilt.

Außerdem macht es wenig Sinn, du hast sicher auch bemerkt, das die die in den Foren weiter helfen, selten nur im einem Forum aktiv sind.

Gruß Holger 

[shift-del]

Moin Näd

Kostenloser Tipp: Entsorge deine Kreuztabelle. Pflege deine Daten stattdessen als Datensatzliste:
Datum - Team - SubTeam - Person - Dienst - Wochentag

Damit kannst alle nur erdenklichen Auswertungen via Pivot machen und musst dich nicht mit komplexen Formeln herumärgern.

[Näd]

Hey Holger

Herzlichen Dank, ich kann es kaum glauben, es funktioniert perfekt!!!


Nun habe ich nochmals zwei Fragen:

Diese Formel funktioniert super:
=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("B"&ZEILE(18:748)))*(WOCHENTAG($A$18:$A$748;2)=1)*($B$18:$B$748=2)))


Wenn ich sie aber rüber ziehen möchte um sie für die weiteren Mitarbieter zu benützten, die $ Zeichen habe ich entsprechend angepasst, Bsp. jetzt für Mitarbieter F: 

=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("B"&ZEILE($22:$2002)))*(WOCHENTAG($A$22:$A$2002;2)=3)*(F$22:F$2002=2)))

dann passt es mir alles an, aber der Verweise "B" wird nicht  auf "F" angepasst, so muss ich in jeder Formel dort die entsprechende Spalte eingeben. Gibt es eine Möglichkeit das dies auch automatisch angepasst wird?



Zusätzlich habe ich die Feiertage jeweils farbig eingefärbt, mir die Farbe berechnen lassen "24" welche in der Spalte AB angegeben ist, und möchte jetzt auch noch, dass es mir angibt wer wieviele Tagdienste an den Feiertagen gemacht hat:

=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("C"&ZEILE($22:$2002)))*(ZÄHLENWENN($AB$22:$AB$2002;24)*(C$22:C$2002=2))))

Das ist die Formel wie ich dachte sollte sie stimmen, aber da gibts irgend einen Fehler, den ich leider nicht erkenne. Es berechnet mir 0 obwohl die Zahl >0 sein sollte.



Das mit den verschienden Foren habe ich nun verstanden und werde dies nicht mehr tun. Kann die Einwände sehr gut verstehen und hatte dies so nicht bedacht. 

Und danke für Deine Antwort shift-del
Leider habe ich keine Ahnung was ein Pivot ist.  


Nochmals vielen Dank für Eure Hilfe!!

[echo]

dann passt es mir alles an, aber der Verweise "B" wird nicht  auf "F" angepasst, so muss ich in jeder Formel dort die entsprechende Spalte eingeben. Gibt es eine Möglichkeit das dies auch automatisch angepasst wird?

Hallo
In Indirekt wird ein Text-String erzeugt. Diesen Wandelt Indirekt in einen gültigen Bezug
Damit der Buchstabe sich anpasst muss der Text sich entsprechend ändern.
Verwende dazu besser die Z1S1 Schreibweise, bedeutet, das auch die Spalte durchnummeriert ist und mit S1 (A) beginnt.
Der Rest sind Verkettete Text-Teile
INDIREKT("Z"&ZEILE(A1)&"S"&SPALTE(A1);0)
Entspricht den Bezug Z1S1 = A1
Benötigst du als Startadresse B5 kannst du das direkt einsetzen INDIREKT("Z"&ZEILE(B5)&"S"&SPALTE(B5);0)
ungetestet für deine Formel:
=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("Z"&ZEILE($A$18:$A$748)&"S"&SPALTE(B1);0)))*(WOCHENTAG($A$18:$A$748;2)=1)*($B$18:$B$748=2))

Das ist die Formel wie ich dachte sollte sie stimmen, aber da gibts irgend einen Fehler, den ich leider nicht erkenne. Es berechnet mir 0 obwohl die Zahl >0 sein sollte.

Lass (ZÄHLENWENN() weg und ersetzte den Teil durch ($AB$22:$AB$2002=24)
SummenProdukt ist schon matrixorientiert. und prüft bereits ob die Prüfung Zeile für Zeile WAHR ist.
ungetestet
=SUMMENPRODUKT((TEILERGEBNIS(102;INDIREKT("C"&ZEILE($22:$2002)))*($AB$22:$AB$2002=24)*(C$22:C$2002=2))))

Gruß Holger

[Näd]

Herzlichen Dank Holger

Bin leider erst jetzt dazu gekommen Deine Vorschläge umzusetzen. Etwas kriege ich leider noch nicht ganz hin, muss aber zuerst noch ein bisschen rumpröbeln, vielleicht muss ich dann nochmals nachfragen!