Summewenns mit mehreren Kriterien

[Ariane_4]

Hallo zusammen,

ich arbeite sehr gerne mit Excel, stoße leider bisher schnell an Grenzen, die ich bisher mir Suchen nach Schlagwörtern in diesem Forum lösen konnten. 
Zu meinem aktuellen Problem finde auch was, doch irgendwie fehlt es mir dennoch an der Umsetzung. Ich weiß nicht was ich wie falsch/fehlerhaft erfasse. Daher bitte ich um eure Unterstützung. 

Es geht um die Formel mit der Funktion "Summewenns". Ich weiß, dass ich mehrere Kriterien erfassen kann, doch bereits bei 4 Kriterien, funktioniert die Formel bei mir nicht mehr. 
Ich habe mal eine Hilfstabelle erstellt.
Spalte A - Fahrzeug (Roller oder Auto)
Spalte B - mehrere Motive
Spalte C - Bereich (Süd, Ost, Nord, West und Mitte)

Formel mit 3 Kriterien:
=WENN(ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$C$2:$C$2728;"Süd")=0;"./.";ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$C$2:$C$2728;"Süd")) --> 153 Treffer

oder

=WENN(ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$C$2:$C$2728;"Mitte")=0;"./.";ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$C$2:$C$2728;"Mitte")) --> 0 Treffer daher "./."

Formel mit 4 Kriterien:
=WENN(ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$B$2:$B$2728;"Wolken";Gesamtliste!$C$2:$C$2728;"Süd")=0;"./.";ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$B$2:$B$2728;"Wolken";Gesamtliste!$C$2:$C$2728;"Süd")) --> 0 Treffer müssten jedoch 294 sein.

Wenn ich nach 2 oder mehr Kriterien in einer Spalte suchen, muss ich dann ein "UND" in die Formel einbauen? Habe dies bereits mit verschiedenen Konstellationen probiert, komme jedoch immer wieder auf den Wert 0 oder direkt einen Fehler.

Ganz herzliche Dank für eure Unterstützung.

Sonnige Grüße

Ariane


  Testtabelle.xlsx (Größe: 55,2 KB / Downloads: 13)

[{Boris}]

Hi,

alle ZÄHLENWENNS-Bedingungen funktionieren nach dem UND Prinzip.

=WENN(ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$B$2:$B$2728;"Wolken";Gesamtliste!$C$2:$C$2728;"Süd")=0;"./.";ZÄHLENWENNS(Gesamtliste!$A$2:$A$2728;"Auto";Gesamtliste!$B$2:$B$2728;"Regenbögen";Gesamtliste!$B$2:$B$2728;"Wolken";Gesamtliste!$C$2:$C$2728;"Süd")) --> 0 Treffer müssten jedoch 294 sein.

kann daher nicht funktionieren, weil in Spalte B gleichzeitig "Regenbögen" und "Wolken" stehen muss.

Einfache Lösung: Addiere beide ZÄHLENWENNS:

=ZÄHLENWENNS(....Regenbögen)+ZÄHLENWENNS(....Wolken)

[Cadmus]

Wie wäre es einfach mit einer Pivotauswertung mit Auswahl per Datenschnitt? Einfach mal probieren.

[Ego]

Hallo Ariane,

wenn dir die Pivotauswertung nicht zusagt habe ich noch zwei Bemerkungen:

1. Wie Boris schon geschrieben hat, werden in den ....WENNS-Funktionen die Bedingungen mit einem logischen UND verknüpft. 
Wenn du ein logisches ODER haben möchtest kannst du entweder Boris Vorschlag nehmen oder die  Spaltenvergleiche multiplizieren und addieren.
Hierbei ist die Multiplikation eine logisches UND und die Addition mit dem Vergleich >0 ein logisches ODER.
In deinem Fall:
=SUMME((Gesamtliste!$A$2:$A$2728="Auto")*((Gesamtliste!$B$2:$B$2728="Regenbögen")+(Gesamtliste!$B$2:$B$2728="Wolken")>0)*(Gesamtliste!$C$2:$C$2728="Süd"))
oder hier auch (da nicht beide Motive gleichzeitig eingetragen sein können)
=SUMME((Gesamtliste!$A$2:$A$2728="Auto")*((Gesamtliste!$B$2:$B$2728="Regenbögen")+(Gesamtliste!$B$2:$B$2728="Wolken"))*(Gesamtliste!$C$2:$C$2728="Süd"))

2. Schau dir einmal die Funktion LET an.
Mit ihr must du die gleichen Ausdrücke in einer Formeln nicht mehrmals berechnen.
Excel hat weniger zu rechnen und du hast weniger zu schreiben und weniger Möglichkeiten eine Fehleingabe zu machen.
=LET(x;SUMME((Gesamtliste!$A$2:$A$2728="Auto")*((Gesamtliste!$B$2:$B$2728="Regenbögen")+(Gesamtliste!$B$2:$B$2728="Wolken"))*(Gesamtliste!$C$2:$C$2728="Süd"));WENN(x=0;"./.";x))


ps. Die Multiplikation und Addition von Spaltenvergleiche ist möglich, da hierbei ein WAHR wie eine 1 und ein FALSCH wie eine 0 berücksichtigt werden.

[EA1950]

Hallo,

