Tag Archives: zählenwenn

Wer spät zu Bett geht und früh heraus muss, weiß, woher das Wort Morgengrauen kommt.

Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:

„Hallo Rene

zum Thema Datumsberechnungen hätte ich gleich eine Frage:

kann Excel auch Zeiträume erkennen, die sich überschneiden, aber unterschiedliche Anfangs- und Endzeiten haben?

Also zum Beispiel:

Mitarbeiter A arbeitet vom 01.05.2021 bis 31.08.2021

Mitarbeiter B arbeitet vom 01.06.2021 bis 15.09.2021

In welchem Zeitraum haben beide gearbeitet

Oder

Von Bis Thema
8:00 12:00 Nachdenken
11:30 12:30 Pause
12:30 15:00 Nix tun

Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?

Im ersten Schritt habe ich das visualisiert:

Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:

=WENN(B33<$B$21;0)

Dann die umschließenden Bereiche:

=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)

Und schließlich die überschneidenden Bereiche:

Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)

Analog die zweite Überschneidung:

Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:

=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)

Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:

und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22;B25>=$B$21);B25-$B$21+1;0)

beziehungsweise:

=WENN(UND(B27>=$B$22;B26>=$B$21;B26<=$B$22);$B$22-B26+1;0)

so:

Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:

  1. Fall: leere Menge
  2. Fall: ganzer Bereich
  3. Fall: Überschneidung
  1. Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)

2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:

=WENN(ODER(B33<$B$21;B32>$B$22);0;WENN(UND(B32>=$B$21;B33<=$B$22);B33-B32+1;0))

2. Fall b: Oktoberfestzeitraum liegt im Datumsbereich:

=WENN(ODER(B31<$B$21;B30>$B$22);0;WENN(UND(B30>=$B$21;B31<=$B$22);B31-B30+1;WENN(UND($B$21>=B30;$B$22<=B31);$B$22-$B$21+1;0)))

Und schließlich die beiden Fälle Nummer 3:

=WENN(ODER(B25<$B$21;B24>$B$22);0;WENN(UND(B24>=$B$21;B25<=$B$22);B25-B24+1;WENN(UND($B$21>=B24;$B$22<=B25);$B$22-$B$21+1;WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0))))

und:

=WENN(ODER(B27<$B$21;B26>$B$22);0;WENN(UND(B26>=$B$21;B27<=$B$22);B27-B26+1;WENN(UND($B$21>=B26;$B$22<=B27);$B$22-$B$21+1;WENN(UND(B26<=$B$21;B27<=$B$22);B27-$B$21+1;WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)))))

Natürlich hätte man die Bedingungen auch umdrehen können.

Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:

  1. Fall: Anfang <= Oktoberfest Anfang? Ja:
    Fall a) Ende vor Oktoberfest Anfang?
    Fall b) Ende nach Oktoberfest Ende?
    Fall c) Ende zwischen Oktoberfest Anfang und Ende?
    […]

Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:

Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:

=SEQUENZ(A6-A5+1;1;A5)

Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:

=ZÄHLENWENN($E$1#;C1)

Und diese Kolonne kann man summieren.

Oder in einer Formel:

=SUMME(ZÄHLENWENN($C$1#;SEQUENZ(A6-A5+1;1;A5)))

Geht das auch mit FILTER? Klar:

=FILTER(C1#;(C1#>=A5)*(C1#<=A6))

Oder in einer Formel:

=FILTER(SEQUENZ(A2-A1+1;1;A1);(SEQUENZ(A2-A1+1;1;A1)>=A5)*(SEQUENZ(A2-A1+1;1;A1)<=A6))

Ich bin sicher, dass es für dieses Problem noch weitere Lösungen gibt. Viel Spaß beim Knobeln.

Hallo Rene,
meine Lösungsvorschlag kommt mit einer einer einfachen Formel (ohne Matrixformel) aus.

Das Startdatum des Oktoberfest steht in B2
Das Enddatum des Oktoberfest steht in B3

Der erste Tag von Adele in München steht in B5
Der letzte Tag von Adele in München steht in B6

Dann berechnet folgende Formel die Überschneidung, also die Anzahl von Tagen die Adele auf das Oktoberfest gehen kann.

=ZEILEN(INDEX(A:A;B2):INDEX(A:A;B3) INDEX(A:A;B5):INDEX(A:A;B6))

Im Falle Adele 13 Tage.

Für die anderen Personen muss diese Formel nur entsprechend angepasst werden.
Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus.
Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.

Salü

Ernst

PS: Nachtrag:

Allerdings kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge eingetragen sind.

Also

=ZEILEN(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6))

Ergebnis = 13

{=ZEILE(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6)) }

Ergebnis ( Zellen als kurzes Datum formatiert.):

18.09.2021
19.09.2021
20.09.2021
21.09.2021
22.09.2021
23.09.2021
24.09.2021
25.09.2021
26.09.2021
27.09.2021
28.09.2021
29.09.2021
30.09.2021

Danke Ernst – sehr clever!

Und schließlich hat Helmut Cantzler eine Lösung mit SUMMENPRODUKT gefunden:

=SUMMENPRODUKT((SEQUENZ($B$3-$B$2+1;;$B$2;1)<=B6)*(SEQUENZ($B$3-$B$2+1;;$B$2;1)>=B5))

Danke an Helmut – auch sehr clever!

Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:

    Dim xlBereich1 As Range
    Dim xlBereich2 As Range
    Dim xlSchnittmenge As Range
    With ThisWorkbook.Worksheets("Helmut")
        Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1), 
           .Cells(.Range("B3").Value2, 1))
        Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1), 
           .Cells(.Range("B6").Value2, 1))
    End With
    Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
    MsgBox xlSchnittmenge.Cells.Count

Oder in einem Befehl:

MsgBox Application.Intersect(Range(Cells(Range("B2").Value2, 1), Cells(Range("B3").Value2, 1)), Range(Cells(Range("B5").Value2, 1), Cells(Range("B6").Value2, 1))).Cells.Count

Klappt! Danke an Andreas Protzmann für diesen Hinweis. Auch clever!

Und schließlich reagiert Christian:

Auch sehr clever! Und sehr elegant! Danke, Christian.

In einem Meeting einen Zettel hochhalten: „Sprecht ganz normal weiter! Wir werden abgehört! Lasst euch nichts anmerken!“

Lieber Rene

Wie geht es dir? Ich habe wieder mal eine Frage:

Im Anhang findest du eine Tabelle. Was ich möchte ist folgendes:

Ich möchte wissen, welche Zahlen zwischen der kleinsten und der grössten Zahl alle noch fehlen. Am liebsten hätte ich alle fehlenden Zahlen in einer Spalte.

Herzliche Grüsse

Andreas

Hallo Andreas,

ich verstehe nicht ganz – in der Liste befinden sich keine Zahlen, sondern Texte. Texte der Form CHE-xxx.yyy.zzz

Was heißt in diesem Zusammenhang „die kleinste und die größte Zahl“?

LG ::  Rene

Lieber Rene

CHE-000.000.000 (kleinst mögliche), CHE-000.000.001 …..  CHE-999.999.999 (grössmögliche).

Vielleicht sollte ich besser „Nummern“ schreiben, das „CHE-“ könnte man auch weglassen, man könnte auch die Punkte durch tausender Trennzeichen ersetzen, damit es  Zahlen werden: 000’000’001 bis 999’999’999

In der Matrix hat es eine Auswahl von möglichen „Nummern“, eine „Nummer“ ist die Grösste, eine die Kleinste, dazwischen hat es in der Matrix welche, aber es fehlen auch welche. Ich möchte gerne alle Nummern, von der kleinsten Nummer bis zu Grössten Nummer, die mir noch fehlen und so, dass ich Sie in einer Spalte habe und sortieren kann.

Konnte ich mich verständlich ausdrücken?

Herzliche Grüsse Andreas

Hoi Andreas,

kurz nachgeschaut:

die kleinste „Zahl“ in deiner Liste ist CHE-100.000.058, die größte: CHE-499.992.187. Das heißt: wir müssten fast 500 Millionen Zahlen prüfen.

Ich habe es mal mit den Zahlen zwischen 100.000.000 und 101.000.000 versucht – bei einer Formel (bist du drin? -> Zählenwenn) geht Excel in die Knie. Nicht auszudenken, wenn du das in einer Spalte „zusammengefasst“ haben möchtest und das ganze x 500!

Da stürzt Excel ab.

Ich würde es entweder mit PowerQuery lösen oder mit VBA.

Liebe Grüße

René

Hoi Rene

1.000 Dank!

Ich prüfe das nochmals…

Herzliche Grüsse

Andreas

Früher war alles leichter. Ich auch.

War das früher besser? Zumindest ging das früher!

Vorn vorne: die Excel-Funktion HÄUFIGKEIT ist eine Matrixfunktion. Mit ihrer Hilfe (ebenso wie mit ZÄHLENWENN oder SUMMENPRODUKT) kann man die Häufigkeit von Daten in einer Liste bestimmen, beispielsweise, wie oft Notenwerte vorhanden sind. Dabei muss Die Funktion als Matrixfunktion verwendet werden, das heißt mit [Umschalt] + [Strg] + [Enter] beendet werden:

Das funktioniert bei exakt vorkommenden Werten, aber auch bei „Zwischenwerten“ – von – bis:

Man kann die Funktion aber auch als eine Funktion verwenden und runterziehen – dann werden die Werte kumuliert:

Nein – so konnte man das früher machen – vor den Spillfunktionen (den Arrayfunktionen SEQUENZ, SORTIEREN, EINDEUTIG, …) machen. Seit Microsoft diese Funktionen und diese Arbeitstechnik eingeführt hat, erzeugt HÄUFIGKEIT beim Markieren eines Wertes zwei Ergebnisse: Wie oft taucht dieser Wert auf und wie viele andere Werte sind vorhanden.

Und heute? – Man muss HÄUFIGKEIT mit einem @ entwerten, dann hat man wieder die gleiche Funktionalität wie früher:

Danke an Mourad Louha für diesen Hinweis.

Woran erkennt man, dass man zu viel mit Excel gearbeitet hat? Wenn man die Augen schließt und sich das Excel-Grid in die Netzhaut eingebrannt hat (Daniel Kogan – excelhero)

Verblüffend!

Man schreibe in eine Zelle den Text ‚WAHR. Das Ergebnis wird linksbündig als Text angezeigt. Das Gleiche passiert, wenn man eine Zelle als Text formatiert und anschließend mit den Buchstaben WAHR füllt.

Soweit so gut. Die Überprüfung mit der Funktion ISTTEXT bestätigt, dass ein Text in der Zelle steht; ISTLOG verneint die Existenz eines booleschen Wertes (WAHR oder FALSCH). Auch die Überprüfung mit =Zelle=WAHR und =ZELLE=“WAHR“, verneint das erste und bestätigt das zweite:

Irritierend ist nun, dass die Funktion ZÄHLENWENN 0 liefert, wenn man den TEXT „WAHR“ zählt:

Der Blick in den Funktionsassistenten irritiert:

Verblüffend!

Menschen, die mich vor 9 Uhr fragen, wie es mir geht, schauen auch mit dem Streichholz nach, ob noch Benzin im Tank ist.

Na, ein bisschen mehr Mühe hätte sich VBA schon geben können:

Anwendungs- oder objektorientierter Fehler. Na toll! Und wo? Und was?

Der Debugger hilft: die Berechnung Row – 8 ergibt einen falschen, nämlich negativen Wert (falsch gerechnet; nicht aufgepasst!) – so kann die Zelle „C-1“ nicht ermittelt werden.

Auch hier: Typen unverträglich!

Ey, VBA: sag mir doch deutlich, dass ich Dumpfbacke bei der Funktion MATCH (VERGLEICH) die beiden Parameter vertauscht habe. Während die Funktion der Zeile darüber (COUNTIF, ZÄHLENWENN) die Parameter ich suche wo wen verlangt, ermittelt MATCH / VERGLEICH: ich suche wen wo. Und keiner hilft mir! *lach*

Heimat ist da, wo dir die Todesanzeigen etwas sagen (Ottfried Fischer)

Amüsant. Gestern in der Excelschulung fragte ein Teilnehmer, ob man nach Duplikaten sortieren oder filtern kann. Das kann man natürlich nicht:

Ich habe ihm die Lösungsansätze genannt, die mir eingefallen sind:

  • die Funktion ZÄHLENWENN (und dann sortieren/filtern)
  • Bedingte Formatierung (und dann sortieren/filtern)
  • Pivottabelle (und dann sortieren/filtern)
  • Spezialfilter

Habe ich etwas vergessen?

Als Baby wirst du für ein Bäuerchen gelobt; dann perfektionierst du es jahrelang und dann bekommst du Schelte!

