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. Teilergebnis bei Summenprodukt aufgrund Filter

    in Microsoft Excel Hilfe
    Teilergebnis bei Summenprodukt aufgrund Filter: Liebe Community, für eine Planungsdatei habe ich eine Formel hinterlegt, die mir freie Verfügbarkeiten errechnet. Sofern in Spalte A, Zelle XY ein Wert hinterlegt ist, ist zu prüfen, ob sowohl die...
  3. Zählenwenns mit externem Bezug (Matrixformel)

    in Microsoft Excel Hilfe
    Zählenwenns mit externem Bezug (Matrixformel): Hallo in die Runde, ich habe folgende Herausforderung, bei der ich eure Hilfe brauche. Ich habe 1 Auswertungsdatei, die mir aus einer Anzahl an wachsenden Exceldateien auswerten soll, wie oft...
  4. NV Fehler bei Summenprodukt

    in Microsoft Excel Hilfe
    NV Fehler bei Summenprodukt: Hallo zusammen ich hätte mal eine Frage zu einer von mir aufgestellten Formel, die unter einer Bedingungen funktioniert, sobald ich allerdings etwas hinzufüge wird mir der NV-Fehler angezeigt....
  5. Summewenn und Summenprodukt für bestimmten Monat aus Datum über mehrere Blätter

    in Microsoft Excel Hilfe
    Summewenn und Summenprodukt für bestimmten Monat aus Datum über mehrere Blätter: Hallo zusammen, ich stehe vor einem von mir leider unlösbaren Problem... Ich habe ein Excel Dokument mit Umsätzen pro Tag, die in einzelne Jahre aufgeteilt werden. Also ich habe mehrere Blätter...
  6. Summenprodukt erfasst leere Felder

    in Microsoft Excel Hilfe
    Summenprodukt erfasst leere Felder: Hallo zusammen ich habe eine Liste für meine Umsätze. In einer Spalte Rechnungsdatum, daneben eine Spalte für das Datum, an dem die Zahlung eingegangen ist. Und in der dritten Spalte den...
  7. Summe Wenn mit Filter -> Teilergebnis und Summenprodukt

    in Microsoft Excel Hilfe
    Summe Wenn mit Filter -> Teilergebnis und Summenprodukt: Hallo zusammen, ich habe ein Problem mit einer Tabelle und habe es nun einige Stunden nicht geschafft dieses mit Google und ausprobieren zu lösen. Und zwar tragen in einer Tabelle verschiedene...
  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