Office: (Office 365) SUMMEWENN mit verschiedenen Kriterien

Helfe beim Thema SUMMEWENN mit verschiedenen Kriterien in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo, ein neuer Versuch. In der folgenden Tabelle habe Daten die mit SUMMEWENN verschieden WGR – Warengruppen zusammenzähle. Die Hardware WGR wird... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von markus73, 20. Oktober 2023.

  1. markus73 Neuer User

    SUMMEWENN mit verschiedenen Kriterien


    Hallo,

    ein neuer Versuch.


    In der folgenden Tabelle habe Daten die mit SUMMEWENN verschieden WGR – Warengruppen zusammenzähle. Die Hardware WGR wird immer gezählt, die Spalte Zubehör nur wenn sie mit einer passenden WAGR vom Zubehör zusammen die gleiche Zahl in der Spalte BON haben.


    Das Ergebnis soll ergeben zu viel Hardware jeweils auf dem gleichen BON ein Zubehör verkauft wurde.


    Wenn ich in der Tabelle die Zubehör WGR zähle, wie die Hardware kommen die Zahlen so raus wie in der Spalte K3 – K4, ich benötige aber das Ergebnis wie in der Spalte K10 – K11, weil nur diese drei Zubehör zusammen mit einer Hardware die Gleiche BON Nummer haben.


    Zwei Zubehör WGR auf einem Bon ergeben keinen Zähler und daher keine Rückmeldung.


    Die Spalte B ARTNO tut hier nichts zur Sache, die Spalte Menge erhöht oder reduziert den Abverkauf

    Vielen Dank für Eure Unterstützung.
     
    markus73, 20. Oktober 2023
    #1
  2. Exl121150 Erfahrener User
    Hallo,

    da du Excel365 hast, habe ich dafür eine Formel gebastelt.
    1) im Zellbereich J3:J6 etwaige Formeln oder Zahlen löschen
    2) in Zelle J3 folgende Formel eingeben/kopieren:
    =LET(wgr; $A$2:$A$56; bon; $D$2:$D$56; menge; $C$2:$C$56; hwWGR; $H$3:$H$6; zbWGR; $J$3:$J$6; hw; WENNFEHLER(VERGLEICH(wgr; hwWGR; 0); 0); fhwBON; FILTER(bon; hw > 0; "nix"); zb; WENNFEHLER(VERGLEICH(wgr; zbWGR; 0); 0); fzbBON; FILTER(bon; zb > 0; "nix"); hwBonZl; WENNFEHLER(VERGLEICH(fzbBON; fhwBON; 0); 0); fhwBonZl; FILTER(hwBonZl; hwBonZl > 0; "nix"); hwzbBON; EINDEUTIG(INDEX(fhwBON; fhwBonZl; 1)); suHwZb; SUMMEWENNS(menge; wgr; zbWGR; bon; hwzbBON); suHwZb)

    In strukturierter Form ist sie etwas leichter verständlich/übersichtlicher:
    SUMMEWENN mit verschiedenen Kriterien upload_2023-10-21_13-16-57.png
    Sie ermittelt die Zubehörmengen aus der Zubehörliste J3:J6, sofern bei einem bestimmten Bon auch Hardware aus der Hardwareliste H3:H6 enthalten ist.
    Sie müsste somit genau die von dir gewünschten Mengen ermitteln.
     
    Exl121150, 21. Oktober 2023
    #2
  3. Exl121150 Erfahrener User
    Hallo,

    beiliegend noch die modifizierte Excel-Datei. In dieser habe ich den Datenbereich A1:D56 in eine strukturierte Tabelle namens "tbBONS" umgewandelt.

    Achtung: Ein Fehler ist im vorigen Posting #2 enthalten:
    Die Formel gehört natürlich nicht in Zelle J3 eingegeben, sondern in Zelle K3
     
    Exl121150, 21. Oktober 2023
    #3
  4. markus73 Neuer User

    SUMMEWENN mit verschiedenen Kriterien

    Super, vielen Dank und Hut ab.
    Das sieht auf den ersten Blick sehr komplex aus. Ich teste das die Tage noch und gebe dir dann noch eine Rückmeldung.

    Vielen herzlichen Dank
     
    markus73, 28. Oktober 2023
    #4
  5. markus73 Neuer User
    anbei die erwähnte Datei aus der Unterhaltung.

    hier funktioniert die Formel leider nicht und ich verstehen den Grund nicht ....
     
    markus73, 5. November 2023
    #5
  6. Exl121150 Erfahrener User
    Hallo,

    ich habe die Formel jetzt nochmals mit deinen neuen Daten getestet und festgestellt, dass sie damit nicht funktioniert - mein Fehler.

    Ich habe dir in den Namensmanager eine kleine benutzerdefinierte Funktion namens "VergleichFilter" eingefügt:
    SUMMEWENN mit verschiedenen Kriterien upload_2023-11-6_11-58-5.png
    Dadurch konnte ich die bisherige LET-Funktion übersichtlicher gestalten. Der eigentliche Fehler lag in der letzten Zeile der LET-Funktion in der SUMMEWENNS-Funktion. Dadurch, dass ich diese in eine REDUCE-Funktion gekapselt habe, konnte ich den Fehler beseitigen. In Zelle BJ5 ist jetzt folgende geänderte LET-Funktion enthalten:
    SUMMEWENN mit verschiedenen Kriterien upload_2023-11-6_13-4-54.png
    Ferner habe ich die LET-Funktion in Zelle BH5 effizienter Excel365-like gestaltet.
    Geänderte Datei liegt bei.
     
    Exl121150, 6. November 2023
    #6
  7. markus73 Neuer User
    Super, vielen Dank. Ich bau das die Tage ein.
     
    markus73, 6. November 2023
    #7
  8. markus73 Neuer User

    SUMMEWENN mit verschiedenen Kriterien

    Es tut mir echt leid, ich bekommen die Formel nicht adaptiert ...
    die ganze Datei besteht aus mehreren solchen Tabellen, ich hab jetzt noch eine weitere hinzugefügt und bekomme wieder einen #WERT Fehler
     
    markus73, 6. November 2023
    #8
  9. Exl121150 Erfahrener User
    Hallo,

    da es in der Tabelle "Artikel" keine BONs gibt, die gleichzeitig die angegebenen Hardware-WGR und Zubehör-WGR enthalten, muss bei allen Zubehör-WGR ein 0-Resultat die Folge sein.
    Da ich damit nicht rechnete bei meiner Formelerstellung, habe ich jetzt die im Namensmanager enthaltene Funktion "VergleichFilter" geringfügig modifiziert, indem ich die beiden Wörter "nix" durch eine 0 ersetzt habe.
    SUMMEWENN mit verschiedenen Kriterien upload_2023-11-7_17-10-19.png
    Die LET-Formel habe ich nicht geändert. Sie liefert in einem solchen Fall lauter 0 als Menge bei den Zubehör-WGR.
    Modifizierte Datei liegt bei.
     
    Exl121150, 7. November 2023
    #9
  10. markus73 Neuer User
    Hallo,

    ich kann dir nicht sagen was hier genau passiert, wenn ich (auch bei deiner Tabelle) nur eine Zahl ändere springt die Auswertung komplett auf #Wert um und ich kann es nur zurückstellen wenn ich schließe ohne speichern und neu öffne.

    gibt es nicht noch eine andere Möglichkeit, gerne auch etwas umständlicher ?
    Tut mir echt leid, das ich solche Umstände mache ...
     
    markus73, 7. November 2023
    #10
  11. Exl121150 Erfahrener User
    Hallo,

    in der beiliegenden Datei habe ich dir im Arbeitsblatt "Attach" in den Spalten/Zellen BR5:CA5 exakt die 10 Formeln eingefügt, die auch in der LET()-Funktion der Zelle BO5 enthalten sind. Auf diese Weise kannst du mitverfolgen, was sich da tut.

    1) In den ersten 3 Spalten (BR,BS,BT) sind einfach die Daten der Tabelle "Artikel" (Spalte WGR, BON, Menge) enthalten.

    2) In den nächsten beiden Spalten (BU,BV) mit Kopf "hwWGR" und "zbWGR" sind die Hardware-WGR und Zubehör-WGR deines jeweiligen Abfragemodells enthalten (zB. aus 2.Modell die Zellbereiche BL5:BL8 und BN5:BN20 - oder aus 1.Modell die Zellbereiche BG5:BG13 und BI5:BI62).

    3) In den beiden folgenden Spalten (BW,BX) mit Kopf "fhwBON" und "fzbBON" sind die Bonnummer-Listen, die Hardware-WGR bzw. Zubehör-WGR enthalten, aufgeführt.

    4) Dann folgt die Spalte BY mit Kopf "fhwBonZl". Hier sind die Zeilennummern aus der Hardware-Bon-Liste "fhwBON", die Zubehör-WGR enthalten, aufgelistet. Dabei beziehen sich die Nummern nicht auf die Zeilennummern des Arbeitsblattes, sondern auf die Zeilennummer innerhalb der Liste: Nr.1 ist Zeilennr.5 des Arbeitsblattes = Zelle BW5, Nr.2 = Zelle BW6, usw.).
    Da es bei deinem 2.Modell keine Bonnummern gibt, die eine Kombination aus aufgelisteter Hardware-WGR und Zubehör-WGR enthalten, können dort auch keine Zeilennummern erscheinen - stattdessen wird eine 0 angezeigt.

    5) In der Spalte BZ mit Kopf "hwzbBON" sind die Bonnummern, die zu den Zeilennummern aus Spalte BY gehören, aufgelistet. Da in deinem 2.Modell eine Zeilennr.=0 in Zelle BY5 enthalten ist, wird auch in BZ5 nur eine 0 angezeigt. Hier habe ich eine kleine Verbesserung in der LET-Funktion eingefügt, sodass im 0-Fall keine überflüssigen Bonnummern angezeigt werden, was aber auf das Resultat aus Punkt 6) keinen Einfluss hat.

    6) Schlussendlich werden in der Spalte CA mit Kopf "suHwZb" die Mengen aufsummiert von Bons mit Hardware-WGR, die ebenfalls eine entsprechende aufgelistete Zubehör-WGR enthalten. Diese Liste muss genauso lang sein, wie die Liste der Zubehör-WGR in Spalte BV (Kopf "zbWGR"). Da in deinem 2.Modell keine entsprechenden Bonnummern gefunden wurden, werden in der Spalte CA natürlich auch nur 0-Mengen aufgelistet.

    Diese Instabilität der Excel-Datei kann nichts mit meinen Funktionen zu tun haben. Es kann höchstens der Fall eintreten, dass bei zu viel an Daten in der Tabelle "Artikel" der benötigte Arbeitsspeicher im Hintergrund der Datei zu klein wird.

    Modifizierte Datei liegt beil.
     
    Exl121150, 8. November 2023
    #11
