Office: (Office 2010) Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen

Helfe beim Thema Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo, ich habe folgendes Problem, ich möchte mehrer Suchkriterien - Textteile - in einer Spalte zählen bzw. addieren. Da auch noch das nach... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von peterpan2007, 27. Februar 2017.

  1. Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen


    Hallo,

    ich habe folgendes Problem, ich möchte mehrer Suchkriterien - Textteile - in einer Spalte zählen bzw. addieren.

    Da auch noch das nach Kalenderjahr Unterschieden werden muß verwende ich Summenprodukt.
    Solange ich nur nach einem Textteil suche ist das kein Problem. Alle Versuche aber mehreren Textteile gleichzeitig suchen zu lassen, ohne die Formeln einfach hintereinander zu hängen, kommt immer #WERT als Ergebnis heraus.

    Formel für einen Textteile:
    =SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)

    Nicht akzeptabele Formel für mehreren Textteile (in diesem Fall drei Stück):
    =SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)+SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)+SUMMENPRODUKT(--(JAHR(Daten!$E$2:$E$100)=$C$3);--(Daten!$A$2:$A$100=$A4);--(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))));Daten!$B$2:$B$100)


    Einfach gesagt, diesen Teil der Formel für mehrere Suchbegriffe:
    --(ISTZAHL(SUCHEN($A$2;(Daten!$F$2:$F$100))))

    Bei 100.000 Datensätzen braucht Excel Stunden... , bitte nicht Pivot vorschlagen oder VBA

    Kann man die Suchbegriffe irgendwie mit "ODER" Verknüpfen, ich bin mit meinem Latein am Ende.
    Eine Testdatei zum ausprobieren findet Ihr im Anhang.

    Schon mal Danke für eure Hilfe im Voraus
     
    peterpan2007, 27. Februar 2017
    #1
  2. BoskoBiati Erfahrener User
    Hi,

    kein Wunder, wenn man mit Summenprodukt arbeitet!

    Code:
    =SUMMENPRODUKT((JAHR(Daten!$E$2:$E$100)=C$34)*(Daten!$A$2:$A$100=$A36)*((ISTZAHL(SUCHEN($A$32;(Daten!$F$2:$F$100))))+(ISTZAHL(SUCHEN($A$33;(Daten!$F$2:$F$100))))+(ISTZAHL(SUCHEN($A$34;(Daten!$F$2:$F$100))))>0);Daten!$B$2:$B$100)
    wesentlich ressourcenschonender wäre das:

    Code:
    =SUMMEWENNS(Daten!$B:$B;Daten!$A:$A;$A36;Daten!$E:$E;">=1.1."&C$34;Daten!$E:$E;"<=31.12."&C$34;Daten!$F:$F;"*"&$A$32&"*")+SUMMEWENNS(Daten!$B:$B;Daten!$A:$A;$A36;Daten!$E:$E;">=1.1."&C$34;Daten!$E:$E;"<=31.12."&C$34;Daten!$F:$F;"*"&$A$33&"*")+SUMMEWENNS(Daten!$B:$B;Daten!$A:$A;$A36;Daten!$E:$E;">=1.1."&C$34;Daten!$E:$E;"<=31.12."&C$34;Daten!$F:$F;"*"&$A$34&"*")
     
    BoskoBiati, 3. März 2017
    #2
  3. BoskoBiati Erfahrener User
    Hallo,

    ein paar Hilfsspalten:

    Arbeitsblatt mit dem Namen 'Daten'
    *ABCDEFGHIJK
    1Lieferantennr.MengePreisWertDatumBezeichnungJahrNameAlexHansPeter
    27002111,00 €1,0002.01.2012blablabla Alex blablabla2012Alex100
    37007711,00 €1,0002.01.2012blablablabla Alex blabla2012Alex100
    47050411,00 €1,0002.01.2012blabla Alex blablablabla2012Alex100
    57079911,00 €1,0002.01.2012blablabla Alex blablabla2012Alex100
    67082911,00 €1,0002.01.2012blablablabla Alex blabla2012Alex100
    770021210,00 €20,0002.01.2013blablabla Peter blablabla2013Peter003
    ZelleFormel
    G2=JAHR(E2)
    H2=INDEX($I$1:$K$1;MAX($I2:$K2))
    I2=ISTZAHL(SUCHEN(I$1;$F2))*SPALTE(A$1)
    J2=ISTZAHL(SUCHEN(J$1;$F2))*SPALTE(B$1)
    K2=ISTZAHL(SUCHEN(K$1;$F2))*SPALTE(C$1)
    Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

    und Du kannst das wunderbar mit einer Pivot auswerten (braucht keine Stunden mehr!)

    Arbeitsblatt mit dem Namen 'Tabelle1'
    *ABCDEFG
    1Jahr(Alle)*****
    2*******
    3*Spaltenbeschriftungen*****
    4*Alex*Hans*Peter*
    5ZeilenbeschriftungenStückzahlGesamtwertStückzahlGesamtwertStückzahlGesamtwert
    670021113300220
    770077113300220
    870504113300220
    970799113300220
    1070829113300220
    Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
     
    Zuletzt von einem Moderator bearbeitet: 30. November 2020
    BoskoBiati, 3. März 2017
    #3
  4. Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen

    Hallo BoskoBiati,

    klappt einwandfrei, vielen Dank.

    Gruß Alex
     
    peterpan2007, 8. März 2017
    #4
