Frage von Beatrix59, 46

Excel Formel Berechnung Anzahl2?

Ich habe folgendes Problem:

In einer Liste mit diversen Einträgen soll die Summe in einer Spalte errechnet werden.

In dieser Spalte gibt es Bereiche mit Zahlen und Datum und Bereiche mit Text und Datum.

Gezählt werden sollen aber nur die Bereiche mit Zahlen und Datum. Bisher habe ich im Interent dazu nichts gefunden, wie ich die Bereiche mit Text und Datum ausschließen kann.

Ich bedanke mich jetzt schon für die sicherlich zahlreichen Lösungsvorschläge.

(Ein Beispiel wollte ich anfügen, das klappte aber leider nicht)

Meine benutze Formel lautet:

=ANZAHL2(G4:G251)

anzumerken wäre noch, dass es auch leere Zellen gibt, die ich natürlich nicht zählen möchte

Antwort
von Funfroc, 27

Hallo,

dazu müsste man zumindest wissen, was in den Zellen nun genau drin steht.

Die Funktion Anzahl errechnet die Anzahl der Zellen, die Zahlen enthalten.

Die Funktion Anzahl2 errechnet die Anzahl der Zellen, die nicht leer sind.

Zahl und Datum ist eigentlich beides eine Zahl, da ein Datum im Hintergrund die Zahl der Tage seit dem 1.1.1900 erfasst hat und nur optisch als Datum dargestellt ist.

Also wäre die Funktion Anzahl mittel der Wahl.

Hast du nun aber Zahlen und Daten in einer Zelle stehen: "123 30.11.2016" so ist das keine Zahl, sondern Text. Und ein solcher Eintrag macht auch keinen Sinn.

Grundregel: nur gleiches in eine Spalte. Spalte A: Datum, Spalte B: Umsatz, Spalte C: Buchungstext... das wäre ein sinnvoller Tabellenaufbau.

Ggf. solltest du mal einen Screenshot hier hochladen, damit man deine Datenstruktur besser beurteilen kann.

LG, Chris

Edit: noch als Hinweis. Leute, die hier zum Beispiel auf Excel spezialisiert sind, bekommen die Frage prominenter angezeigt, wenn du Excel als Kategorie wählst. Excel-Berechnung als ein Suchbegriff macht da eher weniger Sinn.

Kommentar von Beatrix59 ,

Danke ersteinmal. In den Zellen stehen (mit Zeilenumbruch) eine 8 stellige Zahl, darunter das Datum, z.B. 30.11.16. Die Zellen die nicht gezählt werden sollen enthalten Text, z.B. ausverkauft und darunter das Datum. Nun möchte ich alle Zellen zählen, in denen nur die Zahl und das Datum enthalten ist, nicht aber die Zellen mmit dem Text "ausverkauft"

Kommentar von Funfroc ,

Auch auf die Gefahr hin, mich zu wiederholen. So baut man keine Tabelle auf... zumindest nicht, wenn man damit weiterarbeiten und Berechnungen ausführen will.

Wenn du uns mal einen Screenshot zur Verfügung stellen würdest, sage ich dir gerne, wie der Aufbau konkret zu machen wäre.

Dateien hochladen kannst du aber nicht in den Kommentaren, dafür müsstest du eine eigene Antwort geben. Eine XLS bekommst du hier auch nicht hochgeladen, daher muss es eben ein Bild sein. Auf dem Bild sollten bitte Zeilen und Spalten-Nummern zu sehen sein.

LG, Chris

Kommentar von Iamiam ,

So, jetzt nhab ich eine Stunde umsonst rumprobiert, weil ich erst jetzt zu lesen bekomme, dass beides in EINER ZELLE, und nicht abwechselnd in einer Spalte steht. Wenn Du jetzt noch sagen kannst, ob der Text immer am Anfang oder am Ende steht -noch besser: immer in der gleichen Reihenfolge-) oder gemischt, und ob das Datum da drin immer das gleiche Format (welches? zB 12.12.2016 oder ob auch 7.7.2016 oder noch was anderes vorkommen kann) hat, vllt auch noch, ob die Zahl immer ein Komma und zwei Nachkommastellen hat(oder englische Trennzeichen? Woe Groß, von..bis), dann lässt sich vielleicht noch was machen, aber ich hab die nächsten 2 Tage dafür keine Zeit mehr, das dauert nämlich!

Expertenantwort
von Iamiam, Community-Experte für Excel, 5

i'wie sehe ich einen Widerspruch in Deinen Angaben: Datum willst du doch gar nicht ausschließen, oder doch? Entscheide selbst:

Texte: =Anzahl2(G4:G251)-Anzahl(G4-G251)

Zahlen incl. Datum: =Anzahl(G4:G251)

Fürs Datum brauchst du eine Hilfsspalte, zB H,

H4: =Links(Zelle("Format";G4);1) runterkopieren

Dann kannst du die Vorkommen von D summieren. Um aber auszuschließen, dass auch leere Zellen gezählt werden, die als Datum/Zeit formatiert sind, muss noch eine weitere Bedingung verwendet werden:

=Summenprodukt(Nicht(Istleer(G4:G251))($H$4:$H$251="D"))

Reine Zahlen erhältst Du also mit

Anzahl(G4:G251)-Summenprodukt(...)

Ich hab zumindest keinen Weg gefunden, Datümer ohne Hilfsspalte auszuschließen.

kannst Du allerdingssicherstellen, dass es

keine Zahlen zwischen 40000 und 43000 gibt und
alle Datümer zwischen dem 7.7.2009 und dem 21.7.2017 liegen,

