Excel Formeln – ZÄHLENWENN mit mehreren Bedingungen

In diesem Artikel werde ich ihnen zeigen, wie man die Anzahl an Zeilen bestimmen kann, in denen bestimmte Werte in einer vorgegebenen Reihenfolge auftreten müssen. Dabei können Sie beliebig viele Bedingungen stellen.

Wahrscheinlich denken Sie wenn Sie diese Aufgabe hören zuerst an die ZÄHLENWENN Funktion. Eigentlich ist diese Funktion auch nicht die schlechteste Idee. Jedoch funktioniert sie nur bei einer Spalte. Hier möchte ich ihnen aber zeigen, wie Sie die Anzahl an Zeilen bekommen, in denen in mehreren Spalten Bedingungen erfüllt sind. Bei so einer Aufgabe funktioniert ZÄHLENWENN leider nicht. Jedoch gibt es dafür natürlich Abhilfe. Wie man das Problem trotzdem ganz einfach mit einer einzigen Formel lösen kann, zeige ich ihnen nun:

Beginn:

Zu aller erst müssen Sie in ihrer Excel Tabelle natürlich erst einmal die Daten eintragen. Dies machen Sie am besten zeilenweise. Tragen Sie also pro Zeile nun einen Datensatz ein. Dieser besteht aus zwei Werten, die einzeln in der Spalte A und der Spalte B eingetragen werden. Ich habe in meinem Beispiel nur Datensätze gewählt, die aus einer Kombination aus Buchstaben bestehen, bei ihnen werden es später in der Anwendung höchstwahrscheinlich realistischere Werte sein. So könnte links zum Beispiel der Name eines Produktes stehen und rechts sein zugehöriger Preis. Bei meinem  Beispiel habe ich außerdem darauf geachtet, dass eine Kombination mehrmals vorkommt. Als Beispiel dafür habe ich einfach „x“ in der Spalte A und „y“ in der Spalte B gewählt. Die Unterscheidung der beiden Spalten ist wirklich wichtig für die Formel. Haben Sie alle Werte eingetragen, so vergleichen Sie ihre Tabelle nun mit der Zeichnung. Sieht sie ungefähr so aus, dann haben Sie alles richtig gemacht.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Anzahl einer bestimmten Kombination auslesen:

Nachdem Sie nun alle Werte eingetragen haben, können Sie damit beginnen, die Anzahl auszugeben, wie oft eine bestimmte Kombination in der Tabelle auftritt. Eine Kombination kennzeichnet sich immer aus zwei Zellen, bei denen auch unterschieden wird,  ob zuerst der Wert aus Spalte A oder der aus Spalte B kommt. Um nun die Anzahl auszugeben, geben Sie in die Zelle C1 nun die folgende Formel ein:

=SUMME(WENN((A1:A100=“x“)*(B1:B100=“y“);1))

Diese Formel gibt nach der Eingabe direkt noch kein korrektes Ergebnis aus. Das liegt daran, dass sie eine Matrixformel ist. Haben Sie schon die anderen Artikel zu den Excel Formeln gelesen, so wissen Sie nun, was Sie tun müssen. Hier aber nochmals. Zuerst klicken Sie die Zelle an. Danach klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie nun abschließend die Tasten STRG+SHIFT+Enter gleichzeitig auf ihrer Tastatur. Nun erscheint in der Zelle die korrekte Ausgabe. In meinem Fall war es die Zahl „3“, da die gesuchte Kombination aus einem „x“ in Spalte A und einem „y“ in Spalte B mit den Zeilen 4, 6 und 8 drei Mal auftritt. Neben der korrekten Ausgabe hat sich aber auch etwas anderes verändert, die Formel sieht nun so aus:

{=SUMME(WENN((A1:A100=“x“)*(B1:B100=“y“);1))}

Haben Sie einen anderen Zellbereich als ich zu Beginn gewählt, so ändern Sie diesen in der Formel entsprechend ab. Desweiteren können Sie mit der Formel natürlich auch verändern, nach was gesucht werden soll. Ändern Sie dazu den Buchstaben „x“ in der Formel, in den ab, der in Spalte A zu finden sein soll. Das gleiche machen Sie mit „y“ nur eben für Spalte B.

