SOLVED

Zählenwenn mit Datumseinschränkung

%3CLINGO-SUB%20id%3D%22lingo-sub-2314893%22%20slang%3D%22de-DE%22%3ECountingif%20with%20date%20restriction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314893%22%20slang%3D%22de-DE%22%3E%3CTABLE%20width%3D%22402%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3EBs%3FA**%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E01.01.2021%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E07.02.2021%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E15.03.2021%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E21.04.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EBS1A19%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EBS1A19%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EBS1A19%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3EBS1A19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%3CSTRONG%3EBS1A19%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%3CSTRONG%3EBS1A19%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%3CSTRONG%3EBS1A19%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3Estart%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E01.02.2021%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264px%22%20height%3D%2230px%22%3Eend%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E30.04.2021%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%3C%2FP%3E%3CP%3Ewith%20the%20(wrong)%20formula%20in%20cell%20A3%20%3DCOUNT%20(B3%3AE3%3B%20A1)%20I%20get%20%224%22%20as%20a%20result.%3C%2FP%3E%3CP%3EHowever%2C%20I%20only%20want%20to%20include%20cells%20in%20a%20defined%20date%20range%20(B5%20B6).%3C%2FP%3E%3CP%3EExpected%20result%20cell%20A3%3A%20%222%22%20%3CBR%20%2F%3E%20The%20formula%20should%20unfortunately%20be%20compatible%20with%20Excel%202013.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20someone%20could%20help%20me.%20Thank%20you%20very%20much!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2314893%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314913%22%20slang%3D%22en-US%22%3ERe%3A%20Z%C3%A4hlenwenn%20mit%20Datumseinschr%C3%A4nkung%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314913%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(B2%3AE2%2C%24A%241)%0A%0A%3DZ%C3%84HLENWENN(B2%3AE2%3B%24A%241)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314948%22%20slang%3D%22en-US%22%3ERe%3A%20Z%C3%A4hlenwenn%20mit%20Datumseinschr%C3%A4nkung%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVersuche%20in%20A2%3A%3C%2FP%3E%0A%3CP%3E%3DSUMMENPRODUKT(ISTZAHL(SUCHEN(%24A%241%3BB2%3AE2))*(%24B%241%3A%24E%241%26gt%3B%3D%24B%245)*(%24B%241%3A%24E%241%26lt%3B%3D%24B%246))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315134%22%20slang%3D%22de-DE%22%3ERe%3A%20Counting%20if%20with%20date%20restriction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315134%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Mr.%20Vogelaar%2C%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20support%2C%20even%20in%20this%20particular%20case!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM%20PRODUCT(ISNUMBER(SEARCH(Table1%5B%5B%23Kopfzeilen%5D%3B%5B%20Bs%3FA**%5D%5D%3B%20Table1%5B%5B01.01.2021%5D%3A%5B30.04.2021%5D%5D))*(Table1%5B%5B%23Kopfzeilen%5D%3B%5B%2001.01.2021%5D%3A%5B30.04.2021%5D%5D%26gt%3B%3D%24B-7)*(Table1%5B%5B%23Kopfzeilen%5D%3B%5B%2001.01.2021%5D%3A%5B30.04.2021%5D%5D%26lt%3B%3D%24B%248))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20convert%20the%20range%20to%20a%20table%20and%20adjust%20the%20formula%2C%20I%20get%20%220%22%20as%20a%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApparently%20Excel%20does%20not%20recognize%20dates%20in%20headers%20as%20a%20date...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315169%22%20slang%3D%22en-US%22%3ERe%3A%20Z%C3%A4hlenwenn%20mit%20Datumseinschr%C3%A4nkung%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKopfzeilen%20in%20Excel-Tabellen%20sind%20immer%20Text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeiner%20Ansicht%20nach%20sollte%20man%20Excel-Tabellen%20nicht%20f%C3%BCr%20Kreuztabellen-Strukturen%20verwenden.%20Daf%C3%BCr%20sind%20sie%20nicht%20geeignet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2315241%22%20slang%3D%22de-DE%22%3ERe%3A%20Counting%20if%20with%20date%20restriction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315241%22%20slang%3D%22de-DE%22%3EHello%20Mr%20Lewin%2C%20thank%20you%20very%20much%20for%20your%20reply.%20Is%20there%20a%20workaround%3F%3C%2FLINGO-BODY%3E
Occasional Contributor
BS?A**01.01.202107.02.202115.03.202121.04.2021
3BS1A19BS1A19BS1A19BS1A19
2BS1A19 BS1A19BS1A19
     
START  01.02.2021   
ENDE30.04.2021   

 

Hallo,

mit der (falschen) Formel in Zelle A3 =ZÄHLENWENN(B3:E3;A1) erhalte ich als Ergebnis "4".

Ich möchte jedoch nur Zellen in einem definierten Datumsbereich berücksichtigen (B5 B6).

Erwartetes Ergebnis Zelle A3: "2"
Die Formel müsste leider mit Excel 2013 kompatibel sein.

 

Vielleicht könnte mir jemand helfen. Vielen Dank!!!

9 Replies

@learner1234 Try this:

=COUNTIF(B2:E2,$A$1)

=ZÄHLENWENN(B2:E2;$A$1)

 

best response confirmed by learner1234 (Occasional Contributor)
Solution

@learner1234 

Versuche in A2:

=SUMMENPRODUKT(ISTZAHL(SUCHEN($A$1;B2:E2))*($B$1:$E$1>=$B$5)*($B$1:$E$1<=$B$6))

@Hans Vogelaar 

Hallo Herr Vogelaar,

vielen Dank für Ihre Unterstützung, auch in diesem besonderen Fall!!!

 

=SUMMENPRODUKT(ISTZAHL(SUCHEN(Tabelle1[[#Kopfzeilen];[BS?A**]];Tabelle1[@[01.01.2021]:[30.04.2021]]))*(Tabelle1[[#Kopfzeilen];[01.01.2021]:[30.04.2021]]>=$B$7)*(Tabelle1[[#Kopfzeilen];[01.01.2021]:[30.04.2021]]<=$B$8))

 

Wenn ich den Bereich in eine Tabelle konvertiere und die Formel anpasse, erhalte ich als Ergebnis "0".

 

Anscheinend erkennt Excel Datumsangaben in Kopfzeilen nicht als Datum...

@learner1234 

Kopfzeilen in Excel-Tabellen sind immer Text.

 

Meiner Ansicht nach sollte man Excel-Tabellen nicht für Kreuztabellen-Strukturen verwenden. Dafür sind sie nicht geeignet.

 

Hallo Herr Lewin, vielen Dank für Ihre Antwort. Gibt es einen Workaround?
Hallo Herr Vogelaar, die Datumsangaben befinden sich leider in der Kopfzeile einer Tabelle. Gibt es hierfür einen Workaround, um diesen Text als Datum zu interpretieren?

@learner1234 

Versuchen sie mal

 

=SUMMENPRODUKT(ISTZAHL(SUCHEN($A$1;B2:E2))*(DATWERT($B$1:$E$1)>=$B$5)*(DATWERT($B$1:$E$1)<=$B$6))

@learner1234 Ich habe eine Hilfszeile mit den Datumsangaben oberhalb von der Kopfzeile der Tabelle eingefügt, damit funktioniert es nun.

@Hans Vogelaar Super, es funktioniert mit DATWERT