Summenprodukt & teilergebnis bei mehreren Werten in einer Zelle

[Gringo13]

Hallo,

ich habe schon versucht eine Lösung im Forum zu finden, bin aber leider dabei bis jetzt noch nicht erfolgreich gewesen...

Ich habe die folgende Formel:

=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("AF"&ZEILE($25:$10000)))*($O$25:$O$10000="TEXT1"))

Diese Formel funktioniert soweit, d.h. solange in der Zelle die ich prüfe lediglich ein Wort steht z.B. Text1. Mein Problem ist, dass in einigen Zellen mehr als ein Wort stehen z.B. TEXT1,TEXT2,TEXT3. In diesem Fall wird kein Wert gezogen, selbst wenn TEXT1 enthalten ist. Ich habe schon versucht "*TEXT1*" zu nutzen, aber bekomme dann gar kein Ergebnis. Hab Ihr evtl. Ideen wie ich diese Formel retten kann oder muss ich in eine andere Richtung bzw. nach einer anderen Formel suchen?

Danke!

 

[miac]

Das Problem ist die letzte "if" Abfrage.

Ich denke, du mußt eine Funktion wie Finden oder Suchen verwenden. Dann wird es aber etwas komplizierter (Array Formel).

 

[Gringo13]

Danke. Hast Du evtl. eine Idee wie ich das Ganze mit einer Array Formel angehen könnte?

Angenommen alle Werte befinden sich in Spalte A, diese wurde bereits per Filter u.a. auf das aktuelle Jahr eingegrenzt. Jetzt soll eine Summe aus dieser Spalte gezogen werden, aber nur wenn Sie einem Wert in Spalte B zugeordnet werden können (in B können jedoch mehrere Werte in einer Zelle vorkommen).

 

[miac]

Öhm, öhm

ich habe einfach mal dein obiges Beispiel aufgelöst, ohne zu wissen, was du machen willst:

{=SUMME(WENN(ISTFEHL(FINDEN("Text1";$O$25:$O$10000));;AF$25:AF$10000))}

Als Arrayformel eingeben, also ohne {} sondern mit <Strg>+<Umsch>+<Eingabe>

 

[Gringo13]

Danke, die Formel funktioniert zwar, aber in der Summe sind auch die Werte aus bereits gefilterten Zellen enthalten. Gibt es die Möglichkeit Teilergebnis in die Formel einzubauen? Oder muss ich nach einer anderen Lösung suchen?

Oder kann ich ISTFEHLER(FINDEN... in meine erste Formel oben einbauen ohne sie zu sprengen?

 

[miac]

als Matrixformel

{=SUMME(TEILERGEBNIS(9;INDIREKT("AF"&ZEILE($25:$10000)))*WENN(ISTFEHL(FINDEN("Text1";O25:O10000));0;1))}

als SUMMEPRODUKT sehe ich da keine einfache Lösung

 

[Gringo13]

Hi Miac, super, vielen Dank, das hat geklappt! Jetzt habe ich noch ein Problem. Bei einem Fall gibt es zwei Komponenten als Text. die Summe soll gezogen werden wenn min. einer der zwei Texte oder beide enthalten sind. Muss ich hier noch eine "ODER" Funktion einbauen?

Oder gibt es einen anderen Weg den zweiten Teil der Formel zu verändern z.B.:

*WENN(ISTFEHL(FINDEN("Text1, TEXT2";O25:O10000));0;1))}

 

[miac]

Tjaja, wichtig ist, daß die Funktionen prinzipiell ja eine Matrix zurückgeben müssen. Bei einigen Funktionen geht das nich, dann muß man mit INDIREKT arbeiten, um diese zu erzeugen. Für den ODER Fall könnte man das auch machen, hier kann man aber die logischen Werte addieren. Dann müßte man aber ein NICHT verwenden. Ich habe darum das UND (Multiplikation) verwendet und WENN umgedreht:

{=SUMME(TEILERGEBNIS(9;INDIREKT("AF"&ZEILE($25:$10000)))*WENN(ISTFEHL(FINDEN("Text1";O25:O10000))*ISTFEHL(FINDEN("Text2";O25:O10000));0;1))}

sonst würde es so aussehen

{=SUMME(TEILERGEBNIS(9;INDIREKT("AF"&ZEILE($25:$10000)))*WENN(NICHT(ISTFEHL(FINDEN("Text1";O25:O10000)))+NICHT(ISTFEHL(FINDEN("Text2";O25:O10000)));1;0))}

beide Lösungen sind gleichwertig, wobei erstere performancegünstiger ist.

 

[Gringo13]

Vielen Dank, hat alles besten geklappt.

Für mein besseres Verständnis; d.h. sollte ich theoretisch weitere Konditionen hier einfügen dann einfach diese miteinender multiplizieren? Quasi wie hier "vereinfacht" für z.B. einen dritten Text:

"TEXT1"*"TEXT2"*"TEXT3"

(Ich habe mich hier auf das erste Beispiel deiner zwei Lösungen bezogen, aber das weißt Du eh...)

 

[miac]

Genau, einfach die ISTFEHL Teile hinzufügen.

 

[Gringo13]

OK; super, dann hab ich's verstanden. Vielen Dank noch einmal!

 

[Gringo13]

Die Formel funktioniert gut, mir ist beim Aktualisieren der Werte folgendes Problem aufgefallen:

Wenn ich die alten Werte/Zeilen Lösche und neue einkopiere reduzieren sich die 10000 auf z.B. 9467


{=SUMME(TEILERGEBNIS(9;INDIREKT("AF"&ZEILE($25:$10000)))*WENN(ISTFEHL(FINDEN("Text1";$O$25:$O$10000))*ISTFEHL(FINDEN("Text2";$O$25:$O$10000));0;1))}


Bei "AF"&ZEILE bleiben die 10000 jedoch bestehen. Kann ich diese "Methode" auch bei den hinteren Teilen mit "Text1" und "Text2" anwenden? oder gibt es eine andere Lösung?

Vielen Dank!

 

[miac]

Habs gerade nochmal ausprobiert. Wenn ich eine Zeile lösche, schreibt Excel überall 9999 rein, auch beim INDIREKT Teil.

Füge ich wieder eine Zeile ein, wird auch die Zahl wieder erhöht. Also eigentlich normal.

 

[Gringo13]

Ich bin meist so vorgegangen:

Alle alten Zeilen löschen und dann die neuen Werte über die hochgerutschten Zellen kopieren (nicht als Zeilen einfügen, da die Formatierung etwas anders ist...wenn dass aber die einfachste Lösung ist ändere ich einfach die Formatierung und füge dann die Neuen Zellen ein, obwohl es hier dann zu Problemen kommen könnte, da u.a. 500 gelöscht und 600 dazu kommen...) Deshalb dachte ich man könnte die 10000 irgendwie fixieren damit die Anzahl flexibel ist, so lange man unter den 10000 bleibt...

 

[miac]

Hmm, ganz verstanden habe ich es nicht. Ich denke aber, Ziel ist es, die gefüllten Bereiche in die Formel zu bekommen.

In dem Fall arbeitet man normalerweise mit BEREICH.VERSCHIEBEN und innerhalb der Funktion mit ANZAHL um die gefüllten Zellen zu bekommen. Dadurch würde man die feste Eingabe der Zeilenanzahl umgehen.