Anzahl einer bestimmten Kombination ohne WENN auslesen:

Möchten Sie aus irgendwelchen Gründen auf die WENN Funktion in der Formel verzichten, so kann ich ihnen sagen, dass das geht. Normalerweise denkt man ja, dass man für einen Vergleich immer ein WENN benötigt, doch in diesem Fall ist dies nicht nötigt. Wie Sie sehen, gibt es dafür sogar zwei verschiedene Formeln.

Matrixformel:

Möchten Sie auf WENN verzichten, haben Sie wie gesagt zwei Möglichkeiten, die beider auf einer Matrixformel basieren. Wie diese beiden aussehen werde ich ihnen nun zeigen. Zuerst werde ich die erste Funktion der beiden behandeln. Um es ohne WENN zu schreiben, geben Sie nun in die Zelle C1 folgendes ein, nachdem Sie den aktuellen Inhalt gelöscht haben:

=SUMME((A1:A100=“x“)*(B1:B100=“y“))

Wie auch die andere Formel vorhin ist dies eine Matrixformel. Das bedeutet für Sie, dass Sie wieder die drei Schritte beim Umgang einer Matrixformel befolgen müssen. Klicken Sie also zuerst nochmals auf die Zelle. Haben Sie das gemacht, klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das erledigt, drücken Sie nun STRG+SHIFT+Enter. Jetzt sehen Sie, dass in der Zelle der richtige Wert erschienen ist. Bei mir ist das korrekterweise immer noch die Zahl „3“ für die Einträgt in den Zeilen 4, 6 und 8. Außer der Tatsache, dass nun das richtige Ergebnis in der Zelle steht, hat sich die Formel verändert. Sie sieht nun so aus:

{=SUMME((A1:A100=“x“)*(B1:B100=“y“))}

Wenn Sie einen anderen Zellbezug für die beiden Spalten also ich gewählt haben, so ändern Sie die Formel einfach entsprechend ab. Auch hier können Sie die Suchbegriffe wieder verändern, indem Sie das „x“ und/oder das „y“ in der Formel ihren Wünschen nach abändern.

Matrixformel anders:

Wie ich oben schon geschrieben habe, basiert auch der zweite Weg WENN zu vermeiden auf einer Matrixformel. Im Gegensatz zu den anderen beiden Formeln, in denen jeweils ein Produkt aufgetreten ist ( * ), ist dies in diesem Fall nicht gegeben. Die Formel wird, wie Sie gleich sehen, aber trotzdem funktionieren. Löschen Sie nun zuerst den aktuellen Inhalt von C1 und geben Sie dann folgendes ein:

=SUMME(N(A1:A100&B1:B100=“x“&“y“))

Auch das ist ja eine Matrixformel. Befolgen Sie deshalb wieder die drei Schritte, die standardmäßig bei einer Matrixformel durchgeführt werden. Klicken Sie zuerst also wieder in die Zelle. Nun klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie nun abschließend noch STRG+SHIFT+Enter. Jetzt wird wieder der richtige Wert angezeigt. Bei mir also wieder „3“. Außerdem hat sich natürlich die Formel wieder verändert. Das matrixtypische Aussehen von dieser Formel lautet nun:

{=SUMME(N(A1:A100&B1:B100=“x“&“y“))}

Natürlich werden die Zellebezüge wie vorhin abgeändert. Auch „x“ und „y“ ändern Sie entsprechend ab. Möchten Sie, dass drei Bedingungen pro Zeile überprüft werden, so fügen Sie in der Formel direkt hinter „A1:A100&B1:B100“ nun das kaufmännische Undzeichen und den neuen Zellebezug ein. Also zum Beispiel so etwas: „A1:A100&B1:B100&C1:C100“. Natürlich müssen Sie danach für diese Spalte noch eine Bedingung formulieren. Ändern Sie dazu „“x“&“y““ so ab, dass eine neue Bedingung noch eingebaut wird. Zum Beispiel so: „„“x“&“y“&“z““.

Ohne Matrixformel:

Möchten Sie auf das WENN in der Formel verwenden und es soll außerdem keine Matrixformel sein, so haben Sie dann immerhin noch eine Möglichkeit. Um die Formel dafür einzugeben, müssen Sie die Zelle C1 nun wieder leeren. Anschließend geben Sie einfach die folgende Formel in die Zelle ein:

