Tage summieren mit unterschiedlichen Zeiträumen

[LuckyJoe]

Hallo zusammen,

ich verwalte eine Mitgliederliste, in der ich nachhalten möchte, welches Mitglied wieviel Tage dabei ist oder war. Dabei kann es vorgekommen, dass Mitglieder in der Vergangenheit mehrmals ein- bzw. ausgetreten sind. In einer Zelle ist notiert, zu welchem Datum die Berechnungen erfolgen sollen ("Stand").
Ich habe mittlerweile eine Formel zusammengebastelt, die das wohl auch ausrechnet, möchte aber die Formel-Profis fragen, ob es dafür nicht auch eine kürzere Formel gibt (die Formel aus L6 ist bis nach L11 heruntergezogen).

	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	Stand:	07.01.2024
5			Nr.	Vorname	Name	Eintritt 1	Austritt 1	Eintritt 2	Austritt 2	Eintritt 3	Austritt 3	Tage
6			1	Anton	Mustermann	14.01.1979						16.429
7			2	Berta	Musterfrau	14.01.1979	31.12.1984					2.178
8			4	Cäsar	Testmann	01.01.1985	30.06.1986	01.01.1987				14.065
9			5	Dora	Testfrau	27.01.1979	01.05.1982	16.07.1983	30.05.1984			1.509
10			6	Emil	Probename	25.06.1982	31.12.1991	01.01.1998	30.06.2003	01.01.2020		6.949
11			7	Friedrich	Nocheiner	05.11.1997	30.06.2001	01.01.2015	31.12.2016	30.06.2019	31.12.2022	3.343

Zelle	Formel
L6	=WENN(G6="";TAGE($B$4;F6);TAGE(G6;F6))+WENN(H6="";0;WENN(I6="";TAGE($B$4;H6);TAGE(I6;H6)))+WENN(H6="";0;WENN(J6="";0;WENN(K6="";TAGE($B$4;J6);TAGE(K6;J6))))

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 365

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Danke für's Lesen.

[Klaus-Dieter]

Hallo,

ich würde das so lösen:

Tabelle1

	A	B	C	D	E	F	G	H	I	J	K	L	M
3
4	Stand:	07.01.2024
5			Nr.	Vorname	Name	Eintritt	Austritt	Tage			Vorname	Name	Tage
6			1	Anton	Mustermann	14.01.1979	07.01.2024	16429			Anton	Mustermann	16429
7			2	Berta	Musterfrau	14.01.1979	31.12.1984	2178			Berta	Musterfrau	2178
8			4	Cäsar	Testmann	01.01.1985	30.06.1986	545			Cäsar	Testmann	14065
9			5	Cäsar	Testmann	01.01.1987	07.01.2024	13520			Dora	Testfrau	1509
10			5	Dora	Testfrau	27.01.1979	01.05.1982	1190			Emil	Probename	6949
11			6	Dora	Testfrau	16.07.1983	30.05.1984	319			Friedrich	Nocheiner	3343
12			6	Emil	Probename	25.06.1982	31.12.1991	3476
13			7	Emil	Probename	01.01.1998	30.06.2003	2006
14			8	Emil	Probename	01.01.2020	07.01.2024	1467
15			7	Friedrich	Nocheiner	05.11.1997	30.06.2001	1333
16			8	Friedrich	Nocheiner	01.01.2015	31.12.2016	730
17			9	Friedrich	Nocheiner	30.06.2019	31.12.2022	1280

Formeln der Tabelle

Zelle Formel G6 =HEUTE() H6 =G6-F6 M6 =SUMMENPRODUKT((D6:D17=K6)*(E6:E17=L6)*(H6:H17)) H7 =G7-F7 M7 =SUMMENPRODUKT((D7:D18=K7)*(E7:E18=L7)*(H7:H18)) H8 =G8-F8 M8 =SUMMENPRODUKT((D8:D19=K8)*(E8:E19=L8)*(H8:H19)) G9 =HEUTE() H9 =G9-F9 M9 =SUMMENPRODUKT((D9:D20=K9)*(E9:E20=L9)*(H9:H20)) H10 =G10-F10 M10 =SUMMENPRODUKT((D10:D21=K10)*(E10:E21=L10)*(H10:H21)) H11 =G11-F11 M11 =SUMMENPRODUKT((D11:D22=K11)*(E11:E22=L11)*(H11:H22)) H12 =G12-F12 H13 =G13-F13 G14 =HEUTE() H14 =G14-F14 H15 =G15-F15 H16 =G16-F16 H17 =G17-F17

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Peter]

Hallo,

beim Tabellenaufbau von Klaus-Dieter (Spalte A-H), der m.E. deutlich mehr Sinn macht, könnte man auch einfach eine Pivottabelle verwenden um die Tage zu berechnen.

[LCohen]

bei Ausgangskonfiguration L6: =LET(x;SUMME((G6:K6-F6:J6)*ISTUNGERADE(SPALTE(G6:K6)));x+(x<0)*B$4) erlaubt ohne Formelverlängerung noch mehr Ein/Austr..

[Kuwer]

Hallo,

ich hatte mich da auch mal versucht ohne LET.

=SUMME(F6:K6*(ISTGERADE(SPALTE(F6:K6))*-2+1))+(ISTUNGERADE(ANZAHL(F6:K6))*$B$4)

Mit LET dann so:

=LET(x;SUMME(F6:K6*(ISTGERADE(SPALTE(F6:K6))*-2+1));x+(x<0)*$B$4)

Gruß, Uwe

[LuckyJoe]

Hallo zusammen,

vielen Dank für eure Unterstützung.

@Klaus-Dieter, @Peter: Danke für die gute Lösung, allerdings geht mir damit etwas die Übersicht verloren.

@LCohen, @Kuwer: Ich hatte mir auch schon Gedanken um die LET-Funktion gemacht, bin dann aber mit den Formeln nicht weiter gekommen. Normalerweise würde ich das auch über VBA lösen, diesmal soll es aber eine makrofreie Mappe zum Weitergeben bleiben. Mit der LET-Funktion halte ich die Formel für übersichtlich und nachvollziehbar.

Danke nochmals an alle!

[Klaus-Dieter]

Hallo Jörg,

 Danke für die gute Lösung, allerdings geht mir damit etwas die Übersicht verloren.

das kann man möglicherweise unterschiedlich bewerten, ich find die von mir vorgeschlagene Vorgehensweise übersichtlicher. Vor allem ist sie besser für alle Arten der Auswertung geeignet.

[LCohen]

Rein normativ hat K-D recht.

[Luschi]

Hallo LuckyJoe,

hier mal eine Power Query Lösung mit eingebauter M-Funktion.

Gruß von Luschi
aus klein-Paris

[LuckyJoe]

@Klaus-Dieter: ja, grundsätzlich hast du natürlich Recht. In diesem Fall ging es aber um eine andere Darstellung der Tabelle, so dass ich mich für die andere Lösung entschieden habe.

---

Hallo Luschi,

Danke auch dir für deine interessante Lösung.