Office: Summenprodukt mit div. Abfragen (evtl.benutzerdefiniert?)

Hallo erstmal,

erstmal ein großes Lob an dieses Forum. Hier wird immer geholfen wenn man bei x-stelligen Formeln plötzlich auf dem Schlauch steht.

Und hier mein Problem:

Ich habe eine Excel-Datei (Sheet2=Data) mit diversen Abfragen bereits auf einem anderen Tabellenblatt (Sheet1=Berechnung) sortieren/berechnen lassen. Bisher alles wunderbar.

Jetzt benötige ich eine Formel mit folgenden Abfragen:

wenn im Bereich M2:M65000 das Wort "xxx" beinhaltet ist ODER
wenn im Bereich M2:M65000 das Wort "yyy" beinhaltet ist UND
wenn Zeile A5 in den Eingaben im Bereich P2:P65000 übereinstimmt
DANN gib mir die Summe (V2:V65000) der betroffenen Zeilen

Hier noch zur Info:
die Zellen M, P und V sind im Sheet2, die Abfrage/Berechnung soll in Sheet1 erfolgen.

Die Formel:

=SUMMENPRODUKT(ISTZAHL(SUCHEN("*xxx*";Data!M2:M65000))*ISTZAHL(SUCHEN("*yyy*";Data!M2:M65000))*(Data!$P$2:$P$65000=Berechnung!A5);(Data!$V$2:$V$65000))

funktioniert, wenn ich sie direkt in Sheet2 eingebe, aber nicht in Sheet1..... komisch irgendwie. Ich habe gelesen, dass man da anscheinend eine benutzerdefinierte Formel verwenden muss, davon hab ich aber nu garkein Plan (VBA)

Hat jemand eine Idee (damit ich mal wieder ausschlafen kann)?

 

Hallo Drunkenmasta,

ich habe die Formel aus dem Blatt 'Berechnung' heraus ausprobiert, sie funktioniert auch von dort. Es bleibt sich egal, in welchem Blatt man sie einsetzt.
Vielleicht sind die Erwartungen, was die Formel kann, das Problem. Denn ich habe so meine Probleme, wenn ich die Anforderungen an die Formel und die tatsächliche Gestalt der Formel miteinander vergleiche.
So wie die Formel konstruiert ist, passiert Folgendes:
Angenommen ich will haben, dass der Wert der Zelle 'Data!V3' mit in der Summe, die angeliefert werden soll, enthalten sein soll; das trifft nur dann zu, wenn
1) "xxx" in Zelle 'Data!M3' enthalten ist und
2) "yyy" in Zelle 'Data!M3' enthalten ist und
3) der Wert der Zelle 'Berechnung!A5' gleich dem Wert der Zelle 'Data!P3' ist.
Damit andere Zellen der Spalte 'Data!V' mit in die Summe kommen, treffen genau die gleichen vorgenannten Bedingungen zu, nur eben auf die jeweilige Zeile angewandt.

Wenn Du aber haben willst, dass "xxx" oder "yyy" oder beide in Spalte 'Data!M' enthalten sein sollen, muss die Formel modifiziert werden:

=SUMMENPRODUKT(ODER(ISTZAHL(SUCHEN("*xxx*";Data!$M$2:$M$18));ISTZAHL(SUCHEN("*yyy*";Data!$M$2:$M$18)))*(Data!$P$2:$P$18=Berechnung!A5);(Data!$V$2:$V$18))
(An den 3 rot gekennzeichneten Stellen ist sie zu ändern!!)

Außerdem muss sie als Array-Formel eingegeben werden: d.h. wird sie in der Formeleditierzeile eingegeben, muss zur Beendigung der Formeleingabe die Tastenkombination Strg+Umschalt+Eingabe gedrückt werden, sodass die Formel hinterher in geschweiften Klammern eingeschlossen erscheint.

 

Hi,

danke für die Antwort. Leider errechnet Excel bei mir den Gesamtbetrag der Zelle P, in der lediglich die Abfrage:

Data!$P$2:$P$65000=Berechnung!A29

übereinstimmt. Als wenn Excel die anderen Abfragen übergeht bzw. bei nicht übereinstimmenden Ergebnisse trotzdem summiert. Die Formel habe ich entsprechend eingegeben (mit Array):

=SUMMENPRODUKT(ODER(ISTZAHL(SUCHEN("*xxx*";Data!$M$2:$M$65000));ISTZAHL(SUCHEN("*yyy*";Data!$M$2:$M$65000)))*(Data!$P$2:$P$65000=Berechnung!A5);(Data!$V$2:$V$65000))

Grüße

 

Hallo Drunkenmasta,

Du hattest recht, meine Formel funktioniert nicht richtig. Meine neue Formel schaut so aus:

=SUMMENPRODUKT((ISTZAHL(SUCHEN("*xxx*";Data!$M$2:$M$18))+ISTZAHL(SUCHEN("*yyy*";Data!$M$2:$M$18))>0)*(Data!$P$2:$P$18=Berechnung!A5);(Data!$V$2:$V$18))

An den 3 rot gekennzeichneten Stellen habe ich die Änderungen angebracht.
Diese Formel ist eine normale Formel (keine Array-Formel)!

Ich hoffe, dass es diesmal klappen müsste.

 

Perfekt, Danke. Heute habe ich noch von der Liste geträumt und dann auf der Arbeit endlich die Formel eingesetzt.

Vielen Dank nochmal für die super Hilfe