Hallo René,

Frage am Rande:

Den Mittelwert Teilnahme über alle Spalten Teilnahme, die >0 bekomme ich wie am schicksten? Mittelwertwenn geht nur über einen zusammenhängenden Bereich, zählenwenn auch,…

Am Ende ist die Tabelle so:

Hallo Florian,

ja, das ist richtig: ZÄHLENWENN, MITTELWERTWENN kann keine getrennten Bereiche verarbeiten. Der Grund:

Wie soll man ZÄHLENWENN(A2:A5;C2:C5;“>0“)

verstehen?

Also muss du den MITTELWERT „nachbauen“. Mittelwert ist ja Summe/Anzahl

Also beispielsweise so:

=SUMME(A2:A6;D2:D6;G2:G6)/(ZÄHLENWENN(A2:A6;“>0″)+ZÄHLENWENN(D2:D6;“>0″)+ZÄHLENWENN(G2:G6;“>0″))

die Spalten A, D und G

Oder:

{=SUMME(A2:A6;D2:D6;G2:G6)/SUMME((A2:A6>0)*1;(D2:D6>0)*1;(G2:G6>0)*1)}

als Matrixfunktion mit Umschalt+Strg+Enter beendet.

Zu Erklärung der zweiten Funktion: A2:A6>0 liefert WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR

diese Wahrheitswert kann ich jedoch nicht summieren. Multipliziere ich sie mit 1, so erhalte ich

1;1;0;1;1;1

Und das kann ich summieren.

Es gibt noch weitere Ansätze.

Liebe Grüße

Rene u

Ich bin heute so romantisch. Ich könnte alle Menschen drücken. Gegen eine Wand. Mit dem Gesicht zuerst.

Amüsant: Überprüft man, ob in einer leeren Zelle der Wert 0 steht, also =A1=0, so lautet das Ergebnis WAHR. Leer wird als 0 interpretiert. Das gleiche Ergebnis erhält man bei der Funktion SUMMENPRODUKT:

=SUMMENPRODUKT((B2:B17>=0)*1)

Die leeren Zellen werden als 0 interpretiert. Anders dagegen ZÄHLENWENN, SUMMEWENN & co – dort ist 0 etwas anderes, wie

=ZÄHLENWENN(B2:B17;“>=0″)

zeigt:

Danke an Dominic Dauphin für diesen Hinweis.

Für Mädchen ist es besser nicht zu heiraten, aber Junx brauchen jemanden zum Putzen.

Ein Vortrag von Martin Weiß über Bedingte Formatierungen auf den Excellent Days. Er zeigt, wie man mit der Funktion ZÄHLENWENN alle Zahlen einfärben kann, mehr als drei mal vorkommen. Die Formel in der Bedingte Formatierungen lautet:

=ZÄHLENWENN($A$3:$O$32;A3)>3

Alle machen brav mit. Mein Nachbar wundert sich, warum bei ihm keine Zelle rot eingefärbt wird. Er schaut in den Manager für Regeln zur Bedingten Formatierung und wundert sich über die Formel:

=ZÄHLENWENN($A$3:$O$32;XEQ1048550)>3

Der Grund ist einfach: Er hat den Bereich von rechts unten nach links oben markiert. Deshalb wird ein relativer Bezug A3 in der Zelle A3 umgesetzt in: 30 Zeilen nach oben und 15 Spalten nach links. So kommt XEQ1048550 zustande.

Damit so etwas Nervendes nicht passiert, schlage ich ein anderes Vorgehen vor:

Ich beginne im ersten Schritt mit einer Zelle, beispielsweise A3 (oder O32) und aktiviere dort die Bedingte Formatierung:

Im zweiten Schritt aktiviere ich erneut den Regelmanager (Start / Bedingte Formatierung / Regeln verwalten) und dehne nun den Bereich aus:

Das klappt:

Okay – ein Schritt mehr – aber ich bin auf der sicheren Seite. Damit Excel nicht mehr nervt!

 

In den 10 Minuten bevor meine Mutter zu Besuch kommt, schaffe ich mehr Hausarbeit als in einer Woche.

Hallo René

Ich möchte die Rückläufer auswerten

Ich habe vor alle blätter in eine mappe zu kopieren

Dann mit =ZÄHLENWENN(‚ANFANG:ENDE‘!B16;“Ja“) auszuwerten

