Summenprodukt für bedingte Summe - Bedingung kann fehle

[wkosensky]

Hallo,

ich habe ständig Summenprodukte, die unter Beachtung einiger Bedingungen grundsätzlich immer die gleiche Summe bilden. Nur die Bedingungen wechseln und einzelne Bedingungen können auch mal nicht vorhanden sein.
Zugrunde liegt eine intelligente Tabelle "Tab" mit Attributen und Werten. Die Attribute werden als Bedingung in der Formel mit INDIREKT angesprochen und die Werte bei Übereinstimmung der Bedingungen summiert (siehe Zeile 17).
Nun suche ich eine Formel, die eben auch das Nichtvorhandensein von Bedingungen zulässt (siehe Zeile 18 --> da fehlt C18). In Zeile 18 funktioniert die gleiche Formel wie in Zeile 17 nicht, weil das Kriterium in C18 nicht vorhanden ist - ich muss die komplette Bedingung entfernen (siehe Zeile 19). Schwer zu erklären, ich habe eine Datei angehängt.

Könnt ihr mir bitte einen Tipp geben, wie ich die Formel variabel schreiben kann, so dass die Bedingungen beachtet werden, wenn sie vorhanden sind. Wenn eine Bedingung nicht vorhanden ist, sollte diese eben auch nicht beachtet werden, d. h. der entsprechende Term in der Summenprodukt-Formel sollte dann wohl 1 ergeben.

Vielen Dank!!

VG
W.

[slowboarder]

Hi

erweitere die einzelnen Bedingungen nach diesem Prinzip:

aus (INDIREKT($A$16)=$A17)
wird ((INDIREKT($A$16)=$A17)+($A17=""))
dies gilt, wenn in der Tabelle jede Zeile gefüllt ist.
das + simuliert in solchen Matrixformeln die ODER-Verknüpfung, so wie das * die UND-Verknüpfung simuliert.
allerdings sollte es in dieser einfachen Variante ausgeschlossen sein, dass beide Bedingungen gleichzeitig zutreffen, da diese sonst doppelt in die Berechnung eingehen.

sollte es vorkommen können, dass auch in der Tabellenspalte eine Zelle leer sein kann; dann
(((INDIREKT($A$16)=$A17)+($A17=""))>0)

allerdings könntst du auch SummeWenns zum auswerten verwenden:
=SUMMEWENNS(Tab[Wert_1];INDIREKT($A$16);$A17;INDIREKT($B$16);$B17;INDIREKT($C$16);$C17)
wenn du als Bedingung in den Spalten, die nicht berücksichtigt werden sollen den Stern * einträgst, anstatt die Zelle leer zu lassen
Gruß Daniel

[HKindler]

Hi,

=SUMMENPRODUKT((INDIREKT($A$16)=$A18)*(INDIREKT($B$16)=$B18)*((INDIREKT($C$16)=$C18)+($C18=""))*Tab[Wert_1])

Auf die Art muss C18 entweder leer sein oder der Eintrag in der Spalte muss C18 entsprechen. Falls noch mehr Spalten leer sein können, dann musst du dieses Prinzip *((Bedingung)+(Feld=""))* auch für jene anwenden.

Übrigens würde ich INDIREKT meiden. In dieser einfachen Tabelle könntest du z.B. einfach alle Spalten verwenden und die uninteressanten leer lassen. Wenn es deutlich mehr Spalten sind, wird es natürlich schwieriger...

[shift-del]

Moin

Jack's drittes Postulat: Lösungen mit SUMMENPRODUKT() oder INDIREKT() sind Mist.

Erstens verwende SUMMEWENNS():

Code: Alles auswählen

=SUMMEWENNS(Tab[Wert_1];Tab[Spalte_A];A17;Tab[Spalte_B];B17;Tab[Spalte_C];C17)

Zweitens für ein nicht vorhandenes Kriterium trägst du in die Zelle ein * ein.

[wkosensky]

Jack's drittes Postulat: Lösungen mit SUMMENPRODUKT() oder INDIREKT() sind Mist.

Danke für den Denkanstoß - ich habe tatsächlich ein Performance-Problem durch die volatile Funktion Indirekt(), weil die zu verarbeitende Tabelle sehr groß ist.

