Office Anwender Forum

Office Anwender Forum

Posts 1-10 of 11
  • Thea BEER
    Thea BEER
    The company name is only visible to registered members.
    Summewenn mit zwei Bedingungen
    Hallo Forum, ich steh auf dem Schlauch...
    wie bilde ich Summewenn, wenn ZWEI Bedingungen erfüllt sein sollen?

    SUMMEWENN('Tabelle1'$C$5:'Tabelle1'$C$10000;summation!$A3;'Tabelle1'!$N$5:'Tabelle1'!$N$10000)
    kurz: summewenn(Suchbereich1; Suchkriterien1;Summebereich)

    Das geht (ist ja auch einfach..)

    Das soll nun ergänzt werden um die zweite Bedingung, d.h. ein zweiter Suchbereicht soll einem zweiten Suchkriterium entsprechen. Und nur summiert werden, was bei beiden Bedingungen "wahr" liefert.

    Dachte summewenn(und(Suchbereich1; Suchkriterien1);(Suchbereich2; Suchkriterien2);Summebereich) würde tun, aber da kommt das Trennungsproblem ins Spiel:
    Wenn ich mit "und" schachtele meint Excel nach dem Strichpunkt käme die zweite Bedingung für "und" aber es soll die "Summewenn"-Bedingung sein....

    Wer hat Rat?
    Vielen Dank!
    Thea Beer
    • 24 May 2007, 5:22 pm
  • Markus Lenter
    Markus Lenter    Premium Member
    The company name is only visible to registered members.
    Re: Summewenn mit zwei Bedingungen
    Hallo,

    Unterstellt man folgende Tabelle ab Zelle A1:
    50 30 1
    1000 70 2
    50 70 3
    300 20 4
    50 70 5

    So ergibt die Formel =SUMME(WENN(A1:A5=50;C1:C5;0)*WENN(B1:B5=70;1;0)) den Wert 8, sofern die Eingabe mit Strg Shift Return abgeschlossen wird (nicht nur Return, das funktioniert nicht). Dadurch arbeitet er jeden Bereich aus A1:A5 ab, vergleicht ihn mit 50 und gibt bei Erfolg den korrespondierenden Wert aus C1:C5 zurück. Gleiches mit dem Bereich B1:B5, welcher 1 zurück gibt, wenn die zweite Bedingung erfüllt ist. Das Ganze wird summiert.
    In meinem Beispiel schien das zu funktionieren. Absurder Weise verschluckt sich Excel jedoch, wenn man statt dieses seltsamen Konstrukts der Multiplikation mit 1 und 0 die UND-Funktion benutzen möchte (warum auch immer...).

    Weniger fehlerträchtige Lösung: Ergebnis der zusammengesetzten Wenn-Bedingung in eine extra-Spalte ausgeben, und diese mit SUMMEWENN bearbeiten.

    Gruß

    Markus Lenter
    This post was modified on 24 May 2007 at 06:40 pm.
    • 24 May 2007, 6:34 pm
  • Bernd J. Mocker
    Bernd J. Mocker    Premium Member
    The company name is only visible to registered members.
    Re^2: Summewenn mit zwei Bedingungen
    Hallo Herr Lenter,

    ich habe verstanden wenn in der Spalte A ein Wert = 50 steht soll der korrespondierende Wert aus Spalte C summiert werden. Weiterhin soll 1 addiert werden wenn in Spalte B ein Wert = 70 steht.
    Bei dieser Aufgabe lautet das korrekte Ergebnis 12 und kann mit {=SUMME(WENN(A1:A5=50;C1:C5;0)+WENN(B1:B5=70;1;0))} ermittelt werden.

    Lautet die Augabe für jedes Vorkommen des Wertes 50 in Spalte A und des Wertes 70 in Spalte B den Wert 1 addieren so lautet das Ergebnis 6 {=SUMME(WENN(A1:A5=50;1;0)+WENN(B1:B5=70;1;0))}

    Ich habe noch ein paar andere Varianten durchgespielt, 8 erhalte ich nur bei Ihrer Formel. Ich kann aber nicht nachvollziehen was hier berechnet wird.

    Bitte nicht falsch verstehen, soll keine Kritik sein, ich möchte verstehen.

    Besten Gruß

    Bernd J. Mocker
    • 24 May 2007, 7:36 pm
  • Post visible to registered members
  • Jürgen Lechner
    Jürgen Lechner    Premium Member   Group moderator
    The company name is only visible to registered members.
    Re: Summewenn mit zwei Bedingungen
    Hallo,

    das ganze funktioniert am besten mit den von meinen Vorpostern erwähnten Matrixfunktionen. Dummerweise kann eine Matrixfunktion nicht mit den logischen Funktionen UND() und ODER() umgehen.

    Einfaches Beispiel:

    12
    1
    34
    22
    12
    24

    {=SUMME(WENN(A1:A6>20;1;0))} liefert als Ergebnis eine 3.

    WENN(A1:A6>20;1;0) liefert eine Matrix mit 0;0;1;1;0;1, welche durch die SUMME zum Ergebnis "Anzahl der Werte größer 20" addiert wird.

    Wenn ich nun das Beispiel von vorhin aufgreife, bei dem die Kombination von 50 und 70 zur Addition des Wertes in Spalte C führt

    Also

    50 30 1
    1000 70 2
    50 70 3
    300 20 4
    50 70 5

    ergibt 8.

    Dann sollte man zum besseren Verständnis mit einer einfachen Variante der Funktion anfangen. Nämlich:

    =SUMME(WENN(A1:A5=50;WENN(B1:B5=70;C1:C5;0);0))

    Achtung: mit Shift+Strg+Enter abschließen.

    D.h. Wenn einer der Werte von A1 bis A5 = 50 ist, dann wird in den Zweig verwiesen, bei dem in einer weiteren WENN-Funktion die zweite Spalte abgefragt wird. D.h. bei B1 bis B5 = 70, das nur für die Werte aus B1:B5 abgefragt wird, bei denen in Spalte A die Prüfung auf 50 WAHR ergab, wird der entsprechende Wert aus Spalte C zurückgegeben. Alle Wert bei denen die erste oder die zweite Prüfung FALSCH ergaben liefern 0 zurück. Damit habe ich de facto ein logisches UND. D.h. die Limitation der Matrixfunktionen, weder UND() noch ODER() zu unterstützen, muss man durch Schachtelung von WENN-Funktionen kompensieren. Das kann für ODER() recht komplex werden.

    Die Variante von Herrn Lenter ist auch korrekt, allerdings mathematisch "ausgefeilter". ;-)

    Viele Grüße
    Jürgen Lechner
    • 24 May 2007, 9:41 pm
  • Jürgen Lechner
    Jürgen Lechner    Premium Member   Group moderator
    The company name is only visible to registered members.
    Re^2: Summewenn mit zwei Bedingungen
    Mathias schrieb:

    irgendeinen Kram, der sich nicht zitieren lässt
    SCNR :-)

    Die Funktion tut es natürlich besser. Ich habe es gerade unter Excel getestet. Auch die Variabilität des Bedinungsbereiches dürfte identisch sein. Z.B. hat Excel die Änderung von "70" in "<=70" sofort richtig interpretiert.

    Ich sollte mir die Gruppe der DB-Funktionen mal wieder ins Gedächtnis rufen. Da ich meist mit Datenbanken arbeite geht das glatt unter.

    Viele Grüße
    Jürgen Lechner
    This post was changed on 14 Nov 2008 at 08:12 am by Christoph Becker .
    • 24 May 2007, 9:55 pm
  • Bernd J. Mocker
    Bernd J. Mocker    Premium Member
    The company name is only visible to registered members.
    Re: Summewenn mit zwei Bedingungen
    Hallo,

    jetzt habe ich die Aufgabe auch verstanden. Es war wohl zu heiß heute ;-)

    In Excel 2007 funktioniert das Ganze mit =SUMMEWENNS(C1:C5;A1:A5;50;B1:B5;70) und normalem Return.

    Meines Wissens gibt es die Funktion SUMMEWENNS erst ab Vers. 2007.

    BG Bernd J. Mocker
    • 24 May 2007, 10:15 pm
  • Thea BEER
    Thea BEER
    The company name is only visible to registered members.
    Re^2: Summewenn mit zwei Bedingungen
    Mangels Excel 2007 muss ich mir dann wohl die Datenbank/Matrizenfunktionen genauer ansehen...
    Vielen Dank für Ihre Lösungsvorschläge! Ein schönes Wochenende, Thea Beer
    • 25 May 2007, 09:26 am
  • Dr. Jörg Müller
    Dr. Jörg Müller    Premium Member
    The company name is only visible to registered members.
    Re^3: Summewenn mit zwei Bedingungen
    Liebe Experten!

    "mit Shift+Strg+Enter abschließen"

    Was hat es denn mit dieser Tastenkombination auf sich?? Habe davon noch nie gehört.

    Grüße,

    Jörg Müller
    • 22 Jan 2008, 12:47 pm
  • Jürgen Lechner
    Jürgen Lechner    Premium Member   Group moderator
    The company name is only visible to registered members.
    Re^4: Summewenn mit zwei Bedingungen
    Hallo,

    Dr. Jörg Müller schrieb:
    Liebe Experten!
     
    "mit Shift+Strg+Enter abschließen"
     
    Was hat es denn mit dieser Tastenkombination auf sich?? Habe davon noch nie gehört.
    Das definiert eine Formel als Matrixformel.

    Beispiel:

    WENN(A1>50;1;0)

    ist ja noch verständlich und wird meist in Hilfsspalten verwendet, die man dann heimlich versteckt. :-) Wenn man das mit 10 Zellen macht, dann kann man die Werte addieren und erhält die Anzahl aller Werte über 50.

    WENN(A1:A10>50;1;0)

    fragt 10 Zellen ab und liefert dementsprechend ein Array von 10 Werten. Die passen natürlich nicht in eine Zelle. Aber man kann sie direkt addieren:

    SUMME(WENN(A1:A10>50;1;0))

    Wenn man die Formel mit Enter abschließt, dann kommt der Fehler #WERT!, wenn man sie mit Shift+Strg+Enter abschließt, dann tut sie das Gewünschte.

    Viele Grüße
    Jürgen Lechner
    • 22 Jan 2008, 2:19 pm