Ich bekomme #WERT! Zurück

Geht zählenwenn nicht über mehrere Arbeitsblätter nicht?

Ich habe es mit INDIREKT(„‚ANFANG:ENDE‘!B16“;TRUE) … versucht; dann bekomme ich #BEREICH!

Ich weiß, wir hatten das am Stammtisch und du hast auch was gesagt, geschriebenes finde ich nicht

Liebe Grüße

Stefan

Hallo Stefan,

schau mal, was ich vor einem Jahr veröffentlicht habe:

http://www.excel-nervt.de/die-stille-zeit-ist-zu-ende-jetzt-wird-es-wieder-ruhig-karl-valentin/

Also doch anders …

Liebe Grüße

Rene

;-(

Ich brauche EXCELlent DAYS zur Weiterbildung

Viele Grüße

und Danke

Stefan

Meditieren ist immer noch besser, als rumsitzen und nichts tun.

Heute in der Excelschulung. Ich zeige den Teilnehmer das „Leerzeichenproblem“: Manchmal geben Anwender und Anwenderinnen am Ende eines Textes Leerzeichen ein, das man nicht sichtbar machen kann. Das ist fatal beim Sortieren, bei Pivottabellen, die Funktionen WENN, ZÄHLENWENN, SUMMEWENN rechnen vermeintlich falsch … Man kann solche Leerzeichen mit der Funktion RECHTS ausfindig machen, man kann sie mit GLÄTTEN entfernen, … All das zeige ich. Weise darauf hin, dass man (zugegeben: sehr schlecht!) die Leerzeichen sichtbar machen kann, wenn man die Texte rechtsbündig formatiert:

Eine Teilnehmerin meldet sich und fragt, warum bei ihr die Texte am Ende KEINE Lücke aufweisen, obwohl sie ein Leerzeichen eingefügt hat:

Die Antwort: Wenn am Ende eines Textes sich ein Leerzeichen befindet, wird es unterdrückt, wenn ein Textumbruch eingeschaltet ist !?!

Die Sünde ist der beste Teil der Reue

Schon seltsam.

Eine Liste enthält Texte und Zahlen. Ich möchte herausfinden, wie viele Zellen gefüllt sind. Ich versuche ZÄHLENWENN. Wir wissen, das zwei Anführungszeichen innerhalb eines Textes als ein Anführungszeichen interpretiert wird. Also versuche ich:

=ZÄHLENWENN(C2:C15;“<>“““““)

Es scheitert!

Obwohl die Bedingung <>““ korrekt arbeitet, muss man die Bedingung folgendermaßen formulieren:

=ZÄHLENWENN(C2:C15;“<>“)

Analog werden die Anzahl der leeren Zellen ermittelt:

=ZÄHLENWENN(C2:C15;“=“)

Dann klappt es.

Beim nächsten Mal verwende ich wieder:

=ANZAHL2(C2:C15)

oder:

=ZEILEN(C2:C15)-ANZAHLLEEREZELLEN(C2:C15)

Ich kann 3 stimmig singen … laut, falsch und mit voller Begeisterung

Heute im Excel-Coaching in einer Rechtsanwaltskanzlei.

Die Aufgabe: Aus einer Liste von Prozesskosten soll eine Übersicht erstellt werden, wir oft Kosten im Bereich 0 – 10.000 Euro, 10.000 – 20.000 Euro 20.000 – 30.000 Euro und so weiter vorhanden sind. Ich überlege: ZÄHLENWENN oder HÄUFIGKEIT? Ich entscheide mich für eine Pivottabelle. Schnell erstellt, schnell gruppiert – aber: padautz!

„Kann den markierten Bereich nicht gruppieren.“

Dieser Satz hat kein Subjekt. Und auch keine Begründung. Versuche es mehrmals, ziehe, schiebe, lösche … geht nicht. Bis ich ans Ende der Pivottabelle schaue:

Da hat doch tatsächlich jemand Zahlen falsch eingegeben: 49,240.8 oder 11,593.00. Und hat auch noch einige Texte eingetragen: „to be added“, bzw. „t.b.a.“ Also doch ZÄHLENWENN …

Hatte Kribbeln im Bauch … dachte schon bin verliebt … aber war doch nur Hunger …

Schon mal probiert?

Eine Datei greift mit einer Formel auf eine zweite Datei zu. Kein Problem.

Doch: ist ein Problem – nämlich, wenn es sich bei der Formel um SUMMEWENN, ZÄHLENWENN & co handelt. Dann muss nämlich die Quelldatei offen sein, damit kein Fehler angezeigt wird. Sehr erstaunlich!

Ich bin sehr wohl spontan … wenn man mir früh genug Bescheid sagt

Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:

Eine Spalte ist als Text formatiert:

In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:

Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit

{=ODER(K2=$G$2:$G$22)}

(als Matrixformel) korrekt als unterschiedliche Werte erkannt:

Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:

{=ODER(IDENTISCH(K2;$G$2:$G$22))}

Ebenso übergeht ZÄHLENWENN diesen Unterschied:

Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!

Ich brauche keinen Mittelfinger, ich kann das mit den Augen.

Heute in der Excel-Schulung. Ein Teilnehmer zeigt mir seine Lösung, wie er die Anzahl der Zahlen im 90er-Bereich ermittelt hat: Er berechnet die Differenz zweier ZÄHLENWENN-Funktionen:

=ZÄHLENWENN(C8:C37;“<100″)-ZÄHLENWENN(C8:C37;“<90″)

Ich zeige seinen Ansatz der Gruppe. Mit dem Funktionsassistenten rufe ich Zählenwenn auf:

Schritt 1

Schritt 1

Normalerweise trägt Excel im Funktionsassistenten um die Bedingung <100 automatisch die Anführungszeichen ein. Jedoch ein Klick in die Bearbeitungsleiste lehrt mich eines Besseren:

Schritt 2

Schritt 2

Ich fahre fort: tippe ein Minus und rufe erneut den Funktionsassistenten auf:

Schritt 3

Schritt 3

Der Blick in die Bearbeitungsleiste zeigt mir, dass die Anführungszeichen in der ersten Funktion fehlen. Und richtig: Das Bestätigen der Funktion wird mit einer Fehlermeldung quittiert:

Schritt 4

Schritt 4

Schade – denn gerade der Funktionsassistent nimmt Anwendern und Anwenderinnen, die mit Formeln noch nicht so sehr geübt sind, die Arbeit an vielen Stellen ab …

Also doch: Ich zeige dem Teilnehmer ZÄHLENWENNS, die ihm sehr gut gefällt …

Gestern bei offenem Fenster geschlafen. 10.569 Mücken gefällt das.

Hallo Herr Martin,
ich habe eine Liste, bei der ich Auswertungen erstellen soll. Es geht dabei um eine Gewichtung. Folgender Schlüssel liegt der Tabelle zugrunde:

20160912gewichtung01

Ich erstelle eine Formel:

=ZÄHLENWENN(C5:C70;“*x*“)*1+ZÄHLENWENN(C5:C70;“*P*“)*2+ZÄHLENWENN(C5:C70;“*EG*“)*3+
ZÄHLENWENN(C5:C70;“*En*“)*4+ZÄHLENWENN(C5:C70;“*Ew*“)*5+ZÄHLENWENN(C5:C70;“*Ek*“)*2+
ZÄHLENWENN(C5:C70;“*Em*“)*3+ZÄHLENWENN(C5:C70;“*B*“)*4+ZÄHLENWENN(C5:C70;“*A*“)*4+
N(„ZÄHLENWENN(C5:C70;““*Aw*““)*4″)+ZÄHLENWENN(C5:C70;“*K*“)*1+ZÄHLENWENN(C5:C70;“*V*“)*1+ZÄHLENWENN(C5:C70;“*U*“)*1

Vielleicht nicht clever – aber nachvollziehbar. Der Wert „Aw“ wurde auskommentiert – deshalb die Funktion „N“. Allerdings – an einer Stelle rechnet er nicht richtig – ich finde den Fehler nicht:

20160912gewichtung02

Die Antwort: Man muss schon genau hinschauen. Sie haben in die Matrix weitere Informationen eingetragen. Bei Frau Weiß finden sich die beiden Texte SPX. Dort wird das „x“ natürlich auch mitgezählt. Diese Texte dürfen Sie nicht in die Tabelle schreiben.

20160912gewichtung03

Vier von drei Leuten können nicht rechnen.

Heute in der Excel-Schulung. Eine Teilnehmerin zeigte mit eine Datei. Mit fiel auf, dass der Filter (hier: Black-Mitglieder) eine andere Zahl lieferte als die Funktion ZÄHLENWENN. Eine Pivottabelle lieferte das gleiche Ergebnis wie der Filter. Die Ergebnisse von ZÄHLENWENN waren um 1 zu groß.

Kann Excel nicht bis drei zählen?

Kann Excel nicht bis drei zählen?

Ich gestehe – ich habe eine Weile gesucht. Bis ich entdeckt hatte, dass über der Tabelle einige Zellen ausgeblendet waren. Und: richtig – dort stand die Liste, die als Bereich für die Datenüberprüfung verwendet wurde. Da ZÄHLENWENN die ganze Spalte zählte … Tja – halt einer zu viel.

Und hier wurde der Übeltäter versteckt.

Und hier wurde der Übeltäter versteckt.

Neugier ist die erste Stufe zur Hölle.

Ich will das auch sehen. Wenn mein Kollege die Funktion ZÄHLENWENN verwendet, sieht er bereits im Dialog das Ergebnis:

Das Ergebnis der Formel wird angezeigt.

Das Ergebnis der Formel wird angezeigt.

Bei mir jedoch nicht:

Hier nicht!

Hier nicht!

Erst wenn ich den Assistenten mit OK bestätige, steht das richtige Ergebnis in der Zelle. Warum bei mir nicht?

Die Antwort: Ihr Kollege sucht den Ort, indem er dort bereits die Anführungszeichen setzt. Sie haben keine Anführungszeichen eingegeben – de Text – hier Ulm – wird noch nicht als Text erkannt. Erst beim Bestätigen interpretiert der Funktionsassistent die drei Buchstaben Ulm als Text und ermittelt dann das richtige Ergebnis (und schreibt die Anführungszeichen in die Formel).

Ohne einen einzigen Feiertag

Warum klappt das nicht? Ich habe versucht – genau wie im Internet erklärt – einen Kalender zu erstellen. Ich habe die Feiertage berechnet und versuche auf meinem Kalender die Feiertage mit der Funktion ZAEHLENWENN anzeigen zu lassen. Aber er tut es nicht. Nicht einen einzigen Feiertag!

Die bedingte Formatierung funktioniert nicht.

Die bedingte Formatierung funktioniert nicht.

Die Antwort: Sie haben statt ZÄHLENWENN ZAEHLENWENN geschrieben. Erstaunlicherweise übergeht die bedingte Formatierung Tippfehler in den Funktionsnamen und – liefert gar nichts!

Mit ZÄHLENWENN funktioniert es!

Mit ZÄHLENWENN funktioniert es!

 

Zählenwenn rechnet nicht

Warum rechnet die Funktion ZÄHLENWENN (übrigens auch SUMMEWENN) manchmal nicht?

ZÄHLENWENN rechnet nicht.

ZÄHLENWENN rechnet nicht.

Schauen Sie die Funktion genau an. ZÄHLENWENN (und auch SUMMEWENN) verlangt als Reihenfolge zuerst WO wird etwas gesucht und anschließend WAS wird gesucht. Wenn Sie die Reihenfolge vertauschen, zählt er, wie oft der Bereich in der einen Zelle vorkommt und liefert kein Ergebnis.

Die Reihenfolge ist hier falsch.

Die Reihenfolge ist hier falsch.

ZÄHLENWENN zählt zu wenig

Warum ermittelt die Funktion ZÄhLENWENN (und SUMMEWENN) nicht die richtige Zahl?

Zähelnwenn zählt falsch ?!

Zählenwenn zählt falsch ?!

Man muss sich die einzelnen Daten ansehen. In der Bearbeitungsleiste stellt man fest, dass in einigen der Texte noch weitere Informationen („München“) stehen (sie wurden übrigens weiß formatiert). Man hätte auch durch ein Ändern der Ausrichtung auf rechtsbündig feststellen können, dass hinter dem angezeigten Text noch etwas steht.

Die Bearbeitungsleiste liefert das Ergebnis

Die Bearbeitungsleiste liefert das Ergebnis

Oder man formatiert die Zellen rechtsbündig.

Oder man formatiert die Zellen rechtsbündig.

Übrigens: =ZÄHLENWENN(D2:D10;“Rene Martin*“) hätte die korrekte Anzahl ermittelt.