>> SummeProdukt + SummeWenns

[steve1da]

Natürlich kommt bei beiden nichts raus denn in Spalte A steht nirgendwo "B6" oder "B7".

[stoxxii]

Hi Steve1,

klr, das war doofe von mir, die Zelle B6 in Zeichen zu verpacken.

Aber was mir in der Nachct eingefallen ist :  ich brauche dochso etwas wie eine Wenn-Bedingung  ??

Code:

=SUMMENPRODUKT(WENN(Capex!A8:A205=B8;Capex!H8:H205*(Capex!D8:D205=1)+(Capex!D8:D205="new")))     (eigentlich von A6:A203)

Leider funzt diese auch nicht ...  Aber WERT - Fehler erscheint schon mal ... 

Irigendeine Abfrage muss doch funktionieren...

beste grüße
stoxxii

[BoskoBiati]

Hi,

Code:

SUMMENPRODUKT((Capex!A8:A205=B8)*Capex!H8:H205*((Capex!D8:D205=1)+(Capex!D8:D205="new")))

[stoxxii]

nee , sorry  deine Funktion scheint auch nicht zu funktionieren  

ich habe noch eine Variante mit SummeWennS 

Code:

=SUMMENPRODUKT(SUMMEWENNS(Capex!H8:H205;Capex!A8:A205;B8&"*";Capex!D8:D205;1;Capex!D8:D205;"new"))

natürlich funzt diese auch nicht ....
obwohl ich die reine  SummeWennS  so direkt mit der Standort-Abfrage in Spalte "A" mit dem "Wildcard-Symbol" erfolgreich umgesetzt habe, aber nur 
für eine Abfrage-kriterium  aus Spalte " D".

Das wird jetzt hier zum absoluten "Gambling"   

vielleicht rettet mir noch jemand das Weekend.
danke vorab.

beste grüße
stoxxii

[Egon12]

Hallo,

das sollte deine Wünsche erfüllen:

Code:

=SUMMENPRODUKT((ISTZAHL(FINDEN(B6;Capex!$A$6:$A$10000)))*(Capex!$D$6:$D$10000="new")*(Capex!$H$6:$H$10000))

ab O2021 wäre dies das Mittel der Wahl (erst mal noch ungespillt):

Code:

=WENNFEHLER(SUMME(FILTER(Capex!H6:H10000;(ISTZAHL(FINDEN(B6;Capex!A6:A10000)))*(Capex!D6:D10000="new")));"")

Gruß Uwe

[Andreas Killer]

Code:

=SUMMENPRODUKT(Capex!H7:H204*(Capex!A7:A204 = "B7")*((Capex!D7:D204=1)+(Capex!D7:D204="new"))>0)

Leider kommt da beiden nix  heraus.  

Mit Bereinigen habe ich nicht gemeint die Daten zu kürzen, sondern die Nullstrings aus den Daten zu entfernen. Mit egal welchen Formeln wirst Du immer Probleme haben eine wirklich leere Zelle und eine mit Nullstring zu unterscheiden.

Daher der Tip vom Profi: Zusammenfassungen immer mit einer Pivottabelle machen, das Formelgedöns ist zwar nett und hilfreich bei Kalkulationsmodellen um sofort ein Ergebnis zu sehen und verschiedene Szenarien zu untersuchen. Darüber hinaus... überaus fehleranfällig und statisch, denn selbst die perfekten Formeln können NIEMALS neue Kriterien erkennen die plötzlich in den Daten beim Aktualisieren auftauchen.

Damit Du mal eine Idee bekommst wie kompliziert der Krams wird den Du da probierst, meine letzte Beispieldatei erweitert:
   

Datei:

  _Excel - Training __SummeProdukt__uwe.xlsx (Größe: 70,34 KB / Downloads: 1)

Links in Spalte A:C sind meine Pivottabellen, die Kriterien für die Formeln stehen in E:F, die Formeln in G:H, alle Werte die nicht mit der PT übereinstimmen sind eingefärbt.
Eine Besonderheit gibt es in E4:  ="" damit wir die Nullstrings in den Daten finden können.

Die SUMMEWENNS sind alle gleich alá "bilde die Summe eines Kriteriums":
G2:  =SUMMEWENNS('Capex Detail'!$H$6:$H$600;'Capex Detail'!$D$6:$D$600;E2)

Wie wir sehen kommt SUMMEWENNS überhaupt nicht damit zurecht den Unterschied zwischen "" und einer leeren Zellen zu erkennen. Insbesondere funktioniert eine leere Zelle als Kriterium bei allen WENNS-Formeln in Excel gar nicht.