Thema:

SUMMEWENN mit verschiedenen Kriterien

Die Seite wird geladen...
  1. SUMMEWENN mit verschiedenen Kriterien - Similar Threads - SUMMEWENN verschiedenen Kriterien

  2. Wenn/summewenn

    in Microsoft Excel Hilfe
    Wenn/summewenn: Guten Tag Ich bin neu hier in diesem Forum. Ich habe schon gesucht, aber wurde leider nicht fündig. Ich habe eine Tabelle mit verschiedenen Werten in den jeweiligen Zellen. Mein Ziel ist, dass...
  3. SUMMEWENN bei aufgeblendeten/gefilterten Zeilen

    in Microsoft Excel Hilfe
    SUMMEWENN bei aufgeblendeten/gefilterten Zeilen: Hallo, ich steh mal wieder auf dem Schlauch. Ich habe eine Beispieltabelle angehängt. Ich habe hier eine Liste, wo wir unsere Kunden eintragen, die bei uns Bar bezahlen. Dabei gibt es normale...
  4. SUMMEWENN oder SUMMEWENNS zum verzweifeln

    in Microsoft Excel Hilfe
    SUMMEWENN oder SUMMEWENNS zum verzweifeln: Hallo zusammen, manchmal ist es viel schwerer als man denkt. Ich verzweifle an einer Formel die mir anfangs ganz einfach zu sein schien aber anscheinend sind meine Excel Kenntnisse nicht...
  5. Summe aus Zellen, wenn Bedingungen in 2 Spalten stimmen

    in Microsoft Excel Hilfe
    Summe aus Zellen, wenn Bedingungen in 2 Spalten stimmen: Hallo zusammen, ich habe diverse Foren durchforstet, bin aber zu keiner Lösung gekommen. Folgende Anforderung: Die Zahlen/Summen in Zellen der Spalte I (I29:I84), sollen summiert werden, wenn in...
  6. Hilfe bei Formel ggf. Summewenns?

    in Microsoft Excel Hilfe
    Hilfe bei Formel ggf. Summewenns?: Hallo zusammen, Ich hoffe jemand kann mir weiterhelfen. Ich würde gerne für meine Bestellungen für einen Online-Shop eines Monats (z.B. April) die Rücksendequote berechnen. Da Kunden jedoch 30...
  7. Summewenns Funktion mit Wenn Funktion

    in Microsoft Excel Hilfe
    Summewenns Funktion mit Wenn Funktion: Hallo zusammen, ich möchte in einer Summewenns Funktion eine Wenn Funktion einbauen, jedoch klappt dies nicht. Es sollen Werte summiert werden, wenn ein definiertes Datum in einem gewissen...
  8. Summewenns erst, wenn bestimmter Wert erreicht

    in Microsoft Excel Hilfe
    Summewenns erst, wenn bestimmter Wert erreicht: Hallo zusammen, ich komme mit meinen eingeschränkten Excel-Kenntnissen nicht weiter. Ich muss die Daten in der angehängten Tabelle auswerten. Ich möchte jetzt gerne wissen, wie oft eine...
Schlagworte:
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden