Summenprodukt funktioniert nach Update nicht mehr wie zuvor

[rincewind]

Hallo zusammen,

ich habe das Summenprodukt bisher immer verwendet um Wertetabellen nach verschiedenen Bedingungen zu durchsuchen. Leider scheint seit dem letzten Update die Formel nicht mehr in altbekannter Weise zu funktionieren. Mir ist bereits aufgefallen, dass ich neuerdings die nichtnumerischen Arrays mit eins multiplizieren muss, um einen anderen Wert als Null zu erhalten (warum auch immer).

Folgendes soll in dem Beispielblatt geschehen:
  Test.xlsx (Größe: 10,3 KB / Downloads: 8)
In dem gelb markierten Bereich sollen alle Zeilen aus der Wertetabelle Q2:S28 aufsummiert werden, für die folgende Bedingungen erfüllt sind: Der Mandant in Spalte R entspricht dem Mandanten in der jeweiligen Zeile von Spalte A, der Status in Spalte Q stimmt mit der Überschrift der Spalten B oder C überein und der Carrier aus Spalte S entspricht einem der drei möglichen Carrier aus Spalte D, E oder F. Ich weiß nicht, wie ich es leichter erklären soll. In Spalte B habe ich erst mal nur die ersten beiden Arrays verwendet, was auch wunderbar funktioniert. In Spalte C habe ich dann das dritte Array hinzugenommen, bekomme aber, wie zu sehen ist, nur #WERT!
ausgegeben. Ich habe es mehrfach überprüft und genau diese Formel hat exakt in dieser Form in mehreren anderen Tabellen Anwendung gefunden und funktioniert. Ich verstehe einfach nicht, warum das jetzt nicht mehr so ist. Zumal das dritte Array alleine stehend auch ohne Probleme funktioniert, wie ich in Spalte H getestet habe.

Zur Verdeutlichung:
In Zelle C2 sollte als Wert 1 stehen, da die abgefragten Bedingungen nur in der blau markierten Zeile der Wertetabelle alle vereint sind (Status2 / Mandant a / Carrier x1, x2 oder x3). In Zelle C3 sollte wiederum der Wert 3 auftauchen, da die Bedingungen in allen rot markierten Zeilen der Wertetabelle erfüllt sind.

Ich hoffe, dass ich mein Problem halbwegs verständlich erklären konnte und dass mir jemand helfen kann. Vielleicht gibt es für das Problem ja auch einen anderen, einfacheren Ansatz? Wichtig ist nur, dass die Anzahl der Carrier flexibel und ggf. noch auf Spalte G, H, I, etc. erweiterbar bleiben muss. Ich nutze Office 2013.

Danke für jeden kreativen Einfall zu diesem Thema

Gruß
Volker

[steve1da]

Hola,

Code:

=SUMMENPRODUKT(($Q$2:$Q$28=C$1)*($R$2:$R$28=$A2)*($S$2:$S$28=$D2:$F2))

Gruß,
steve1da

[Klaus-Dieter]

Hallo,

gelöscht

[Jockel]

Hallo, oder ..:

PHP-Code:

=SUMMENPRODUKT((ZÄHLENWENNS($Q$2:$Q$28;C$1;$R$2:$R$28;A2;$S$2:$S$28;D2:F2))) 


[rincewind]

ok, das Problem ist offenbar gelöst, aber ich verstehe es nicht. Mit den Multiplikationszeichen habe ich angefangen. Die haben nicht funktioniert. Dann habe ich es mit Semikolon versucht, was teilweise funktioniert hat und teilweise nicht. Und jetzt letztendlich funktioniert es doch wieder mit Multiplikationszeichen und ohne *1 dahinter? Kann mir jemand erklären, warum ich in diesem Fall "*" und nicht ";" nutze?
Und @Jockel: Was macht deine Formel? Das sie auch funktioniert habe ich gemerkt, aber was ist da der Hintergrund? Bzw. ich verstehe nicht, warum hier das Summenprodukt die Zählenwenns Formel ergänzen muss.

[schauan]

Hallöchen,


bei der Formel mit den "*" passiert folgendes:

Es wird innerhalb der 3 Klammerausdrücke geprüft, ob ein bestimmter Zellinhalt vorliegt.
Wenn ja, dann ist die Prüfung Wahr. Dafür gibt es intern die 1
Wenn falsch, dann gibt es die 0

Bei SUMMENPRODUKT werden diese Prüfungen mit UND verknüpft, sie müssen also alle 3 WAHR bzw. 1 sein. Daraus erschließt sich dann die Multiplikation. Damit das Ergebnis auch wahr ist, müssen alle 3 Bedingungen wahr sein. 1*1*1 = 1 --- 1*0*1 = 0

Die andere Formel kann Dir sicher Jörg besser erklären.

[rincewind]

Danke für den Erklärungsaufwand. Was "*" macht habe ich auch verstanden. Auch auf die Gefahr hin zu nerven, muss ich allerdings doch noch mal nachfragen, wo jetzt der Unterschied zu ";" ist. Ich will doch auch bei der Formel mir "*", dass die Arrays alle Wahr sind... Nur dann soll die Zeile gezählt werden, wenn alle drei Bedingungen zutreffen. das wäre doch im Prinzip ein UND. Warum funktioniert dann ";" nicht? Kannst du dazu Beispiele geben?

[snickers]

http://www.clever-excel-forum.de/Thread-...#pid100310

... und dort etwas höher für den Kontext.

[rincewind]

http://www.clever-excel-forum.de/Thread-...#pid100310

... und dort etwas höher für den Kontext.

Yieha, ich habs verstanden. Die Aussage ";" lässt nichtnumerische Werte außenvor, hat die Lücke geschlossen ;)
Aber brauchst du in deinem Beispiel eigentlich die Leerspalte A wirklich? Wenn ich es korrekt verstehe, musst du ja das erste Array nicht unbedingt von B1 bis K3 wählen sondern es reicht ja auch bis J3 und dann entsprechend das zweite Array von C1 bis K3. Im zweiten Summenprodukt vertauscht du dann die Bezüge einfach und es sollte das selbe raus kommen nur ohne Leerspalte vorweg, korrekt?

[snickers]

Ausprobieren statt Mutmaßen. Es fliegt Dir um die Ohren.