Thema:

Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen

Die Seite wird geladen...
  1. Summenprodukt - mehrere Suchkriterien - Textteile- in einer Spalte zählen - Similar Threads - Summenprodukt Suchkriterien Textteile

  2. Summewenns mit Datum und Zwei Buchstaben eingrenzen

    in Microsoft Excel Hilfe
    Summewenns mit Datum und Zwei Buchstaben eingrenzen: Guten Abend Zusammen, ich versuche aus einer Tabelle mit Summewenns (sumifs) die Beträge vom Kalendermonat abweichend zu errechnen. Das funktioniert mittlerweile auch. Da das Datum über den...
  3. Summenprodukt mit wenn funktion

    in Microsoft Excel Hilfe
    Summenprodukt mit wenn funktion: Hallo zusammen, ich benötige Hilfe bzgl. meiner Berechnung. Ich habe schon vieles ausprobiert........., immer falsches Ergebnis Es soll wie folgt gerechnet werden: wenn unter Preis (Spalte...
  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. SUMMENPRODUKT + INDIREKT

    in Microsoft Excel Hilfe
    SUMMENPRODUKT + INDIREKT: Hallo, ich bräuchte mal wieder eure Hilfe :) Ich hab eine Tabelle gebastelt in der ich ein Summenprodukt aus variablen Textteilen haben möchte. Es ist ein selbtgebastelter Filter aus einer anderen...
  6. Summenprodukt mit Abhängigkeit in VBA

    in Microsoft Excel Hilfe
    Summenprodukt mit Abhängigkeit in VBA: Hallo Zusammen, bin leider bei den vorhandenen Beiträgen nicht fündig geworden. Als Summenprodukt pro Zeile bekomme ich das hin, aber nicht in VBA. [ATTACH] Dies ist nur ein Auszug zur...
  7. Summenprodukt in gefilterten Zeilen

    in Microsoft Excel Hilfe
    Summenprodukt in gefilterten Zeilen: Hallo zusammen, in einer sehr umfangreichen Datenbank,die sich ständig erweitert, werden über mehrere Filter in verschiedenen Spalten nur noch bestimmte Zeilen angezeigt. Nun soll nur aus den...
  8. INDEX SUMMENPRODUKT Zeile wiedergeben mit 2 Suchkriterien

    in Microsoft Excel Hilfe
    INDEX SUMMENPRODUKT Zeile wiedergeben mit 2 Suchkriterien: Hallo, ich möchte eine übersichtliche Auswertung von meinen Verkaufskennzahlen erstellen. Die Kennzahlen kann ich mir aus meiner Branchensoftware exportieren. Das ist mein Datenblatt...
  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