Wenn wir in die Daten gehen, den Filter so setzen das wir nur leere Zellen sehen, dann beinhaltet das auch die Zellen mit den Nullstrings. Markieren wir nun alle Zellen in der Spalte und drücken einmal ENTF, dann passt die SUMMEWENNS die auf ="" prüft. Nun ja, es gibt noch andere Fallstricke bei den WENNS, nur das Du das mal gehört hast.

Kommen wir zu SUMMENPRODUKT.
H2:  =SUMMENPRODUKT('Capex Detail'!$H$6:$H$600*('Capex Detail'!$D$6:$D$600=E2))
Das Ergebnis ist falsch, weil eine Formel =A1 eine 0 zurückgibt wenn die Zelle leer ist. Die Formeln in Spalte I sind je nach Kriterium anders, damit am Ende die korrekten Werte raus kommen.

Soweit so einfach, jetzt willst ja auch noch zusätzlich alles was mit z.B. "B1" in Spalte A anfängt als 2tes Kriterium summerien.
H11:  =SUMMENPRODUKT('Capex Detail'!$H$6:$H$600*('Capex Detail'!$D$6:$D$600=E11)*('Capex Detail'!$D$6:$D$600<>"")*('Capex Detail'!$A$6:$A$600=F11))

Das klappt logischer Weise nicht weil in Spalte A "B1 - 25" steht und nicht "B1". Netter Weise gibt es da auch noch "HH1 - 22", also brauchen wir eine Formel die uns den linken Teil vor dem "-" zurückgibt, sowas wie =GLÄTTEN(LINKS(A6;SUCHEN("-";A6)-1))

Nun gibt es in Deinen Daten in Spalte aber auch Leerzeilen und in denen liefert das SUCHEN dann einen Fehler, d.h. wird müssen den auch noch abfangen. Und dann haben wir ein Formelmonster:
I11:  =SUMMENPRODUKT('Capex Detail'!$H$6:$H$600*('Capex Detail'!$D$6:$D$600=E11)*('Capex Detail'!$D$6:$D$600<>"")*(GLÄTTEN( LINKS('Capex Detail'!$A$6:$A$600;WENNFEHLER( SUCHEN("-";'Capex Detail'!$A$6:$A$600);1)-1))=F11))

Und für alle anderen Kriterien musst Du nun die passenden SUMMENPRODUKT-Formeln nach dem gleichem Schema zusammenbauen.

Andreas.

[schauan]

egal ob Deine Formel

=SUMMENPRODUKT(WENN(Capex!A8:A205=B8;Capex!H8:H205*(Capex!D8:D205=1)+(Capex!D8:D205="new")))

sinnvoll ist oder nicht, #WERT erscheint, weil in dem betroffenen Bereich auch Texte stehen. Leerzeichen sind auch Texte, Andreas hat ja darauf hingewiesen. Stehen dort keine Texte, gibt's ein Ergebnis.

[stoxxii]

Hi Andreas,

erstmal großes Dankschön für die detaillierte Erläuterungen   

So viel habe ich als NOOB gar nicht verdient.

Aber im mich kurz zu fassen : eine KRITIK  bzw. Verteidigung für meine Tabelle sei mir erlaubt   

Meine echte Arbeits-Firmen-Excel hat 1000 Zeilen und ca. 50 Spalten. Mag sein, unsauber, was ich nie bemerkt hatte.

und ich greife die SummeWennS , wie hier gezeigt,  über 2 Sheets ab : 
und insbes. die 1.Abfrage : Standort , das mit dem WildCard - Symbol 

und das funzt gnadenlos korrekt über 40 Standorte mit der Bezeichnung aus Spalte "A" (Sheet Capex) .

Ich war nur nicht n der Lage :   2 Kriterien oder gar 3 Kriterien aus SPALTE "D"" abzufragen (Sheet Capex). 
 Daran bin ich gescheitert, weil ich NOOBi bin, wie ihr seht. 

Ich versuche noch eine kürzere Variante zu finden.

Euch allen erstmal Schönes Frostfreies Weekend.
bestes
stoxxii

[stoxxii]

Hi Steve1,

hier müsste ich dich noch mal fragen:  Wieso nimmst du nicht nur :  LINKS(A6:A203; 3)   ??  :   Wäre 3 nicht die Länge ??

und wie bitte würde man die Text - Abfrage schreiben, wenn man über 2 Sheets geht , 
also die Formel in Sheet "Budget" steht
aber die Abfrage im Sheet "Capex" erfolgt ??

Bei mir produziere ich immer eine Error - siehe im Anhang.

beste grüße
stoxxii

[steve1da]

Ja, 3 ist die Länge. Mit dem Wert aus B8 funktioniert das dann aber nicht mehr.
Und wie man das genau mit Links() genau schreibt steht hier mehrere Male, zuletzt in Andreas ausführlicher Antwort. 
…..links(Capex!A6:A200;3)=….
Scheinbar liest du dir die Antworten nicht wirklich durch.