Office 2013: Formel gesucht ähnlich SUMMEWENNS (evtl. auch ganz andere Formel)

[tobias.97]

Hallo Ihr Lieben

Ich habe mir jetzt schon einen Wolf gesucht und bin einfach nicht fündig geworden und würde mich sehr freuen, wenn einer von Euch schlauen Köpfen mir weiterhelfen könnte

Mein Problem sieht wie folgt aus:
In Zelle C5 möchte ich mir die (für den Bonus B1) “bonuswirksamen Umsätze“ anzeigen lassen, die der Kunde mit der Nr. 1002 in Quartal 1 erzielt hat.

Kunde 1002 ist dem Verband A zugehörig, also bekommt er seinen Bonus anhand der Konditionen berechnet, die Verband A ausgehandelt hat. Die Konditionen habe ich im gleichnamigen Reiter „Konditionen“ stehen. Dort steht einerseits für jede Bonusart (also B1, B2, B3) wie viel % Bonus der Verband bzw. die dem Verband zugehörigen Kunden bekommen und andererseits steht dort ebenfalls für jede Bonusart, auf welche Produktgruppen sich der jeweilige Bonus bezieht. (Es gibt noch zusätzlich zu Verband, Bonusnummer/-art und Produktgruppe ein paar weitere Attribute für die Boni, hier jedoch vereinfacht dargestellt.)

In Zelle C5 soll also konkret der Umsatz in den Produktgruppen „Gabel“ und „Messer“ (Konditionen!D2:I2) stehen, die der Kunde 1002 (A5) im Quartal 1 (A2) erzielt hat. Anhand dieser Kriterien soll die Formel also aus dem Reiter „Umsatz“ die €-Beträge aufsummieren, die auf diese Kriterien zutreffen. In diesem Beispiel: 1.000€ und 2.000€.

Ich hatte es auf verschiedene Arten und Weisen mit SUMMENPRDUKT oder SUMMEWENNS versucht, aber bin leider immer wieder gescheitert.

Diese Formel habe ich aktuell in C5 stehen:
=SUMMENPRODUKT(SUMMEWENNS(Umsatz!$D$2:$D$37;Umsatz!$A$2:$A$37;A5;Umsatz!$B$2:$B$37;$A$2;Umsatz!$C$2:$C$37;Konditionen!D2:I2))
Die ist natürlich insofern Mist, dass sich das letzte Suchkriterium (also die zu suchenden Produktgruppen - Konditionen!D2:I2) nicht automatisch anpasst und ich dieses in jeder Zelle ändern müsste…

Meine Idee: Gibt es vllt. eine Funktion, die mir das letzte Suchkriterium (Konditionen!D2:I2) aus dem Reiter „Konditionen“ heraussucht? Also ähnlich wie bei der INDEX-Funktion (in Kombination mit VERGLEICH), bloß dass mir nicht eine einzelne Zelle ausgegeben wird, sondern die 4 aneinanderhängenden Zellen Konditionen!D2:I2?

Oder habt ihr bessere Lösungswege?

Ganz liebe Grüße und vielen herzlichen Dank schon mal vorab!

(der verzweifelnde) Tobias


PS: die Formeln in den anderen Umsatz-Feldern kann man natürlich getrost ignorieren, da diese nur nach dem Umsatz im jeweiligen Quartal für den jeweiligen Kunden suchen, aber das Kriterium der Produktgruppe völlig außer acht lassen...

[gmg-cc]

Moin Tobias,
ich weiß, ein komplexes Thema ...
Ich habe mir das 3 Mal durchgelesen, kann aber das Ganze nicht nachvollziehen. Sicher scheint mir, dass da irgendwo irgendwie etwas fehlt oder falsch eingeordnet ist.

Eine kurze Kontrolle: Die Umsätze des 1. Quartals ergeben 111.800,00€, in der Umsatz-Tabelle (mit Pivot berechnet) jedoch 121.200€. Warum dieses? Und wo fließt wie die Bonusgruppe in die Berechnungen ein? Auf die Produktgruppe kann sich das mE nicht beziehen ... Zugegeben, ich mag derart lange Formeln nicht analysieren, das ist nicht weine Welt.

[tobias.97]

Hallo Günther,

vielen Dank, dass du dir das mal angeschaut hast.

Ich habe natürlich noch fleißig weiter gesucht und bin just über die BEREICH.VERSCHIEBEN-Formel auf meine Lösung gekommen.
War vorher iwie total auf dem Schlauch gestanden...
BEREICH.VERSCHIEBEN(Konditionen!$C$1;VERGLEICH(Bonus!B5&Bonus!$C$4;Konditionen!$A$2:$A$9&Konditionen!$B$2:$B$9;0);1;1;4)

Und nochmal in der Gesamtformel für Feld C5:
=SUMMENPRODUKT(SUMMEWENNS(Umsatz!$D$2:$D$37;Umsatz!$A$2:$A$37;A5;Umsatz!$B$2:$B$37;$A$2;Umsatz!$C$2:$C$37;BEREICH.VERSCHIEBEN(Konditionen!$C$1;VERGLEICH(Bonus!B5&Bonus!$C$4;Konditionen!$A$2:$A$9&Konditionen!$B$2:$B$9;0);1;1;4)))

Ich vermute, dass die Summen nicht übereinstimmen, da ich in Zelle C5 nur die Umsätze für die die Produktgruppen Gabel und Messer für Q1 summiert habe. (Die Formel weicht von den anderen Umsatz-Formeln ab)

Dennoch vielen Dank!

Gruß

Tobias