Office: summieren wenn in bestimmter Zelle bestimmter Wert steht

schönen Tag Euch

In meiner Bestandsliste stehen die verschiedenen Artikel in mehrzeiligen Blöcken untereinander.
In den Zellen C14, C19, C24 usw. werden die Bestände der verschiedenen Artikelversionen summiert, und diese Summen Zähle ich in Zeile 9 wiederum zusammen als Gesamtsumme.
In den Zeilen 13, 18, 23 usw. stehen die Bezeichnungen der verschiedenen Artikelversionen.

Momentan sehen meine Formeln so aus:

Zelle C14 =SUMME(E14:M14)
Zelle C19 =SUMME(E19:M19)

Zelle C9 =SUMME(C14;C19;C24;C29;C34;C39;C44;C49;C54;C59)+SUMME(C64;C69;C74;C79;C84;C89;C94;C99;C104;C109)+SUMME(C114;C119;C124;C129;C134;C139;C144;C149;C154;C159)+SUMME(C164;C169;C174;C179;C184;C189;C194;C199;C204;C209)+SUMME(C214;C219;C224;C229;C234;C239;C244;C249;C254;C259)

Wenn ein Artikel gelöscht wird oder hinzu kommt (oder sich sonst etwas ändert) muss ich händisch anpassen.


Kennt Ihr eine Formel/Funktion mit der man folgendes realisieren könnte:
SUMME(C:C, wenn zwei Zellen nach rechts und eine nach oben "32" steht)

Die Bedingung soll in jeder Zelle von Spalte C geprüft werden und nur die Zellen wo die Bedingung erfüllt ist sollen summiert werden.

Wäre klasse wenn jemand eine Lösung hätte.
Den Rest (für die anderen Summen) kann ich mir dann selber anpassen wie ich es brauche.

 

Hallo Achim,

eein Beispiel sagt mehr als tausend Worte.
Nach rechts wäre SummeWenn(). Eins nach oben kein Ansatz.

Gruß Hajo

 

guten Morgen Hajo,

da Du nach der Datei fragst, hänge ich sie hier mal an.

Noch zur Ergänzung worum es mir geht:

erstens - eine Verkürzung der Formel
zweitens - die Formel so gestalten das es egal ist ob alle vier, fünf sechs oder wieviel auch Zeilen summiert wird
drittens - die Formel so gestalten, dass ich bei Änderungen in den Artikeln die Bedingung möglichst einfach anpassen kann
viertens - das bei verschiedenen Artikelgruppen, mit unterschiedlichen Bedingungen, die Formel die jeweils anderen Artikel ignoriert, sprich ich mehrere verschiedene Gesamtsummen bilden kann, jeweils für die Artikelgruppen

Im 3. oder 4. Blatt wirst Du z.Bsp. 2 verschiedene Gesamtsummierungen finden, eben weil es andere Bedingungen gibt.

Bin mal gespannt was davon überhaupt machbar ist.
(wenns garnicht anders geht muss ich halt ein Makro schreiben, würde es diesmal aber gerne über Formeln lösen)

 

Hallo Achim,

ich bin erst wieder Abends am Rechner. Ich hätte ja eine Erkläung in der Datei erwartet was Addiert wird. Die meisten sehen die Datei das erste mal.

Gruß Hajo

 

Hallo Hajo,
ich danke Dir schon mal für Deine Zeit die Du Dir für mich nimmst.

Und ich hätte erwartet das es so schon verständlich ist.
So ist das halt wenn man sich mit etwas beschäftigt hat, man verliert den Blick/das Bewusstsein für den Verständnishintergrund der anderen.


Ich habe überlegt wie ich die Datei am anschaulichsten erklären kann (sämtliche Kunden bezogene Bezeichnungen und Informationen musste ich natürlich entfernen), und hoffe das es mir mit einem Bild gelingen wird.


In Zeile 12, 17, 22 usw. stehen Lagerorte, die sind hier nicht von Belang.
Tabelle5 ist ein Lagerplan, der ist hier auch wurscht.
Tabelle 2 fällt aus dem Schema und braucht auch nicht zu interessieren.
Die Zahlen in Spalte P sind eine Wiederholung der Zahlen von Spalte C, und auch nicht von Belang.
In Zeile 11, 16, 21 stehen sonst die Artikelnummern, Artikelbezeichnungen und andere Informationen, die sind hier aber auch alle nicht von Belang.


In Zeile 8, 13, 18 stehen Kleidergrößen (also unterschiedliche Artikelversionen). Auf diese Größen wollte ich mich bei der Abfrage beziehen (Größe = Bedingung).
In Tabelle3 ist gut zu sehen das die Größenangaben halt auch ein anderes Format haben (gutes Beispiel für ganz andere Artikel).

Jeder Artikel besteht aus einem Block von 5 Zeilen (inkl. Leerzeile). 11-15, 16-20, 21-25 usw.
Das ist im Moment so, muss aber nicht immer so sein.

