Office: (Office 365) SUMMENPRODUKT dynamisch aus bestimmten Tabellenblättern

Hallo zusammen,

ich habe bei meiner bisherigen Zählerstandsmappe bisher folgende Grundstruktur:

Es gibt jeweils Tabellenblätter (2024, 2025 etc.) wo in Spalte A (A3-A367 bzw. bei Schaltjahr bis A368) das jeweilige Datum (01.01. bis 31.12.) steht. In Spalte B-D stehen dann die jeweiligen Zählerstände des Tages (Strom, Gas, Wasser), in Spalte E-G dann die jeweiligen errechneten Tagesverbräuche. Ist ein Tag noch in der Zukunft, sind die jeweiligen Spalten in E-G mit einem #NV versehen, was ich auch brauche für meine dazugehörigen Diagramme. Soweit so gut, das sind dann meine "Datenbanken" aus den jeweiligen Jahren, woraus ich alle anderen Berechnungen und Statistiken & Diagramme erzeuge.

Nun gibt es eine Übersichtstabelle, wo ich die jeweiligen Jahre "aufliste" bzw. "auswerte":

Spalte A:

A1: 01.01.2025 (formatiert als Jan 25)
A2: 01.02.2025 (formatiert als Feb 25)
A3: 01.03.2025 (formatiert als Mrz 25)
-
A12: 01.12.2025 (formatiert als Dez 25)
usw.

In Spalte B-D untereinander die Summen aus den jeweiligen Monaten für Gas, Wasser, Strom aus der "Datenbanktabelle" von 2025 (Spalte E-G):

B1: Monatswert Strom für Jan 25
B2: Monatswert Strom für Feb 25
B3: Monatswert Strom für Mrz 25
-
B12: Monatswert Strom für Dez 25

Gas und Wasser dann analog in C1-C12 & D1-12.

Bisher "hole" ich mir diese Werte mit folgender Formel (exemplarisch für Strom Jan 2025):

Code:

=WENN(SUMMEWENNS('2025'!$E$3:$E$33;'2025'!$E$3:$E$33;"<>#NV")=0;"";SUMMEWENNS('2025'!$E$3:$E$33;'2025'!$E$3:$E$33;"<>#NV"))

Für Feb 2025 ist es dann - in Abhängigkeit von einem Schaltjahr - E34-E61 oder - E62 (Schaltjahr). Analog dann für die weiteren Monate. Das habe ich bisher für jedes Jahr immer händisch ändern müssen, wenn es Schaltjahr ist. Das ist immer ein Haufen Arbeit. Das möchte ich nun umgehen, vllt mit einem SUMMENPRODUKT (wobei das wohl keine #NV Werte aus der Datenbanktabelle mag?):

Summiere in Spalte B1 alle Werte als Tabelle 2025 Spalte E, wo in Spalte A der Monat 1 (also Januar) ist und kein #NV drin steht. Analog für die weiteren Monate.

Habt ihr eine Idee?

 

Moin!
Zunächst zum Kardinalfehler:

"Mein" Excel hat 2^20 aka 1.048.576 Zeilen.
Dies entspricht grob 2.871 Jahren.
Führe die Jahrestabellen zusammen, und alle Probleme lösen sich in Luft auf.
Auswertungen dann mittels Pivot oder Filter iVm TEILERGEBNIS()

Gruß Ralf

 

Dank für deine schnelle Antwort. Das würde ich wirklich ungern machen (Aufwand & Übersichtlichkeit der Jahre).

 

Ad Aufwand: Über wie viele Jahre reden wir denn?
Ad Übersichtlichkeit:
Kennst Du z.B. Datenschnitte?

Da es ja um Verbräuche geht, brauchst Du wenig bis nichts anonymisieren und kannst deshalb ja mal die Datei hochladen.
Dann kann ich konkrete Tipps geben.

Gruß Ralf

 

Ich habe die Tabelle jetzt mal entleert (ursprl. Größe über 1MB - Diagramme etc. - Jahrestabellen von 2020-2026).

Ich würde schon gerne die Struktur so beibehalten, das wäre mir wichtig.

 

Hallo,

Welche Excelversion nutzt du?

Gruß Uwe

 

Microsoft 365

 

Hallo,

eh ich weiterbaue teste mal in B3 und runterziehen:

Code:

=LET(xA;A3;
xB;MONATSENDE(A3;0);
xC;FILTER('2025'!E2:M368;('2025'!A2:A368>=xA)*('2025'!A2:A368<=xB);"");
xD;SPALTENWAHL(xC;1;3;5;7;9);
HSTAPELN(SUMME(INDEX(xD;;1));SUMME(INDEX(xD;;2));SUMME(INDEX(xD;;3));SUMME(INDEX(xD;;4));SUMME(INDEX(xD;;5))))

Da diese Formel spillt müssen alle Zellen des Spillbereiches leer sein, da sonst #Überlauf! kommt.

Gruß Uwe

 

Guckst du hier:

 

Vielen Dank erstmal für deine/eure Hilfe mit der LET Formel. Aber ich glaube, damit bin ich völlig überfordert... Ich habe jetzt garkeine Spaltenbezeichnungen wie A oder B mehr, nur noch 1, 2, 3 etc.... Puh

 

"Ich habe jetzt gar keine Spaltenbezeichnungen" ??? Hääh? Sowas wirft dich aus der Bahn ???
Du brauchst doch nur unter Optionen / Formeln / Arbeiten mit Formeln auf A1 statt Z1S1-Schreibweise umzustellen.
"damit bin ich völlig überfordert." ??? Womit denn ÜBERFORDERT ?
Du hast eine fix- und fertige Lösung bekommen, bei der du GAR NIX mehr machen musst !

 

Das mit dieser Schreibweise kannte ich bisher wirklich nicht - danke für's dazulernen.

Ich versuche es mal chronologisch durchzugehen:

Du definierst "ja" als Jahr aus A3 ebenso "mo" als Monat aus A3 - soweit verstanden.
Was ist aber "da"? Vermutlich verweist du da auf ein Tabellenblatt? Und was ist "we"? Auch da wird wieder auf ein Tabellenblatt verwiesen? Und wofür der "fi" (irgendein Filter?)... Sorry, habe mit dieser Formel noch nie gearbeitet...

Ich bin wirklich sehr dankbar für Hilfe, meine Antwort sollte eben auch nicht pissig rüber kommen.

 

Eigentlich ist es ja gut, dass du Formeln auch verstehen willst, die du verwendest.
Aus der Jahreszahl wird ermittelt, auf welches Blatt (2025 oder 2026) zugegriffen wird (mittels INDIREKT kann man sowas).
Dann werden aus dem entsprechenden Blatt "da" und "we" ermittelt. "da" ist die Spalte mit den Datumswerten und "we" die Spalte mit den Verbrauchswerten (je nachdem Spalte 5,7,9,11 oder 13).
Dann wird einfach die Spalte der Werte nach Datum gefiltert und die Summe daraus gebildet.

 

Danke für die Erläuterung, denn natürlich will und muss ich die Formel verstehen :) Mit der Erläuterung werde ich mal versuchen weiter zu machen - und mich damit weiterbilden.

 

Hier mal die Sache als LAMDA() im Namensmanager eingebaut.
Aufruf der Formel für 2025:=SummenAusgeben(A3:A14)
2026: =SummenAusgeben(A18:A29)

Gruß Uwe