Excel bietet allerhand Funktionen, um Werte in Tabellen zu zählen: ANZAHL, ANZAHL2, ZÄHLENWENN, ZÄHLENWENNS. Damit werden schon sehr viele Szenarien abgedeckt.
Was machst du aber, wenn in deiner Tabelle Werte mehrfach vorkommen, du aber jeden Wert nur einmal zählen möchtest? Eine vermeintlich triviale Aufgabe, für die es jedoch in Excel leider keine Standardfunktion gibt.
Im heutigen Artikel zeige ich dir, wie man es mit einem kleinen Trick doch hinbekommt.
Und so geht’s:
Zur Demonstration habe ich folgende kleine Beispieltabelle vorbereitet, die du dir bei Bedarf hier herunterladen kannst:
Beispieltabelle mit mehrfachen Werten
Die Funktion ANZAHL liefert – wie der Name schon sagt – die Anzahl aller Werte im genannten Bereich zurück:
Anzahl aller Werte
Wie man allerdings unschwer erkennt, sind auch einige Werte doppelt enthalten. Diese wollen wir bei unserer Zählung jedoch ignorieren. Mit Hilfe der folgenden Array-Formel erreichen wir genau dieses Ziel:
{=SUMME(1/ZÄHLENWENN(A1:A10;A1:A10))}
Anzahl eindeutiger Werte
Wichtig:
Die geschweiften Klammern dürfen in dieser Formel nicht von Hand eingetippt werden. Stattdessen schließt man die Eingabe mit der Tastenkombination STRG+Umschalt+Enter ab!
Was passiert hier?
Die ZÄHLENWENN-Funktion
Sehen wir uns zuerst den inneren Teil der Formel an. Die ZÄHLENWENN-Funktion zählt die nichtleeren Elemente in einem bestimmten Bereich abhängig von dem angegebenen Suchkriterium:
=ZÄHLENWENN(Bereich; Suchkriterium)
Wenn wir unsere Beispieltabelle in eine Liste umwandeln, wird das Prinzip deutlicher. In Zelle F3 kommt die ZÄHLENWENN-Funktion zum Einsatz. Als Bereich wird die komplette Liste von E1:E31 angegeben, als Suchkriterium dient Zelle E1. Da der Wert 9 insgesamt 3x in der Liste vorkommt, liefert die Funktion den Wert 3 zurück:
Hilfstabelle: ZÄHLENWENN
Kopieren wir die Formel nach unten, ergibt sich folgendes Bild:
Die Anzahl jedes Elements
Für jeden Wert in der Liste wissen wir nun, wie oft er vorkommt.
Der Kehrwert
Im nächsten Schritt nehmen wir von jedem berechneten Wert den Kehrwert:
Kehrwerte bilden
Für die ersten drei Beispiele habe ich farbige Markierungen verwendet, damit man sieht, was damit erreicht wird:
Die Summe der Kehrwerte ergibt 1
Da die Zahl 9 insgesamt dreimal vorkommt, ist der Kehrwert jeweils 1/3 bzw. 0,33. Zählt man diese drei Werte zusammen, ergibt das 1. Die 11 kommt nur einmal vor, daher ist auch der Kehrwert 1. Die 15 gibt es zweimal, was einen Kehrwert von 1/2 bzw. 0,5 ergibt. Auch hier kommt als Summe der beiden logischerweise wieder 1 heraus.
Addieren wir nun in Zelle G32 sämtliche Kehrwerte, erhalten wir dadurch die Anzahl aller eindeutigen Werte:
Die Array-Funktion zum Vergleich
Die Array-Formel in G33 (und B14) macht nichts anderes, als die vorhergehenden Schritte in eine einzige Formel zu verpacken und kommt daher zum gleichen Ergebnis, nur eben bedeutend eleganter und ohne die ganzen Hilfsberechnungen.
Ich weiß, dass viele Excel-Anwender noch nie etwas von Array-Formeln gehört haben. Und vielen anderen sind sie einfach nicht ganz geheuer. Aber an dem heutigen Beispiel erkennt man ganz gut, wie nützlich sie sein können. Es kann also nicht schaden, sich hin und wieder einmal mit etwas „gruseligen“ Formeln zu beschäftigen 🙂
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
das war mal wieder ein richtig cooles Beispiel der Anwendung der Matrix- bzw. Arrayfunktion. Leider vergisst man in der Praxis oft diese auch mal wieder anzuwenden. Das werde ich für meine Userschulungen mit aufnehmen. Mit dem Beispiel kann man die Vorteile wirklich recht einfach erklären…..
Danke dir! Echt top!
Hallo Michael,
vielen Dank für das schöne Feedback! Freut mich sehr, wenn die Tipps praktische Anwendung finden.
Schöne Grüße,
Martin
Hallo Martin
Vielen Dank für die tolle Hilfestellung! Ich bin auf der Suche nach einer Lösung für das einmalige Zählen von mehrfach vorkommenden Werten in einer Spalte auf deine Seite aufmerksam geworden. Deine Lösung zum zählen funktioniert in meiner Tabelle einwandfrei bis auf den Fall, wo ich einen Filter setze. Der gezählte Wert bleibt immer der gleiche. Gibt es vielleicht eine Möglichkeit mit Einbezug von Filtern?
Gruss
François
Hallo François,
mit Filtern wird es richtig kniffelig. Die TEILERGEBNIS-Funktion berücksichtigt grundsätzlich gefilterte Werte, bietet aber keine Möglichkeit, nur eindeutige Werte zu zählen. Man muss also die oben im Artikel vorgestellte Lösung mit der TEILERGEBNIS-Funktion kombinieren. Ich habe ein wenig herumprobiert und kann – ohne jegliche Gewähr – folgende Lösung anbieten:
=AUFRUNDEN(SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT(„A“&ZEILE(A2:A12)))=1)*(1/ZÄHLENWENN(A2:A12;A2:A12)));0)
Ob das wirklich zuverlässig ist, kann ich nicht sagen. Einfach mal ausprobieren, vielleicht funktioniert’s ja.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank! Es hat bei mir leider nicht funktioniert, ich bin über den Bezug INDIREKT(“A”&ZEILE(… gestolpert? Auch die Formelauswertung hat mir nicht wirklich weitergeholfen. Macht aber gar nichts, alleine schon dein Trick =SUMME(1/ZÄHLENWENN(C5:C540;C5:C540)) hat mir sehr geholfen. Nochmals ganz herzlichen Dank.
Herzliche Grüsse aus der CH
François
Pingback: Die Summe der Top-3-Werte | Der Tabellen-Experte
Hallo Martin,
echt cooles Beispiel. Aber was mache ich denn, wenn ich leere Zellen in dem Bereich habe?
Danke für deine Hilfe.
Gruß
Sabine
Hallo Sabine,
da leere Zellen zu einem #DIV/0!-Fehler in der Formel führen, musst Du nur diesen Fehler abfangen:
{=SUMME(WENNFEHLER(1/(ZÄHLENWENN(A1:C10;A1:C10));0))}
Schöne Grüße,
Martin
oder ohne Matrixformel:
=SUMMENPRODUKT(1/(ZÄHLENWENN(A1:C10;A1:C10))*(A1:C10<>““))
Übrigens funktioniert diese Formel grundsätzlich auch dann, wenn keine Leerzeichen vorhanden sind. Der Vorteil von dieser Lösung ist: Es ist keine Matrixformel. Aber der Kern der Idee ist derselbe. Die Idee mit dem Kehrwert finde ich genial (habe gerade dieses Problem gehabt und natürlich hier wieder mal eine geniale Lösung gefunden).
Hinweis: HTML schein zugeschlagen zu haben, denn die Formel ist nicht sauber. Es muss am Ende wie folgt heißen:
(A1:C10 ungleich „“)
Ungleich sollte eigentlich in Kombination sein. Das scheint aber nicht zu funktionieren.
Hallo Andreas,
vielen Dank für diese schöne Alternative. Es stimmt, das HTML spielt einem hier leider immer wieder einen Streich. Ich habe die Formel in deinem Kommentar so angepasst, dass sie jetzt lesbar ist.
Schöne Grüße,
Martin
Hallo Martin,
das funktioniert mit Zahlen super.
Ich habe allerdings in diesem Fall keine Zahlen, sondern Artikelnummern (z.B. 100100-200). Ich habe die Formel ausprobiert, habe aber als eindeutige Anzahl „0“ heraus bekommen. Kann man die eindeutige Anzahl von diesen Nummern, oder auch von Bezeichnungen auch mithilfe einer Formel heraus bekommen, oder gehe ich da den uneleganten Weg alle Duplikate raus zu schmeißen und mir die dadurch gewonnen Informationen zusammen zu flicken?
Danke und viele Grüße
Sarah
Hallo Sarah,
dann muss sich irgendwo in deiner Formel der Wurm eingeschlichen haben. Denn die im Artikel gezeigte Matrixformel funktioniert nicht nur bei Zahlen, sondern auch bei Texten.
Schöne Grüße,
Martin
Hallo Martin,
danke dir für die tolle Erklärung.
Ich habe eine Tabelle vor mir, die in einer Spalte Artikelnummern hat.
Nun möchte ich diese unterschiedlichen Artikelnummern zusammenzählen jedoch nur die aus dem Standort A und aus dem Jahr 2017.
Sprich, ich möchte die Formel weiter Einschränken, aber wie ist das möglich. Ich habe es ein paar Mal mit =ZählenwennS probiert, klappt aber nicht.
Danke dir im Voraus
Hallo Tobias,
mit ZÄHLENWENNS bist du genau auf der richtigen Spur. Ich kenne jetzt den Aufbau deiner Tabelle nicht, daher folgende vereinfachte Annahme. Standort steht in Spalte B und das Jahr in Spalte C, dann lautet die Formel:
=ZÄHLENWENNS(B1:B100;“A“;C1:C100;2017)
In diesen beiden Artikeln ist die ZÄHLENWENNS-Funktion beschrieben:
https://www.tabellenexperte.de/wieviele-sind-das-eigentlich/
https://www.tabellenexperte.de/wenns-mal-wieder-ein-bisschen-mehr-excel-sein-darf/
Schöne Grüße,
Martin
Hallo Martin,
ich probier das Problem noch einmal zu beschreiben.
Insgesamt habe ich rund 40.000 Datensätze.
In Spalte A habe ich das Jahr, in dem das Teil verkauft wurde
In Spalte B habe ich den Standort, von dem es verkauft wurde (Standort Paris oder London)
In Spalte C habe ich die dazugehörige Artikelnummer.
Ich möchte nun herausfinden, wie viele unterschiedliche Artikelnummern Im Jahr 2016 vom Standort Paris verkauft wurden.
Wie viele unterschiedliche Artikelnummern im Jahr 2017 vom Standort Paris verkauf wurden usw.
Ich dachte da eher an eine Formel die in etwa so aussehen könnte, die natürlich nicht funktionier 🙁
{=SUMME(1/ZÄHLENWENNS(A:A;“2016″;B:B;“Paris“;C:C;C:C))}
Ich komme über Umwege zu meinem Ergebnis (mit Filtern und Dupplikaten enfernen, oder Hilfsspalten …) würde mich aber eben für die „elegantere“ Lösung interessieren.
Danke dir!
Hallo Tobias,
ok, der Knackpunkt ist hier tatsächlich, dass du nur die unterschiedlichen Artikelnummern brauchst. Wenn du Excel 2013 oder neuer einsetzt, geht das mit einer Pivot-Tabelle. Du musst nur beim Erstellen der Pivot-Tabelle das Häkchen „Zum Datenmodell hinzufügen“ auswählen. Danach gibt es in den Wertfeldeinstellungen nicht nur die Funktionen Anzahl, Summe etc., sondern auch diskrete Werte.
Falls du noch Excel 2010 oder 2007 hast, geht das leider nicht. Dann musst du eine Hilfsspalte erstellen, die eindeutige Einträge zählt. Gib in die erste Zelle folgende Formel ein (und achte unbedingt auf die genaue Setzung der $-Zeichen!)
=WENN(ZÄHLENWENNS($A$2:A2;A2;$B$2:B2;B2;$C$2:C2;C2)>1;0;1)
Und dann die Formel nach unten kopieren. Damit wird immer nur beim ersten vorkommenden Artikel die 1 gesetzt, ansonsten die 0. Und das kannst du jetzt in einer „normalen“ Pivot-Tabelle auswerten.
Schöne Grüße,
Martin
Hallo Martin,
absolut Top deine Erklärung und genau das, wonach ich schon lange suche. Der Tipp mit der Pivot ist echt genial, weil ich hauptsächlich mit Pivots arbeite.
Aber jetzt nochmal zurück zum Thema über die Formel. Ich habe die Anforderung, dass ich 2 Parameter habe. Sagen wir Kunden mit eindeutigen Produkten. Eindeutige Produkte über alle Kunde schaffe ich mit oben angeführter Formel. Sehe ich das richtig, dass es tatsächlich mit 2 Parametern nicht funktioniert?! Kann ich die oben angeführte Formel mit Zählenwenns nicht so erweitern, dass er mir JE Kunde die verschiedenen Produkte ohne Doppelzählung auswirft?
So wie es Tobias beschrieben hat. Hast du eine Idee warum das nicht funktioniert?
Lg Franz
Hallo Franz,
ich habe auch ziemlich lange mit einer Formel herumprobiert und ehrlich gesagt: Ich weiß es nicht 🙁
Schöne Grüße,
Martin
Hallo Martin,
diese Formel ist wirklich gut zu gebrauchen. Ich wollte sie gleich mal erweitern auf zwei (oder mehr) Spalten.
Als Beispiel diene eine Tabelle mit zwei Spalten, Name (A:A) und Vorname (B:B). Die Formel soll nun alle Paare ohne Mehrfachwertung zählen: =SUMME(1/ZÄHLENWENN(A:A&B:B;A:A&B:B)), natürlich als Matrixformel eingegeben. Dabei bekomme ich eine Fehlermeldung. Wenn ich die Pärchen in einer Hilfsspalte erstelle, also Spalte C = A:A&B:B und dann auf die Hilfsspalte referenziere, also =SUMME(1/ZÄHLENWENN(C:C;C:C)), funktioniert es prima. Auch die Syntax =SUMME(1/ZÄHLENWENN(C:C;A:A&B:B)) funktioniert. Mache ich hier einen Denkfehler oder ist das mal wieder ein Microsoft-Feature?
Die Formeln werden natürlich immer als Matrixformel eingegeben.
Viele Grüße, Reinhard
Hallo Reinhard,
eine sehr interessante Frage und ein in der Tat nicht so richtig nachvollziehbares Verhalten von Excel. Ich habe auch ein wenig herumprobiert, bin aber auch auf keine Lösung gekommen. Anscheinend akzeptiert die ZÄHLENWENN-Funktion wirklich nur für den zweiten Parameter (Suchkriterium) einen zusammengesetzten Ausdruck, nicht aber für den ersten Parameter. Warum das so ist, erschließt sich mir leider auch nicht.
Schöne Grüße,
Martin
Hallo Martin,
dann sitzt das Problem wohl doch in Redmond und nicht vor dem Computer. Schade, das hätte gut gepaßt. Aber mit einer Hilfsspalte geht’s ja auch. Danke für die Hilfe.
viele Grüße, Reinhard
Auch in Redmond sitzen sie vor einem Computer 🙂
Schöne Grüße,
Martin
Hallo Martin,
danke das funktioniert sehr gut, leere Zellen in dem Spaltenbereich muss man aber mit „1“ vorbesetzen sonst bekommt man kein Ergebnis. Da muss man dann nochmal „1“ subtrahieren um den richtigen wert zu bekommen.
Was ist aber wenn ich die3se Formel mit dem Teilergebnis verküpfen will weil ich durch einen Filter nur einen Teilbereich der Matrix betrachte?
Hallo Peter,
ja, bei leeren Zellen liefert die Formel einen Fehler. Aber ich verstehe deine Frage mit dem Teilergebnis nicht, das müsstest du etwas näher erläutern.
Schöne Grüße,
Martin
Hallo,
wenn ich Zählen ohne Duplikate mit Bedingungen verknüpfen möchte.
Wäre das z.B. so möglich =SUMME(1/ZÄHLENWENN(A26:A223;A26:A223))-ZÄHLENWENNS(C$26:C$223;“> 0,4″)
In Spalte A stehen Namen (teils auch doppelt) in Spalte C stehen Werte die ich auf verschiedene Weise auswerten möchte.
Also kleiner als 0,4 oder auch gößer als 0,4 und kleiner-gleich 0,8 usw.
Hallo Martin,
vielen Dank für diesen Artikel und diese Formel, sie hat mir bereits weiter geholfen.
Ein Problem hat sich mir dennoch aufgetan.
Ich möchte einem bestimmten Kriterium zugeordnete eindeutige Werte herauslesen und zählen.
Diese befinden sich jedoch in einer separaten Tabelle.
Sobald beide Tabellen geöffnet sind, funktioniert die Lösung mit der Formel =ZÄHLENWENN.
Sobald die Tabelle mit den „Rohdaten“ jedoch geschlossen ist, liefert mir die Formel keinen Wert.
SUMMENPRODUKT kommt leider nicht in Frage, da die eindeutigen Werte nicht gezählt werden.
Gibt es dafür eine einfache Lösung?
Viele Grüße
Martin
Hallo Martin,
nein, dafür gibt es keine einfache Lösung. Formeln, die auf andere Dateien verweisen, liefern nur verlässliche Ergebnisse, wenn die Dateien geöffnet sind. Abgesehen davon rate ich von dateiübergreifenden Formelkonstrukten unbedingt ab, wenn es irgendwie vermeidbar ist. Die machen nur Ärger.
Schöne Grüße,
Martin
Hallo Martin,
danke zunächst für deine Geduld mit uns Excel „Nichtfachleuten“. 🙂
Ich habe deine Formel für das Zählen nichtleerer Zellen ohne Dopplungen ausprobiert. Leider scheint sich Excel aufgrund der schieren Mengen an Zeilen (über 850.000 Zeilen) immer wieder aufzuhängen, weil ich als Ergebnis die Zahl „0“ ausgewiesen bekomme und Excel danach nicht mehr funktioniert. Wat nu? Hast du hierzu einen Rat?
Hallo Mario,
kein Problem, wir alle sind auf allen möglichen Gebieten ja Nichtfachleute und wollen etwas lernen 😉
Zu Deiner Frage:
Ja, bei dieser Menge an Daten wirst du mit einer Formellösung immer an Grenzen stoßen, dafür ist das Ganze einfach nicht ausgelegt. Wenn es sich um eine Liste handelt, in der alle relevanten Werte in einer einzigen Spalte stehen, würde ich auf die Schnelle eine Pivot-Tabelle verwenden. Die Liste sollte eine Überschrift haben, dann einfach alles markieren, Menü „Einfügen| PivotTable“. Und dann das Feld mit den Werten in den Zeilen-Bereich ziehen. Damit bekommst du eine Liste aller eindeutigen Werte.
Schöne Grüße,
Martin
cooler Tip … vielen Dank !!
Gern geschehen 🙂
Schöne Grüße,
Martin
Nun sitzen wir gleich zu Dritt und problieren Deine Beispiele, bzw. versuche sie nachzuempfinden.
– Doch leider, wie so oft ist dies mit Deinen Excel-Tips problematisch.
1. Schon das angekündigte „downloaden“ der Excel Tabelle funktioniert nicht. Klickt man auf den Link, dann ist „Pause“. Es tut sich, wie immer, nichts.
2. Erstellt man sich eine Beispielliste von Hand und gibt dann die Funktion (Formel), die Du zeigst, ein, dann funktioniert das auch nicht.
Schade, dabei glauben nun 2 Freunde, daß Du die Leute „veräppelst“. Ich halte Dir noch die Stange und schreibe Dir deshalb.
Aber warum können wir bei anderen „Online“-Teachern alles nachempfinden?
Der HInweis, daß man die { } Klammern nur mit „Strg“+“Shift“+“Return-Taste“ verwenden soll, ist wohl auch ein Scherz? Wie soll das zu verstehen sein? Die Tasten gibt es doch. Wenn man sie als ASCII-Wert eingeben soll, dann niemals mit „Strg“+“Shift“+“Return-Taste“.
Very strange, regards Rudy
Ich sehe, daß Du Dir viel Mühe gegeben hast,
Hallo Rudy,
erst einmal danke für deinen Kommentar. Warum der Download bei euch nicht klappt, kann ich leider nicht beantworten. Ein Test bei mir hat funktioniert. Aber es ist natürlich nicht ausgeschlossen, dass der Server gelegentlich überlastet ist.
Der Hinweis auf die spezielle Eingabe mit Strg+Shift+Return ist kein Scherz und hat auch nichts damit zu tun, dass man irgendeinen ASCII-Wert eingeben soll. Array-Funktionen in Excel müssen einfach auf diese Weise eingegeben werden (siehe auch https://support.microsoft.com/de-de/office/erstellen-einer-matrixformel-arrayformel-e43e12e0-afc6-4a12-bc7f-48361075954d). Lediglich die aktuelle Excel 365-Version arbeitet hier etwas anders.
Falls deine Freunde tatsächlich glauben, ich möchte hier jemand veräppeln, dann haben sie vermutlich einen sehr speziellen Humor. Oder glaubt jemand ernsthaft, ich stecke ungezählte Stunden in diesen Blog, nur um mich über irgendjemand lustig zu machen? Ich kann dich beruhigen, das ist nicht der Fall (mit wenigen Ausnahmen).
Es ist trotzdem schade, dass die Umsetzung bei Euch nicht geklappt hat. Den Kommentaren oben nach zu urteilen hat es zumindest bei anderen Lesern funktioniert.
Schöne Grüße,
Martin
Ich könnte mir vorstellen, dass es eventuell daran liegt, dass die geschweiften Klammern vor dem „Ctrl+Shift+Enter“ nicht entfernt wurden.
Hallo Martin,
hoffentlich hast du auch eine Lösungsidee für meine Problemstellung:
Ich hab im Bereich B2:B21 Zahlenwerte von 0 bis 10 eingetragen.
Im Feld B23 möchte ich angezeigt bekommen, die oft der Zahlenwert „5“ in diesem Bereich vorkommt – allerdings nur in den ungeraden Zellen (also B3, B5, B7, …,B21) und nicht im gesamten Bereich.
Wie lässt sich das realisieren?
Vielen Dank vorab & freundliche Grüße
Hallo Roman,
das ist etwas kniffeliger, aber die folgende Formel sollte das Problem lösen:
=SUMMENPRODUKT((B2:B21=5)*ISTUNGERADE(ZEILE(B2:B21)))
Schöne Grüße,
Martin
Hallo Martin,
super erklärt und ein toller Ansatz! Hat mir sehr im Controlling geholfen und mich weitergebracht!
Beste Grüße
Wolfgang
Hallo Wolfgang,
das ist schön zu hören, danke fürs Feedback.
Schöne Grüße,
Martin
Hallo
Dieser Ansatz lässt noch etwas zu wenig Flexibilität zu. Soll die einmalige Zählung von Elementen einer Gruppe in Abhängigkeit einer Liste von weiteren Gruppen gezählt werden sehe ich hier keine Lösung.
Beste Grüsse
Hallo Herr Weingartner,
das stimmt natürlich, das Beispiel ist hier sehr einfach gewählt. Für komplexere Fälle müssen andere Lösungen her. Hier bieten die neuen Array-Funktionen FILTER und EINDEUTIG in Microsoft 365/Office 365 deutlich mehr Komfort.
Schöne Grüße,
Martin
Hallo Martin,
Danke für die ausführliche Erklärung.
Ich hoffe du kannst mir bei meinem Problem helfen 🙂
Ich habe eine Tabelle vor mir liegen, bei dem Lieferanten aufgelistet werden die mehrmals vorkommen. Meine Aufgabe ist es, den Wert auszugeben, wie viel unterschiedliche Lieferanten wir haben. Nun sind in der selben Spalte mittendrin irgendwelche Überschriften wie „Kanban Lieferant“. Natürlich zählt Excel die Überschriften mit. Ich brauche aber nur die Lieferanten.
Meine Formel sieht aktuell wie folgt aus: =SUMMENPRODUKT(1/ZÄHLENWENNS(Lieferanten!$A$5:$A$500;Lieferanten!$A$5:$A$500))
Wie kann ich verhindert, dass er mir die Überschriften nicht mitzählt?
Vielen Dank im Voraus!
Hallo Lena,
wenn sich innerhalb der Daten „irgendwelche Überschriften“ befinden, dann wäre meine Empfehlung, die Daten von diesen Überschriften zu befreien 😉
Oder woran kann Excel erkennen, was Überschriften sind und was gewünschte Daten?
Schöne Grüße,
Martin
Hallo, die Formel klappt wunderbar, danke. Leider kann ich sie nicht anwenden, wenn die Liste sich zukünftig erweitert.
Wenn ich vorsorglich schon mal die gesamte Spalte oder auch nur einige zusätzliche Zeilen mit angebe, will es die leeren Zeilen nicht.
Wie kann man das lösen?
Hallo Martin,
für den Fall schlage ich eine formatierte („intelligente“) Tabelle vor. Die erweitert sich automatisch und wenn man in den Formeln dann Bezug nimmt auf die formatierte Tabelle, dann erwischt man immer den aktuellen Datenbereich.
Nehmen wir also das Beispiel aus dem Artikel und ich wandle den Zellenbereich A1:C10 in eine formatierte Tabelle um (markieren, Menü „Start | Als Tabelle formatieren“), für die ich den Name „tblDaten“ vergebe. Dann heißt die Zählformel statt
=SUMME(1/(ZÄHLENWENN(A1:C10;A1:C10)))
jetzt
{=SUMME(1/(ZÄHLENWENN(tblDaten;tblDaten)))}
Wenn neue Werte in der Tabelle dazukommen, werden diese automatisch von der Formel berücksichtigt.
Vielleicht hilft das ja.
Schöne Grüße,
Martin
Danke Martin – sehr hilfreich und auch so schön erklärt, dass man versteht was Excel tuten tut!
Gerne 😊
Hallo ,
Sehr schön und gut erklärt wie man mit Arrayformeln umgeht. Ich habe dies mit meiner Excel Tabelle, welche Temperaturdaten von meiner Wetterstation getestet, habe aber wahrscheinlich den falschen Ansatz.
In meiner Tabelle werden pro Tag 24 (pro Stunde) Daten gesendet.
Ich wollte nun über den ganzen Monat mit Zählenwenn die Tage herausfinden , an denen z.B 15 Grad oder mehr waren.
Das gibt aber einfalswches Ergebnis, da er pro Tag nur einmal die 15 Grad (wenn erreicht) zählen darf. Auch mit Zählenwenns habe ich nicht das gewünschte Ergebnis erreicht.
Hallo Thomas,
das ist eine etwas kniffelige Frage. Ich hätte eine Lösung, wenn du Excel aus Microsoft 365 im Einsatz hast. Folgende Annahme:
A2:A100 enthält das Datum
B2:B100 enthält die Uhrzeiten
C2:C100 enthält die Temperaturen
Lösungsformel:
=EINDEUTIG(FILTER(A2:A100;C2:C100>=15))
Das klappt also nur, wenn Datum und Uhrzeit in separaten Zellen stehen. Jedes Datum wird dann nur einmal gezählt.
Für ältere Excel-Versionen habe ich im Moment leider keine Lösung.
Schöne Grüße,
Martin
Vielen Dank für die Antwort.
Habe aber kein Excel 365 im Einsatz.
Danke
Thomas Sölch