In den Zeilen 14, 19, 24 usw. stehen unter den Größen die jeweiligen Lagerbestände. Links davon, in Spalte C, werden diese dann für die jeweilige Zeile summiert. Also Gesamtsumme pro Artikel (alle Größen zusammengefasst). ==> Zelle C14 =SUMME(E14:M14) <==

In Zeile 8 summiere ich spaltenweise nach unten, d.h. in C8 habe ich die Anzahl aller einzelnen Kleidungsstücke im Lager, in E8-M8 stehen die Summen für alle Kleidungsstücke einer Größe (egal welche Version, welches Modell etc.)



Für die Formeln in Zeile 8 suche ich hier jetzt Hilfe, denn wenn sich die Anzahl der Artikel verändert, oder Artikel mit anderen Größenangaben kommen (siehe Tabelle3), oder sich sonstwas ändert, wird es für mich aufwändig. (und noch ist die Liste klein, sie wird vermutlich wachsen)

Deshalb hatte ich gehofft es gäbe eine Möglichkeit die Größenangabe als Bedingung für die Summierung zu nutzen.

So rein ins blaue:
SummeWenn BereichVerschieben ... oder sowas
Damit kenne ich mich leider nicht aus, habe aber schonmal gelesen das man mit BereichVerschieben in alle vier Richtungen gehen kann.

Jedenfalls muss ich versuchen die Formeln variabel anstatt starr, und leichter und schneller auf Änderungen anpassbar zu machen.
Wichtig sind dabei Tabelle 1, 3, 4.
Aber wenn ich einmal das Schema der (hoffentlichen) Lösung begriffen habe, kann ich die Anpasserei selber machen.


Ich hoffe jetzt ist soweit alles verständlich?
Wenn doch noch Fragen sein sollten: Antworten werden kommen.

 

*Meldung mach*

In der weiter oben bereitgestellten Beispieldatei haben sich leider Fehler eingeschlichen:

In Tabelle1 Zelle C9, C14 und C19 sind die Summenformeln abhanden gekommen, statt dessen stehen dort Fantasiezahlen.
Habe ich vorhin erst gesehen (ist ja auch nicht meine Arbeitsdatei).
Davon also nicht verwirren lassen.


Mit meiner Vermutung "SummeWenn BereichVerschieben" war ich garnicht verkehrt.
Habe es inzwischen hinbekommen:

Tabelle1 Zelle C9:
=SUMMEWENN(E10:E65000;32;BEREICH.VERSCHIEBEN(E10:E65000;1;-2;1;1))

Das kann ich mir gut auf alle anderen Summierungen anpassen, und wenn ich Artikel lösche oder hinzufüge brauche ich garnichts zu tuen.

Und wenn sich die Bedingung ändert ist es auch nicht viel Arbeit.

Auf jeden Fall ist es einfacher als vorher, und übersichtlicher.


Ich bedanke mich für die Zeit die Ihr Euch genommen habt.
Falls noch jemand einen Verbesserungsvorschlag hat, gerne, immer her damit, ich werde ihn mitbekommen.

 

Hallo!

Deine Angaben verwirren mich etwas, daher komme ich einfach mal auf deine Grundfrage zurück:

Das geht doch mit SUMMEWENN:

=SUMMEWENN(E10:E9999;32;C11:C10000)

Mit deiner Formel wäre ich vorsichtig:

Mit der Formelauswertung erkennst du schnell, dass hier BEREICH.VERSCHIEBEN(...) nur die Zelle C11 zurückgibt.
(Was auch logisch ist, denn du hast ja als Höhe und als Breite jeweils 1 angegeben...)
Wenn ich deine Beschreibung richtig verstanden habe, dann ist das nicht das, was du möchtest.

 

Vielen Dank für Deine Hilfe schatzi.
"SUMMEWENN" hat mich zum Erfolg geführt.

Hatte Deine Antwort zwar noch morgens vor der Arbeit gelesen, aber da hatte ich keine Zeit zum Antworten.
In der Arbeit habe ich dann solange ausprobiert bis ich die Syntax begriffen habe und die Formel angepasst einbauen konnte.

Mit Deiner Warnung bezüglich meiner Formel (Bereich.Verschieben) hattest Du recht.
Die hatte ich in einer leeren Zelle eingegeben, um ihr Ergebnis mit der bisherigen (normalen) Summenformel zu vergleichen.
Dabei habe ich zielsicher genau die Summierungszelle erwischt, sprich SUMMEWENN(BEREICH.VERSCHIEBEN) hat mir die schon vorhandene Summe ausgegeben.
Sobald ich was geändert habe kam immer nur "0". (Daran hab ichs dann gemerkt)
Auch mit zig Änderungsversuchen (auch die Bereichsgröße) habe ich meine Formel nicht ans laufen gebracht.
Keine Ahnung obs damit überhaupt geht.

Außerdem ist Deine eh einfacher, genau so flexibel wie ich es brauche, und jederzeit schnell und einfach anzupassen falls es nötig werden sollte.

Für den Fall das es jemanden interessiert habe ich meine Datei, jetzt mit Deiner Formel, nochmal angehängt.

In Tabelle 3 und 4 kann man schön sehen wozu ich das brauche, dort gibt es unterschiedliche Gesamtsummen.