+- Office-Fragen.de (https://office-fragen.de)
+-- Forum: Microsoft Office (https://office-fragen.de/forum-1.html)
+--- Forum: Excel (https://office-fragen.de/forum-2.html)
+--- Thema: Summenprodukt, mehrere Arbeitsblätter (/thread-28806.html)
Summenprodukt, mehrere Arbeitsblätter - Alexander83 - 06.06.2023
Hallo zusammen,
ich habe mich in den letzten Tagen intensiv mit der Summenprodukt Formel auseinandergesetzt um aus einer Importierten und filterbaren Liste eine Summe zu bilden, für die ein oder mehrere Kriterien gelten.
Die Problematik der nicht verwendbaren Wildcards in Summenprodukt habe ich mit Hilfe von anderen Beiträgen gelöst.
Leider funktioniert die Funktion nur dann, wenn Sie im gleichen Arbeitsblatt ausgeführt wird obwohl meines Erachtens die Verweise auf das andere Arbeitsblatt soweit passen.
Zur Aufgabenstellung sei gesagt:
Eine Liste mit Datum, Betreff und Betrag als Spalten(typisch Kt. Auszug) mit PowerQuery aus CSV Ordner importiert.
Nun gibt es Monat für Monat zahlreiche Vorgänge mit unterschiedlichen Kategorien. Erhalte ich zB. eine Zahlung, so lautet der Betreff:
"payment for order xxxxxxxx". Ich muss also nach dem Textteil "payment for order" suchen, da die jeweilige Bestellnummer mit drin steht und somit der Betreff nie gleich ist.
Wenn ich nun die Liste nach Monat filtere, so soll natürlich auch nur die Summe der gesuchten Zeilen im gefilterten Bereich addiert werden.
Des Weiteren muss ich auch Textteile ausschließen können, denn "payment for order..." gibt es auch als Variante "credit for payment for order..".
Wenn ich diese Positionen auftrennen will, dann muss ich also nach "payment for order" suchen aber "credit" ausschließen.
Bis dato habe ich das mit "*payment for order*" und <>"*credit*" realisiert. Mit der komplexen Formel über Summenprodukt funktionierte das aber nicht mehr. Deswegen habe ich zusätzlich ISTZAHL und SUCHEN mit verwendet. Soweit so gut.
Eigentlich bin ich fast am Ziel. Das Problem ist aber dass die Funktion auf ein anderes Tabellenblatt soll als die Tabelle mit Daten.
Folgende Formel verwende ich:
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("D"&ZEILE('AnderesArbeitsblatt!R2:R999)))*(ISTZAHL(SUCHEN("Payment";'AnderesArbeitsblatt'!B2:B999))))
Statt SUCHEN habe ich es auch mit FINDEN probiert, dann muss ich den Suchbegriff aber genau definieren, was mir natürlich nichts bringt. Ich will ja sozusagen unscharf suchen.
Wenn ich auf einem anderen Blatt diese Formel verwende, dann bleibt das Ergebnis 0.
Leider kann ich nicht so richtig debuggen weil Teile der Formel einzeln kein Ergebnis oder nur Fehler liefern.
Gerne liefere ich eine Beispiel Datei. Möglicherweise habe ich auch den richtigen Threat mit der Antwort noch nicht gefunden.
Ich freue mich auf eure Hinweise.
VG Alex
RE: Summenprodukt, mehrere Arbeitsblätter - steve1da - 06.06.2023
Hola,
die 0 ist nachvollziehbar weil du halt nicht alles auf das andere Blatt bezogen hast.
"D" zeigt bei dir aktuell nicht auf AnderesArbeitsblatt.
Gruß,
steve1da
RE: Summenprodukt, mehrere Arbeitsblätter - Alexander83 - 06.06.2023
Tatsächlich. Das ist ein wenig verwirrend, weil ich davon ausging, dass INDIREKT eigentlich entweder eine Art Pointer erwartet bzw. einen String, den sie als Adresse interpretiert. Daher das "D" &...
Tatsächlich will INDIREKT aber auch das Tabellenblatt.
Der Vollständigkeit halber, da ich ein bisschen probieren musste bis es ging, lautet die richtige Syntax dann:
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("'AnderesArbeitsblatt'!D"&ZEILE('AnderesArbeitsblatt!R2:R999)))*(ISTZAHL(SUCHEN("Payment";'AnderesArbeitsblatt'!B2:B999))))
Weil es schlecht zu erkennen ist, nochmal nur der Teil nach Indirekt mit den diversen Anführungsstrichen:
INDIREKT ( " ' AnderesArbeitsblatt ' ! D " &Zeile(....))
Besten Dank für den Hinweis!!
RE: Summenprodukt, mehrere Arbeitsblätter - Alexander83 - 06.06.2023
Zufällig noch ne schnelle Idee, wie ich ein 2. Suchkriterium mit einreihen kann? Ich möchte nach "payment..." suchen UND NICHT " credit for payment".
Also alle Zeilen mit payment außer die mit credit...
Ich habe bei SUCHEN mit einem UND probiert aber bisher noch ohne Erfolg.
Damit ich mal wieder eine Nacht schlafe frage ich mal direkt
Besten Dank
RE: Summenprodukt, mehrere Arbeitsblätter - steve1da - 06.06.2023
So?
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("'AnderesArbeitsblatt'!D"&ZEILE(AnderesArbeitsblatt!R2:R999)))*(LINKS(AnderesArbeitsblatt!B2:B999;7)="payment"))
RE: Summenprodukt, mehrere Arbeitsblätter - Alexander83 - 06.06.2023
Ja, wenn ich immer weiß, dass gewisse Texte an gewissen Positionen stehen dann würde das gehen. Lieber wäre mir die Flexibilität beide Suchbegriffe unabhängig der Position zu finden.
Alternativ kann ich beim Import ja Texte entsprechend zurecht schieben. Wäre aber nur die letzte Variante.
RE: Summenprodukt, mehrere Arbeitsblätter - steve1da - 06.06.2023
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("'AnderesArbeitsblatt'!D"&ZEILE(AnderesArbeitsblatt!R2:R999)))*(ISTZAHL(SUCHEN("payment";AnderesArbeitsblatt!B2:B999)))*(NICHT(ISTZAHL(SUCHEN("credit";AnderesArbeitsblatt!B2:B999)))))
RE: Summenprodukt, mehrere Arbeitsblätter - Alexander83 - 06.06.2023
TOP! That´s it!
Muchas gracias!
Nun habt ihr mich an der Backe. Ich komme wieder