Excel-Formel LINKS in Zählenwenn einbinden, wie?

Hallo zusammen, Ich kann die Excel-Formel LINKS nicht in die Zählenwenn Formel einbetten, was mache ich falsch?

Ausgangslage:

Spalte A

• Hans
• Franz
• Marie

Spalte B

• 01.07.1987
• 02.02.1988
• 01.04.1990

Wenn ich nun zählen möchte wie viele Personen am 01. eines Monats Geburtstag haben, würde ich folgende Formel einsetzen:

=ZÄHLENWENN(LINKS(B:B;2);"01")

Warum funktioniert diese Formel nicht, was mache ich falsch? Wichtig ist, dass ich keine hilfszellen erstellen darf, die Anzahl soll mit einer Formel ermittelt werden können.

Vielen Dank für eure Hilfe! Gruss Reti

Answer 1

[Tron1701]

Hallo,

ich würde mit einer Zusatzspalte arbeiten.

Angenommen Deine Daten würden ab der 3. Zeile beginnen (Also A3 und B3).

Spalte C folgende Formel: =TAG(B3), diese Formel nach unten kopieren soweit Daten vorhanden. Die Spalte kann jetzt ausgeblendet werden.

Jetzt eine Zelle für das Suchkriterium bestimmen (in meinen Beispiel wäre es die Zelle D1). Somit hast Du eine Variable nach dem man Suchen soll.

Jetzt die eigentliche Formel, als Beispiel in Zelle E1: =ZÄHLENWENN(C:C;D1)

Vorteil darin ist, dass lediglich die Zahl in D1 (mit oder ohne "0", der "." ist nicht erforderlich) eingegeben wird. Möchtest Du, dass in D1 bei der Eingabe die Vor-Null erscheint, dann Text-Format wählen.

Gruß Tron

Answer 2

[PWolff]

ZÄHLENWENN ist für den Vergleich von Zellbereichen mit Texten konzipiert worden und der Vergleich mit Zahlen kommt mir ziemlich wie ein Notbehelf vor.

Außerdem fordert ZÄHLENWENN als erstes Argument einen Zellbereich und weigert sich, etwas anderes entgegenzunehmen. (Nicht einmal als Matrixformel kann man eine Funktion auf die Zellen anwenden.)

Drittens vergleicht ZÄHLENWENN den unformatierten Inhalt der Zelle - und unterscheidet dabei nach den Datentypen numerisch, Text und Wahrheitswert. Daten werden intern als Zahl gespeichert, unabhängig davon, wie sie in der Zelle erscheinen.

Das Format TT.MM.JJJJ ist eine reine Formatierung, also nur etwas, das die Anzeige des Wertes betrifft. Wenn man in Excel einen Wert eingibt, den Excel als Datum interpretieren kann, wandelt es den Wert in eine Zahl um und stellt das Format der Zelle auf Datumformat.

Was funktioniert, ist eine "Matrixformel":

{=SUMMEWENN(TAG(B:B)=1;1;0))}

Die Formel ohne die geschweiften Klammern eingeben, dann die Eingabe nicht mit Zeilenschaltung/Enter übernehmen sondern mit Strg+Umschalt+Zeilenschaltung/Enter - dann übernimmt Excel die Formel als Matrixformel und kennzeichnet das durch die geschweiften Klammern.

(Und ja, es muss eine Matrixformel sein, sonst interpretiert Excel z. B. in Zelle C4 das B:B in obiger Formel als B4.)

Comments

[Iamiam]

ich dachte schon, das sei eine mir unbekannte Syntax, aber das krieg ich nicht zum Laufen. Hast Du das ausprobiert?

[PWolff]

@Iamiam

Sorry. Hab beim Abschreiben eine Klammer vergessen. SUMMEWENN gibt's ja auch.

{=SUMME(WENN(TAG(B:B)=1;1;0))}

Das hab ich aber ausprobiert. Excel 2010.

[Iamiam]

@PWolff

ja, so gehts! danke.

@Frager: wobei ich den Bereich stark eingrenzen würde, denn da könnten ja auch andere Monate drin sein mit demselben Tag.

Tag() selbst scheint mir ungeeignet!

[PWolff]

@Iamiam

Der Tag des Monats ist ja gerade das, was Reti90 in der Frage erwähnt hat.

Eine Erweiterung ist leicht möglich, z. B. für den 1. April:

{=SUMME(WENN(UND(TAG(B:B)=1;MONAT(B:B)=4);1;0))}

(wenn ich mich nicht wieder bei den Klammern vertan habe)

[Iamiam]

@PWolff

stimmt, ich lese viel zu ungenau!

[PWolff]

@Iamiam

Passiert mir auch regelmäßig :-/

Answer 3

[Iamiam]

Excel hat den Umgang mit Datümern wesentlich vereinfacht:

=ZÄHLENWENN(A1:A50;"2.3.17")

funktioniert inzwischen, aber auch

=SUMMENPRODUKT((A1:A50="2.3.17"*1)*1)

Letztere ist erweiterungsfähig, wenn Uhrzeiten dabeistehen, aber der Tag gemeint ist: (Das Datum in "" muss in Summenprodukt  *1 oder +0 genommen werden, damit es zur Zahl umgewandelt wird, da verhalten sich verschiedene Funktionen unterschiedlich, schadet aber nie)

=SUMMENPRODUKT((GANZZAHL(A1:A50)="16.1.17"*1)*1)

Comments

[Iamiam]

im übrigen ginge auch

=SUMMENPRODUKT((LINKS(TEXT(A1:A50;"TT.M.JJ");2)="01")*1)

aber nur nach dem Motto: warum einfach, wenn's kompliziert auch geht?? ;)

[Iamiam]

@Iamiam

wie ich bei PWolff schon schrieb, erscheint mir Tag() ungeeignet, weil das auch für jeden anderen Monat zutreffen kann.

Also das volle Datum nehmen (Kurzschreibweise reicht ) und wenn es nur um eine Vorwarnung geht, dann auch anstatt "3.3.17"*1 auch(am 2.3.17) 

heute()+1

das ist morgen.(geht auch in Zählenwenn).

Nützt allerdings nichts, wenn Wochenende oder Feiertag dazwischen, +1 bleibt +1, auch wenn Du am Freitag die Vorwarnung für Montag wissen willst.

Ginge mit Summenprodukt auch, aber sehr viel umfangreicher, und da geh ich jetzt nicht auch noch drauf ein.

[Iamiam]

@Iamiam

Sorry, hab nich immer auf Adressen in A bezogen, die Deinigen stehen ja in B, also anpassen!

Answer 4

[Oubyi, UserMod Light]

SO ist die Formel ganz falsch aufgebaut.
SO wäre es sinnvoll, funktioniert aber auch nicht, da ZÄHLENWENN nicht so flexibel ist:
=ZÄHLENWENN(B1:B10;TAG(B1:B10)=1)
SO sollte es klappen:

=SUMMENPRODUKT((TAG(B1:B10)=1)*1)

Woher ich das weiß: Berufserfahrung – IT-Administrator (i.R.)