Excel Formeln – Anzahl an Zeilen die eine Gesamtsumme überschreiten anzeigen

In diesem Artikel werde ich ihnen zwei einfache Wege zeigen, wie man in Excel die Anzahl an Zeilen ausgeben kann, in denen ein gewünschter Betrag überschritten wird.

Nehmen Sie an, Sie haben in Excel eine Tabelle, in der Sie ihre monatlichen und jährlichen Ausgaben darstellen. Dabei steht jede Spalte für einen Monat. Nun möchten Sie, dass ihnen die Anzahl an Jahren ausgegeben wird, in denen ihre Ausgaben einen gewissen Betrag überstiegen haben. Mit den zwei Möglichkeiten, die Sie hier kennenlernen ist das ein Kinderspiel.

Beginn:

Zuerst legen Sie die Tabelle an, in der die Zahlen eingegeben werden. Am besten ist es, wenn Sie erst einmal den Zellbereich von A1 bis E6 benutzen, damit Sie die Formeln später nicht extra umändern müssen. Um die Wirkungsweise der Formel zu demonstrieren, wäre es gut, wenn Sie sich vor dem Eintragen der Daten eine Zahl überlegen, die in manchen Zeilen überschritten werden soll und in manchen nicht. Bei mir zum Beispiel war es die Zahl 30, welche ich in der Zeile 3 und 6 überschritten habe.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Mit Hilfszellen:

Formel für die Hilfszellen:

Wie oben schon einmal ganz kurz angesprochen, gibt es zwei Wege, wie man an die Anzahl an Zeilen herankommt, in denen ein gewünschter Betrag überschritten wird. Die erste Methode ist die der Verwendung einer Hilfszelle, die zweite hingegen kommt ganz ohne aus. Zu Beginn möchte ich ihnen die erste Möglichkeit näher bringen. Wie schon gesagt, benötigen Sie hierfür Hilfszellen. Diese legen Sie am besten in der Spalte F an. Haben Sie andere Zellebezüge als ich gewählt, so nehmen Sie einfach die erste nicht benutzte Spalte. Dort tragen Sie hinter die erste Datenreihe – in meinem Fall in Zeile 2, also in der Zelle F2 – die folgende Formel ein:

=SUMME(A2:E2)

Mit dieser Formel wird der Gesamtbetrag der Zeile ermittelt. Haben Sie andere Zellbezüge als ich gewählt, so bitte ich Sie die Formel dementsprechend anzupassen. Direkt nachdem Sie die Formel eingegeben haben, werden Sie auch schon das korrekte Ergebnis sehen, da es keine Matrixformel ist. Diese Formel benötigen Sie natürlich auch für die anderen Zeilenbeträge. Deshalb kopieren Sie diese Formel nun nach unten. Dazu klicken Sie die Zelle nochmals an und fahren anschließend mit der Maus über ihre rechte untere Ecke. Anschließend verwandelt sich der Mauszeiger in ein Kreuz. Ist das passiert, so klicken Sie nun und halten Sie die Maustaste gedrückt, während Sie die Maus nach unten ziehen. Wie weit Sie gehen müssen, hängt davon ab, wie viele Zeilen Sie benutzt haben. Die Formel muss auf jeden Fall noch in der letzten verwendeten Zeile stehen. Lassen Sie die Maus nun wieder los und Sie sollten jetzt hinter den jeweiligen Einzelbeträgen den Gesamtbetrag der Zeile sehen.

Berechnung mithilfe der Hilfszellen:

Nachdem die Hilfszellen nun erstellt worden sind, können Sie damit beginnen die Anzahl an Zellen auszugeben, in denen ein bestimmter Wert überschritten wurde. Wählen Sie dazu irgendeine noch freie Zelle, wie zum Beispiel A8, aus und schreiben Sie in sie die folgende Formel:

=ZÄHLENWENN(F2:F6;“>30“)

Nachdem Sie die Formel eingegeben haben, wird ihnen umgehend die Anzahl an Zellen ausgegeben, in denen der Wert überschritten wurde. In meinem Fall beträgt dieser Wert „30“. Ausgegeben wird also die Zahl „2“, da die Bedingung in Zeile 2 und 6 erfüllt wurde. Nun aber zur Funktion dieser Formel. Die Formel Zählt zuerst alle Zellen in dem Bereich von F2 bis F6. Das sind 5 Zellen. Jetzt wird noch geschaut, wie viele von diesen 5 Zellen die Bedingung „>30“ erfüllen. Das sind noch zwei Zellen, daher auch die Ausgabe. Haben Sie andere Zellbezüge wie ich gewählt oder Sie möchten die Zellen auf einen anderen Überschreitungswert überprüfen, so ändern Sie die Formel entsprechend ab. Natürlich können Sie auch die Anzahl an Zellen ausgeben, in denen ein Wert nicht erreich wird. Dann müssen Sie aber „

Ohne Hilfszellen:

Wie ich schon angesprochen habe, gibt es zwei Wege an die Anzahl der Zeilen zu kommen, in welchen ein bestimmter Wert erreicht wird. Der zweite Weg besteht darin, dass komplett auf Hilfszellen verzichtet wird. Dies ist eigentlich auch fast immer möglich, wenn Hilfszellen verwendet werden. Da keine Hilfszellen gebraucht werden, benötigen Sie natürlich auch nur eine Formel. Schreiben Sie daher am besten in die Zelle A8 folgendes nachdem Sie den alten Inhalte gelöscht haben:

=SUMME(WENN(SUMMEWENN(BEREICH.VERSCHIEBEN(A1:E1;ZEILE(1:6)-1;;;);"<>0")>30;1))

Diese Formel ist aber eine Matrixformel. Das heißt, Sie können die Formel nicht direkt ausführen. Um Sie korrekt auszuführen, klicken Sie nochmals auf die Zelle, in meinem Beispiel A8. Danach klicken Sie oben in die Bearbeitungszeile der Formel. Nun drücken Sie auf ihrer Tastatur noch gleichzeitig die Tasten STRG+SHIFT+Enter, wodurch in der Zelle der richtige Wert angezeigt wird und sich die Formel so verändert:

{=SUMME(WENN(SUMMEWENN(BEREICH.VERSCHIEBEN(A1:E1;ZEILE(1:6)-1;;;);"<>0")>30;1))}

Möchten Sie die Formel verändern, so geht das auch wie bei den „normalen“ Formeln. Ist ihr Zellbereich ein anderer, so geben Sie statt „E1“ in der Formel einfach die letzte Zelle in der ersten Zeile ein, die noch verwendet werden soll. Haben Sie mehr als 5 Zeilen für die Datensätze benutzt, so ändern Sie die Zahl „6“ in der Formel so ab, dass auch die letzte Zeile mit Daten noch mit einbezogen wird.

Nun wissen Sie alles darüber, wie man die Anzahl an Zellen ausgeben kann, in denen ein bestimmter Wert erreicht, überschritt oder unterschritten wurde. Welche der beiden Möglichkeiten, ob mit Hilfszelle oder ohne, Sie benutzen ist völlig ihnen überlassen.