zählenwenn() mit 2 Oder-Kriterien + 1 Und

[horst]

Hallo,

ich bitte um Hilfe bei einer Formel, die ich nicht zusammenbekomme:

Formel soll in G2

Durchsuch-Bereich H5:H35 und ist Standard/Textspalte

Suchkriterium: "Tag" oder "tag" (aus z. B. Feiertag, Besonderer Tag, oder Wortteil tag)
bislang stocke ich.

ich habe mir 2 Hilfsspalten gemacht in I und J (die dann überflüsig werden sein sollen)
Formel in I:
'=ZÄHLENWENN($J$5:$J$35;ODER(TEIL(RECHTS(H5;3);1;3)="tag";TEIL(RECHTS(H5;3);1;3)="Tag"))
Formel in J:
'=ODER(TEIL(RECHTS(H5;3);1;3)="tag";TEIL(RECHTS(H5;3);1;3)="Tag")

Meine Versuche misslingen; probierte es auch mit Wahr und Finden - erziele aber nicht das gewünschte Ergebnis, welches sein soll, dass eine Summe in G2 der Zellen (Bereich H5:H35) ausgegeben werden soll, wo in H beliebige "tag"-Formen gezählt werden sollen. Pro Zelle ist jedoch nur 1 Eintrag. Hintergrund: Urlaubstage, freie Tage, Ostertage etc. als Bemerkung soll gezählt werden. Als Bedingung sollte noch sein, dass nur gezählt wird, wenn in der jeweiligen Zeilenzelle E ein Wert > 0 ist.

Wer kann mir bitte helfen? Ich doktere nun im Kreise.

Danke und Gruß
horst

[Jockel]

hi horst, meinst du so..?

=SUMMENPRODUKT(N(ISTZAHL(SUCHEN("tag";H5:H35))*1))

bzw..:

=SUMMENPRODUKT(N(ISTZAHL(SUCHEN("tag";H5:H35))*(E5:E35>0)))

[Peter]

Hallo,

suchst etwas das in diese Richtung geht?

Arbeitsblatt mit dem Namen 'Tabelle1'
	D	E	F	G	H
5	2	5			Feiertag
6		6			Besonderer Tag
7					Ostertag
8					Montag
9

Zelle	Formel
D5	=SUMMENPRODUKT((RECHTS(H5:H10;3)="Tag")*(E5:E10>0))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Wobei bei dieser Formel nur die letzten 3 Zeichen in Spalte H geprüft werden.

[Josef B]

Hallo Horst

Oder ab Excel 2007:

=ZÄHLENWENNS(H5:H35;"*Tag*";E5:E35;">"&0)

Gruss Sepp

[Peter]

Hallo,

oder, wiederum alternativ und als Ergänzung, wenn das auch unter XL2003 laufen soll und der 'Tag' irgendwo in der Zelle steht:

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E	F	G	H
5	2	2	5			Feiertag
6			6			Test
7						Ostertag
8						Test
9			10			Ein Tag Urlaub

Zelle	Formel
C5	=ZÄHLENWENNS(H5:H35;"*Tag*";E5:E35;">"&0)
D5	=SUMMENPRODUKT((ISTZAHL(SUCHEN("Tag";H5:H10))*(E5:E10>0)))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Jockel]

Hallo,

oder, wiederum alternativ und als Ergänzung, wenn das auch unter XL2003 laufen soll und der 'Tag' irgendwo in der Zelle steht:

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E	F	G	H
5	2	2	5			Feiertag
6			6			Test
7						Ostertag
8						Test
9			10			Ein Tag Urlaub

Zelle	Formel
C5	=ZÄHLENWENNS(H5:H35;"*Tag*";E5:E35;">"&0)
D5	=SUMMENPRODUKT((ISTZAHL(SUCHEN("Tag";H5:H10))*(E5:E10>0)))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

hi Peter, da sind wir ja ganz nah beieinander..: http://www.clever-excel-forum.de/showthread.php?tid=487&pid=3119#pid3119

[Peter]

Hallo,

kann das sein, dass dein erstes Posting ursprünglich etwas mit Zählenwenn war?

Ich dachte noch bei mir da hat der Jockel den letzten Satz überlesen

wenn in der jeweiligen Zeilenzelle E ein Wert > 0 ist.

Und als Josef mit Zählenwenns() gekommen ist habe ich mir dann gedacht, dass ich das noch ergänze um eine Variante bei der das Tag überall stehen kann - ohne das ich mir die vorherigen Beiträge nochmals angesehen habe - sonst hätte ich mir das gespart.

[horst]

Hallo Peter,

hier die Datei, die ich aufbaue. Hätte ich wohl gleich hochladen sollen. In G2 soll die Tagesanzahl ausgegeben werden. Und es ist richtig, dass die zu zählenden Tage mit Tag/tag enden.

Schau einmal.

Danke und Gruß
horst

[Peter]

Hallo,

dann ändere die Formel mal so ab:

=SUMMENPRODUKT((ISTZAHL(SUCHEN("Tag";H5:H35))*(E5:E35<>"")))

bzw

=SUMMENPRODUKT((RECHTS(H5:H35;3)="Tag")*(E5:E35<>""))

[Jockel]

Hallo Peter,

hier die Datei, die ich aufbaue. Hätte ich wohl gleich hochladen sollen. In G2 soll die Tagesanzahl ausgegeben werden. Und es ist richtig, dass die zu zählenden Tage mit Tag/tag enden.

Schau einmal.

Danke und Gruß
horst

hi host, die formeln funzen ja... ... aber 8ung! KALENDERWOCHE() rechnet für deutschland erst ab XL2010 richtig..:

=KALENDERWOCHE(A5;21)