[Excel] SUMMEWENN mit variablem Summenbereich

lechef

Lieutenant
Registriert
März 2007
Beiträge
541
Hallo zusammen,

ich suche eine Formel in der Art der SUMMEWENN-Funktion, mit der ich allerdings einen variablen Spaltenbereich für die Summenbildung abdecken kann.

Dazu folgende zwei Beispiele, siehe Anhang.

Mein Suchkriterium steht B3 bzw. B10, Suchbereich ist Spalte D. Der Summenbereich soll nun, beginnend bei Spalte F abhängig von dem angegebenen Index (Zähler) in B4 bzw. B11 nach rechts erweitert werden.

Zum besseren Verständnis habe ich die Zellen rot eingefärbt, über die ich eine Summenbildung brauche.

Jemand ne Idee?
 

Anhänge

  • excelprob.png
    excelprob.png
    7,7 KB · Aufrufe: 2.331
gude,

ich habe mich mal drangesetzt und bin auf folgendes ergebnis gekommen:

oben:
=SUMMEWENN($E$3:$E$5;$B$3;F3:F5)+SUMMEWENN($E$3:$E$5;$B$3;G3:G5)+SUMMEWENN($E$3:$E$5;$B$3;H3:H5)+SUMMEWENN($E$3:$E$5;$B$3;I3:I5)

unten:
=SUMMEWENN($E$10:$E$12;$B$10;$F$10:$F$12)+SUMMEWENN($E$10:$E$12;$B$10;$G$10:$G$12)+SUMMEWENN($E$10:$E$12;$B$10;$H$10:$H$12)+SUMMEWENN($E$10:$E$12;$B$10;$I$10:$I$12)+SUMMEWENN($E$10:$E$12;$B$10;$J$10:$J$12)+SUMMEWENN($E$10:$E$12;$B$10;$K$10:$K$12)+SUMMEWENN($E$10:$E$12;$B$10;$L$10:$L$12)

vielleicht gibt einen besseren weg, denn die summewenn addiert nur von "oben nach unten" und nicht von "links nach rechts".
darum habe ich einfach das + genommen.

hoffe die formeln helfen dir weiter ^^

gruß
yaki
 
Hallo lechef,

es ist möglich alles in einer Formel zu verpacken.
Um Fehler zu vermeiden und das Ergebnis nachvollziehen zu können rate in diesen Fall dringend davon ab.

Stattdessen ist es empfehlenswert eine Hilfsspalte zu verwenden,
in der je Zeile eine Teilsumme errechnet wird (Simples Wenn-Dann).
Trickreich bei der Summierung ist die dynamische Bestimmung der Spaltenbreite (Zelle B4).
(Dies ist in yaki.udon's Formel noch nicht berücksichtigt.)
Das funktioniert mit den Formeln Indirekt, Adresse und Zelle.

Ein vollständiges Beispiel findest du im Anhang.

knoxyz

PS: Bitte lade das nächste Mal auch eine Beispieldatei und nicht nur den Screenshoot hoch.

Nachtrag: Kompatibilitäts-Bug zwischen Deutscher/Englischen Version
Im Excel mit englischer Sprachversion funktioniert die Formel nicht,
weil nur Funktionswörter wie Wenn, Summe, Adresse und Zelle kompatibel (automatisch ins deutsche/englische übersetzt) werden,
jedoch die innerhalb der Zellenfunktion vorschriftsmäßig in Anführungszeichen eingefügten Wörter "Spalte"/"Zeile" deutsch bleiben... :stock:
Ok, die Zellen-Formel dürfte relativ selten verwendet werden, aber trotzdem :watt:
 

Anhänge

Zuletzt bearbeitet:
Da eignet sich das SUMMENPRODUKT, mit INDIREKT kann man dann den variablen Zellbereich erfassen.

Formel in E6:
=SUMMENPRODUKT((E3:E5=B3)*(INDIREKT("Z"&ZEILE()-3&"S"&SPALTE()+1;0):INDIREKT("Z"&ZEILE()-1&"S"&SPALTE()+B4;0)))​

Summiere in den Zeilen, wo E3 bis E5 = B3 von aktueller Zeile()-3 Zeilen* und aktueller Spalte()+1 Spalte* bis aktuelle Zeile()-1 Zeile* und aktuelle Spalte()+B4*


Das einzig Unschöne an der Formel ist, dass man bei ZS-Bezügen (anstelle von "gewöhnlichen" A1-Bezügen) scheinbar keine variablen, erweiterbaren Bereiche angeben kann.


Eigentlich würde man nur einmal INDIREKT benötigen und schreiben:
INDIREKT("Z"&ZEILE()-3&"S"&SPALTE()+1:"Z"&ZEILE()-1&"S"&SPALTE()+B4;0)​
Aber das mag Excel wohl nicht, darum muss man den Bereich von Zelle X bis Zelle Y auf zwei INDIREKT aufteilen.

(das ganze funktioniert aber sehr wohl mit nur einem INDIREKT, wenn man feste Zellbezüge angibt: INDIREKT("Z3S6:Z5S"&5+B4;0) - aber dann kann man die Formel nicht nach unten in E13 kopieren, weil Z3S6:Z5S... eben fest ist, man unten aber Z10S6:Z12S... bräuchte).


Ich hoffe, das war halbwegs verständlich :D



*) aktuelle Zeile/Spalte bedeutet die Zeile/Spalte, in der sich die Formel befindet, also hier E6. Ausgehend von E6 geht man dann bspw. 3 Zeilen nach oben (-3) und eine Spalte nach rechts (+1). Zelle E6 - 3 Zeilen + 1 Spalte = Zelle F3.
 

Anhänge

Zuletzt bearbeitet:
Zurück
Oben