zusätzlich zur Verwendung der LET-Funktion würde ich empfehlen, die Daten im Arbeitsblatt "Gesamtliste" in eine strukturierte (=intelligente) Tabelle umzuwandeln. Dazu musst du nur
1) die aktive Zelle irgendwo in diesen Datenbereich platzieren
2) die Tastenkombination Strg+T drücken, sodass ein kleines Dialogfenster erscheint. Dort muss die Checkbox "Tabelle hat Überschriften" angehakt sein und der Adressbereich muss diese Überschriftzeile mit enthalten. Button "OK" klicken.
3) Über das Menüband > Tabulator "Tabellenentwurf" > Gruppe "Eigenschaften" > Textbox "Tabellenname" einen aussagekräftigen Namen für die Tabelle statt des Standardnamens eingeben, zB. "tbDaten"

Dann vereinfacht sich die LET-Formel von Ego
=LET(x;SUMME((Gesamtliste!$A$2:$A$2728="Auto")*((Gesamtliste!$B$2:$B$2728="Regenbögen")+(Gesamtliste!$B$2:$B$2728="Wolken"))*(Gesamtliste!$C$2:$C$2728="Süd"));WENN(x=0;"./.";x))
zu
=LET(x;SUMME((tbDaten[Fahrzeug]="Auto")*((tbDaten[Motive]="Regenbögen")+(tbDaten[Motive]="Wolken"))*(tbDaten[Bereich]="Süd")); WENN(x=0;"./.";x))

Diese Formel ist einfacher zu lesen/verstehen. Außerdem werden insbesondere am Ende der Daten keine Zellen mit Daten übersehen. Darüber hinaus ist sie dynamisch, dh. man kann ab der ersten freien Zeile nach der Tabelle weitere Datenzeilen hinzufügen, die automatisch zu einem Bestandteil der strukt.Tabelle werden, sodass sich vorgenannte Formel automatisch aktualisiert (im Gegensatz zur Version mit fixen Zellbereichsangaben).

Man muss dabei nur auf eines achten: Kopiert man eine solche Formel, muss man das mit dem Befehl fürs Formelkopieren tun - niemals jedoch mit dem Formelkopierpunkt (an der rechten unteren Ecke einer Zelle). Mit letzterem verschieben sich nämlich die Spaltenangaben (Bezeichner in den eckigen Klammern), wie wenn es Spaltenangaben ohne $-Zeichen wären.

Hast du aber ein ganz aktuelles Excel365-Insider, dann kannst du obige Formel vereinfachen zu:
=LET(x;SUMME((tbDaten[Fahrzeug]="Auto")*REGEXTEST(tbDaten[Motive];"Regenbögen|Wolken")*(tbDaten[Bereich]="Süd")); WENN(x=0;"./.";x))

[Roger]

Hallo Ariane,

ich hab das mal mit Dropdown’s gelöst, da kannst du dir das passend zusammenstellen.

Gruß Roger

[Ariane_4]

Hallo zusammen,

wow!! Mega lieben Dank! Habe es erst in der Probetabelle und anschließend in die "echten" überführt. Der Freudenschrei ging über den ganze Flur. Die Formel sieht unheimlich aus, da es am Ende 22 Kriterien sind. Doch egallllll! Das Ergebnis zählt. Ich bin soooo happy!!!!

@{Boris} - Ganz lieben Dank für die Erklärung, warum meine Formel nicht gegangen ist. Das hat es mir noch mal deutlicher vor Augen geführt, warum die Formel fehlerhaft war.

@Cadmus -  Danke für den Denkanstoß mit der Pivot. Ich werde dies prüfen, in wie weit dies ggf. möglich sein könnte. Ich habe halt eine Grundtabelle, über die ich 1x im Monate Daten ziehe. Und dann muss ich am Ende 60 unterschiedliche Daten "rauslesen". Bisher löse ich dies über ein Zusatzblatt mit unter anderem diese tollen neuen Formel. 

@Ego - Also von dieser Formelfunktion "LET" habe ich noch nie gehört. Und ich finde es einfach mega, dass die Formel am Ende schlanker ausschaut als vorher. Freue mich wirklich sehr. Danke sehr. 

@EA1950 -  Lieben Dank mit dem Hinweis zur Formatierung der Formel als Tabelle. Beim aktuellen Versuch erhielt ich den Hinweis, die Daten konnten nicht berechnet werden (nach 1x oder 2x F9 klicken tat es doch). Es ist wohl durchaus zielführend, die Formel dahingehen anzupassen. Doch da ich nicht alleine mit den Daten arbeite, muss ich dies erst abklären.

@Roger - Danke dir für die Mühe und Anpassung meiner Testtabelle. Die Idee finde ich auch toll.

Nochmals ganz, ganz lieben Dank für Eure Unterstützung. Ich bin wirklich richtig glücklich, dass ich dies zunächst lösen konnte und neue Denkanstöße habe, es doch mal mal hier und da anders aufzubauen. 

Herzliche Grüße, Ariane

[EA1950]

Hallo,

@EA1950 -  Lieben Dank mit dem Hinweis zur Formatierung der Formel als Tabelle. Beim aktuellen Versuch erhielt ich den Hinweis, die Daten konnten nicht berechnet werden (nach 1x oder 2x F9 klicken tat es doch). Es ist wohl durchaus zielführend, die Formel dahingehen anzupassen. Doch da ich nicht alleine mit den Daten arbeite, muss ich dies erst abklären.

hattest du vielleicht das Arbeitsblatt auf manuelle Berechnung eingestellt? Ansonsten wäre mir so ein Verhalten völlig unerklärlich. Excel verhält sich nicht anders, als wären es Formeln mit Zell/Bereichsangaben. Da muss man nirgends eine F9-Taste drücken.