geht auch

=Summenprodukt(Istzahl($G$4:$G$251)($G$4:$G$251>40000)*($G$4:$G$251<43000))

Die Zahlen 40000 und 43000 kannst Du natürlich auch anders anpassen.

Antwort
von offeltoffel, 23

Die Funktion "Anzahl" tut genau das: sie berechnet die Anzahl an Zellen, denen ein Zahlenwert zu Grunde liegt (also Zahlen, aber auch Datum). Anzahl2 zählt hingegen die Anzahl an nicht-leeren Zellen (also auch Text).

Anzahl gibt dir aber (wie der Name eigentlich sagt) nicht die Summe wieder. Die Funktion "Summe" zählt die Werte aller Zelle zusammen, die von "Anzahl" berücksichtig werden - also Zahlen und und auch Datum (was oft für Verwirrung sorgt...15. August 2016 plus 12 = 42609 (oder 27. August 2016, je nach Formatierung der Zelle)

Solltest du nur die Anzahl der Zahlen OHNE Datum wollen (falls du dich in der Frage verschrieben hast) wird es in der Tat schwierig, wenn du kein VBA benutzen möchtest. Sollten die Zahlen relativ klein sein, kannst du dir eine Krücke bauen:

ZÄHLENWENN(A1:A10;"<42370")

Diese Funktion zählt nur die Anzahl der Zellen, deren Wert kleiner als 42370 ist. Diese Grenzwert habe ich genommen, weil es als Datum dem 01.01.2016 entspricht. Wenn du also nur Datums aus diesem Jahr hast, funktioniert es. Generell klappt es immer dann, wenn deine größte Zahl immer noch kleiner ist, als das kleinste Datum. Falls nicht, wirst du um VBA nicht drum herum kommen - dabei kannst du nämlich überprüfen, wie eine Zelle formatiert ist und sie ggf. vom Zählen ausschließen.

Kommentar von Beatrix59 ,

achhier meinen Dank. Ich meinbte mit der Summe, das zählen der betreffenden Inhalte - bei mir ist das immer noch eine "Summe".

Das Datum ist relevant und muss stehen bleiben. Zu zählen sind nur die Zellen mit der 8 stelligen Zahl mit darunter stehendem Datum. Nicht aber die Zellen mit Text, z.B. "ausverkauft" mit darunter stehendem Datum.

Kommentar von offeltoffel ,

In dem Fall muss ich "funfroc" absolut Recht geben. In einer einzigen Spalte große Zahlen, Texte und Datums zusammenzulegen, macht keinen Spaß und keinen Sinn. Genau aus Problemen, wie du sie beschrieben hast.

Ich würde mir ernsthafte Gedanken über eine Umstrukturierung der Daten machen. Sollten die Zahlen allerdings allesamt achtstellig sein, dann probier mal Folgendes:

angenommen, deine Info-Spalte ist die gesamt Spalte A. Dann schreib z.B. in B1:

=ZÄHLENWENN(A:A;">9999999")

Damit zählt er dir alle Zahlen, die mind. 8 Stellen haben. Da kommt ein Datum effektiv nie hin.

Edit: Ach, jetzt willst du doch wieder Datum haben? Sorry, du redest ziemlich wirr...

Antwort
von offeltoffel, 19

Sorry für den Doppelpost, aber....kannst du an dem Beispiel bitte erklären, was du genau für eine Lösung brauchst?

Kommentar von Beatrix59 ,

wie ich schon schrieb:

In den Zellen stehen (mit Zeilenumbruch) eine 8 stellige Zahl, darunter das Datum, z.B. 30.11.16.

Die Zellen die nicht gezählt werden sollen enthalten Text, z.B. ausverkauft und darunter (mittels Zeilenumbruch) das Datum, z.B. 30.11.16

Ich möchte alle Zellen zählen, in denen nur die 8 stellige Zahl und das Datum enthalten ist, nicht aber die Zellen mit dem Text "ausverkauft" + Datum.

Kommentar von offeltoffel ,

Nein, du hast geschrieben:

Nun möchte ich alle Zellen zählen, in denen nur die Zahl und das Datum enthalten ist, nicht aber die Zellen mmit dem Text  "ausverkauft"

Das ist etwas völlig anderes.

Jetzt verstehe ich aber, was du mit Zeilenumbruch meinst. Du hast nicht nur innerhalbe einer SPALTE verschiedene Dinge, sondern auch innerhalbe einer ZELLE? Um Himmels Willen, damit kannst du getrost das Handtuch werfen.

Wenn du sowas in einer Zelle hast:

"10120230

30.11.2016"

Ist das nämlich keine Zahl mehr, sondern ein TEXT. Du kannst eine Zelle nämlich nicht verschiedenartig formatieren, deswegen ist das für ihn alles Text. Und damit scheiden Funktionen wie ANZAHL und ZÄHLENWENN etc schonmal aus.

Für Excel ist das also nicht "10120230" sondern "einsnulleinszweinullzweidreinull".

Woher kommt diese Tabelle? Hast du die erhalten oder selbst generiert? Strukturier sie sauber um, dann klappt es auch mit den Funktionen.

Antwort
von Beatrix59, 12

Vielen Dank an Alle, jetzt ist mir klar, warum ich mit den vielen Versuchen der diversen Funktionen nicht zum gewünschten Ergebnis kam. Schade

Kommentar von offeltoffel ,

Woher kommt diese Tabelle? Hast du die erhalten oder selbst generiert? Strukturier sie sauber um, dann klappt es auch mit den Funktionen.

Keine passende Antwort gefunden?

Fragen Sie die Community