Erstens verwende SUMMEWENNS():

Code: Alles auswählen

=SUMMEWENNS(Tab[Wert_1];Tab[Spalte_A];A17;Tab[Spalte_B];B17;Tab[Spalte_C];C17)

Zweitens für ein nicht vorhandenes Kriterium trägst du in die Zelle ein * ein.

Danke für den Tipp, so etwas halbwegs "elegantes" hab ich gesucht. Leider funktioniert es aber noch nicht - es kommt "0" raus. Es liegt wohl an den *.

Hättest Du vielleicht eine Lösung dafür? Ich gebe einen aus!

VG
W.

[shift-del]

In deiner Beispielmappe hat es funktioniert. Aber vermutlich sieht deine Original-Mappe gaaaaanz anders aus.

[wkosensky]

In deiner Beispielmappe hat es funktioniert. Aber vermutlich sieht deine Original-Mappe gaaaaanz anders aus.

Ok, stimmt, in meinem Beispiel funktioniert es. Problem ist wohl, dass in der Originalmappe die Attribute auch mal Zahlen sein können und nicht nur Text. Bei Zahlen funktioniert wohl der * als Platzhalter nicht.
Gibt es dafür einen eleganten Workaround?

[shift-del]

Probiere mal bei den Zahlen den Eintrag >=0.

[slowboarder]

Hi

die verschiedenen Joker und Vergleiche als Kriterium zählen folgendes:
"*" und "?" zählt nur Texte
">0" , "<0" zählt nur Zahlen
"<>" zählt alles, was einen Inhalt hat, Texte und Zahlen, aber keine Leerzellen.
"=" zählt Leerzellen

Gruß Daniel

[wkosensky]

Probiere mal bei den Zahlen den Eintrag >=0.

OK, das funktioniert jetzt. Nett, elegant genug! Danke Dir dafür - das hilft mir sehr! Schicke mir doch gern per PN mal eine Möglichkeit, mich erkenntlich zu zeigen (Paypal, etc.)!!

Auch den anderen vielen Dank, aber die Lösungen waren zu kompliziert. Bei vielen Kriterien wäre das zu unübersichtlich.

[wkosensky]

Gibt es eine Möglichkeit, dass ich nicht fest in die Summenwenns()-Funktion schreibe, welche Wert-Spalte ich summieren möchte, ohne indirekt() zu benutzen? D. h. ich möchte in einem anderen Feld festlegen, welche Wert-Spalte summiert werden soll.

[shift-del]

Es ist mir nicht klar warum du so unsystematisch Werte summieren willst.

Besser wäre es wenn es ein System gäbe dann ließe sich das bestimmt mit einer Pivot darstellen.

[slowboarder]

Hi

Auswählbare Spalten ohne Indirekt geht auch etwas aufwendiger mit Index

anstelle von Indirekt(Spaltenname) schreibst du Index(GanzeTabelle;0;Vergleich(SpaltenÜberschrift;ÜberschriftenZeile;0))

Gruß Daniel

[wkosensky]

Es ist mir nicht klar warum du so unsystematisch Werte summieren willst.

Besser wäre es wenn es ein System gäbe dann ließe sich das bestimmt mit einer Pivot darstellen.

Das System gibt es schon: um beim Bsp. zu bleiben --> Anzahl aller verkauften Mercedes-Teile, Anzahl aller verkauften Mercedes-Lichter, Anzahl aller verkauften Lichter (egal ob Mercedes oder VW), Anzahl aller verkauften hochwertigen Teile etc... Nur das ich in echt viel mehr Attribute habe, die mir mein gesamtes Volumen (die Wert-Spalten) immer genauer erklären.
Meine Letzte Frage nach der variablen Wert-Spalte zielte darauf ab, dass ich eben das eine Mal die Anzahlen addieren möchte, und dann auch mal die Verkaufspreise - mit den gleichen Kriteren wie bei Anzahlen.

Mit einer Pivottabelle kann ich doch immer nur eine Kombination von Attributsausprägungen wählen und damit verschiedene Werte-Spalten addieren. Ich möchte aber von unterschiedlichsten Attributskombinationen die addierten Wert-Spalten übersichtlich darstellen.