=SUMMENPRODUKT((A1:A100=“x“)*(B1:B100=“y“))

Hier wird ihnen, da keine Matrixformel, auch umgehend das Ergebnis angezeigt. In meinem konkreten Beispiel also die Zahl „3“, da „x“ in Spalte A und „y“ in Spalte B in den Zeilen 4, 6 und 8 vorkommt. Also in drei verschiedenen Zeilen. Wie oben auch, ändern Sie die Zellbezüge wieder ihren Wünschen nach ab. Desweiteren verändern Sie die Suchbegriffe wieder durch Verändern von „x“ und „y“.

Excel 2007 oder neuer:

Haben Sie auf ihrem PC Office 2007 oder neuer installiert, so haben Sie noch zwei weitere Formeln zur Auswahl. Dabei ist eine eine Matrixformel und die andere ist eine „normale“ Formel. Zuerst werde ich mich um die „normale“ Formel kümmern.

Ohne Matrixformel:

Diese Formel tragen Sie natürlich wieder in die Zelle C1 ein. Dazu löschen Sie erst einmal wieder den aktuellen Inhalt der Zelle. Nun tragen Sie in sie folgendes ein:

=ZÄHLENWENNS(A:A;“x“;B:B;“y“)

Haben Sie die Formel eingegeben, sehen Sie umgehend das korrekte Ergebnis in der Zelle. In meinem Fall also logischerweise die Zahl „3“. Mit dem Ausdruck „A:A“ und „B:B“ wird einfach festgelegt, dass die komplette Spalte für die Formel verwendet werden soll. Auch hier ändern Sie die Suchbegriffe wieder dadurch, dass Sie das „x“ und das „y“ ersetzen. Möchten Sie, dass auch noch eine weitere Bedingung vorkommt, so tragen Sie diese in der Formel ein. Dazu schrieben Sie hinter dem „y“ einen Strichpunkt und geben dann den neuen Zellbereich ein. Anschließend machen Sie wieder eine Semikolon und dann  geben Sie das Zeichen ein, welches in dieser Zelle stehen soll. Ausgegeben wird dann die Anzahl an Zellen, die alle drei Bedingungen erfüllen.

Mit Matrixformel:

Wie vorhin schon einmal angesprochen ist die zweite zusätzliche Excel 2007 Möglichkeit die, eine Matrixformel zu verwenden. Auch diese schreiben Sie wieder in die Zelle C1. Löschen Sie dazu erst einmal ihren aktuellen Inhalt und geben Sie dann folgendes ein:

=SUMME((A:A=“x“)*(B:B=“y“))

Da es eine Matrixformel ist, sehen Sie nun noch nicht das richtige Ergebnis. Um das zu bekommen, müssen Sie wieder die drei mittlerweile bekannten Schritte tätigen. Klicken Sie also wieder in die Zelle. Anschließend klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie nun abschließend STRG+SHIFT+Enter. Nun sehen Sie das richtige Ergebnis, welches bei mir immer noch die Zahl „3“ ist. Aber auch die Formel hat sich wieder verändert. Neu sieht sie nun so aus:

{=SUMME((A:A=“x“)*(B:B=“y“))}

Haben Sie einen anderen Zellbereich gewählt, so ändern Sie „A:A“ und „B:B“ entsprechend ab. Außerdem ändern Sie die Suchbegriffe, indem Sie „x“ und „y“ mit den gewünschten Zeichen ersetzen.

Nun wissen Sie, wie man die Anzahl an Zeilen ausgibt, in denen bestimmte Bedingungen erfüllt sind. Möchten Sie, dass die Zeilen mehr als zwei Bedingungen erfüllen müssen, so lesen Sie entweder den dazugehörigen Text unter den Formeln, oder wenn es sich auf die Formeln bezieht, die ein Produkt ( * ) enthalten, so machen Sie folgendes: Fügen Sie einfach noch einen neuen Faktor in die Formel ein. Dieser sieht so aus, dass er in Klammern steht und in ihm der Zellbereich steht, darauf ein Gleichheitszeichen folgt und dann der zu suchende Wert in Anführungszeichen steht. Beispiel: (C1:C100=“z“)