Office: (Office 365) XVERWEIS mit "Versatz" suchen

Servus Office-Gemeinde,

ich glaube ich treibe Xverweis gerade in die Superlative (für mein Excel-Verständnis) - zumindest hoffe ich, dass es per Xverweis geht und ich nicht auf dem Holzweg bin.

Ich habe ein Übersichtsblatt für alle Kollegen, quasi ein Produktivitätsblatt mit Anzahl der Tage, die gearbeitet wurden, Krankheits- und Urlaubstage etc. und dann eben für jeden Mitarbeiter ein eigenes Blatt.

Für den aktuellen Monat den aktuellen Wert zu suchen klappt mit

Code:

=XVERWEIS(B4;Maier!A6:A101;Maier!Q6:Q101;)

einwandfrei.

Nun soll aber noch zusätzlich der ein Summenwert für die letzten 12 Monate eingefügt werden und hier stehe ich vor dem Problem, wie ich den "Versatz" reinbekomme.

Aktuell habe ich es mit

Code:

=SUMME(XVERWEIS(B4;Maier!A6:A101;Maier!Q6:Q101):XVERWEIS(B4-365;Maier!A6:A101;Maier!Q6:Q101))

versucht zu lösen. Klappt (bisher) grundsätzlich, aber das Problem ist

Bei einem Schaltjahr bzw wenn dann der Februar in die Berechnung einfließt, stimmen die Werte natürlich nicht mehr bzw es gibt #NV aus, da mit den 365 Tagen nicht der 1.2., sondern der 2.2. genommen wird und mit B4-Monat(12) o.ä. zieht Excel immer nur einen Tag ab.

Gibt es denn eine Möglichkeit im Xverweis die Rückgabematrix simpel um 12 Zeilen (sofern vorhanden, aber solang kein neuer Mitarbeiter kommt, stellt sich das Problem erstmal nicht) nach oben zu verrücken?
Natürlich ist der "unschöne" Weg, die Formeln zu ändern, sobald ein Schaltfebruar einberechnet wird und sobald nicht mehr, die Formel wieder zu ändern, möglich, aber 2024 denk ich da sicher nicht mehr daran und ich bin kein Freund von unschönen/unsauberen Wegen.

 

Hola,
wenn du eine Summe berechnen willst, nimm Summewenn().
Mehr kann man nicht sagen da du zwar viel geschrieben hast, sich das aber ohne Exceldatei niemand vorstellen kann was wo steht.
Gruß,
steve1da

 

Summwenn arbeitet aber allein nicht in einer Matrix, wenn ich den Monat verändere?!

Hab die Datei mal "bereinigt" - da es sich um Firmendaten handelt, ist ja hoffentlich logisch, dass ich eigentlich nichts davon rausgeben wollte (bzw auch sollte, Datenschutz etc) und dachte, dass wo was steht grundsätzlich "egal" ist [ich lasse mich gern eines Besseren belehren], wenn es darum geht "Ausgabematrix = Eingabematrix minus 12 Zeilen".

Wenn mann jetzt im B4 irgendeinen Monat zwischen 1.3.20 und 1.2.21 eingibt, sieht man in E5 den Fehler.

 

=SUMME(INDEX(Maier!Q6:Q101;VERGLEICH(B4;Maier!A6:A101;0)):INDEX(Maier!Q6:Q101;VERGLEICH(B4;Maier!A6:A101;0)-12))
oder
=SUMMENPRODUKT((Maier!A6:A101<=B4)*(Maier!A6:A101>=EDATUM(B4;-12))*(Maier!Q6:Q101))
oder
=SUMMEWENNS(Maier!Q6:Q101;Maier!A6:A101;"<="&B4;Maier!A6:A101;">="&EDATUM(B4;-12))