Zählenwenns mit Zeitwert

%3CLINGO-SUB%20id%3D%22lingo-sub-2484464%22%20slang%3D%22de-DE%22%3ECountingifs%20with%20time%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2484464%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%20everyone%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20count%20the%20cells%20that%20take%20place%20on%20a%20certain%20day%20of%20the%20week%20in%20the%20time%20from%20to.%3C%2FP%3E%3CP%3Ee.B%20Monday%20between%2000%3A00%20and%2000%3A59%20or%20Wednesday%20between%2014%3A00%20and%2014%3A59%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20a%20further%20step%2C%20I%20have%20to%20supplement%20the%20table%20with%20a%20specific%20sector.%3C%2FP%3E%3CP%3Ee.B%20Monday%20between%2000%3A00%20and%2000%3A59%20in%20the%20Eastern%20sector%20or%20Wednesday%20between%2014%3A00%20and%2014%3A59%20in%20the%20Middle%20Sector%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20with%20all%20information%20already%20exists%2C%20now%20I%20have%20to%20get%20this%20list%20on%20another%20sheet%20and%20at%20the%20end%20represent%20the%20whole%20thing%20in%20a%20diagram.%20The%20list%20is%20supplemented%20daily%20because%20data%20is%20constantly%20added.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20have%20to%20enter%20the%20correct%20formula%20and%20is%20counting%20the%20correct%20formula%20for%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20support.%3C%2FP%3E%3CP%3EGreetings%20Ursula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2484464%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2485002%22%20slang%3D%22en-US%22%3ERe%3A%20Z%C3%A4hlenwenns%20mit%20Zeitwert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2485002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087830%22%20target%3D%22_blank%22%3E%40Ursula76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDesirably%20to%20have%20small%20sample%20file.%20It%20depends%20on%20how%20your%20data%20is%20organized.%20Variants%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20408px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291248i056BDDEA2BF2D105%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%2C%20whatever.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2485053%22%20slang%3D%22de-DE%22%3ERe%3A%20Countingifs%20with%20time%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2485053%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20small%20excerpt%20from%20the%20table.%20Are%20other%20dates%2C%20but%20these%20are%20not%20important%20here.%3C%2FP%3E%3CTABLE%20width%3D%22525px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3Eday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3Edate%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3Eplace%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3EAlarm%20input%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3Ewest%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3Emiddle%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3Eeast%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EFriday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E01.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EZuchwil%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E15.02%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3ESunday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E03.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3ESolothurn%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E22.16%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EMonday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E04.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EBellach%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E6%3A04%20pm%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EMonday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E04.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EDintikon%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E14.46%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EMonday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E04.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EAarau%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E18.16%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3ETuesday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E05.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EWohlen%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E12%3A38%20a.m.%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3ETuesday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E05.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EOensingen%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E17.52%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EWednesday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E06.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EOberentfelden%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E20.52%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EWednesday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E06.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3ESolothurn%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E22.00%20h%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3EThursday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E07.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3EOlten%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E9%3A48%20pm%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2294px%22%3ESaturday%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E09.01.2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3ENiederbipp%3C%2FTD%3E%3CTD%20width%3D%22110px%22%3E12%3A06%20a.m.%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hallo zusammen

 

Ich möchte die Zellen zählen welche an einem bestimmten Wochentag in der Zeit von bis stattfinden.

z.B Montag zwischen 00:00 und 00:59 oder Mittwoch zwischen 14:00 und 14:59

 

In einem weiteren Schritt muss ich die Tabelle noch ergänzen mit einem bestimmten Sektor.

z.B. Montag zwischen 00:00 und 00:59 im Sektor Ost oder Mittwoch zwischen 14:00 und 14:59 im Sektor Mitte

 

Die Tabelle mit allen Angaben existiert bereits, jetzt muss ich auf einem anderen Blatt diese Aufstellung hinbekommen und am Ende das Ganze in einem Diagramm darstellen. Die Liste wird täglich ergänzt da laufend Daten dazu kommen.

 

Wie muss ich die korrekte Formel eingeben und ist Zählenwenns die korrekte Formel dafür?

 

Besten Dank für eure Unterstützung.

Gruss Ursula

3 Replies

@Ursula76 

Desirably to have small sample file. It depends on how your data is organized. Variants could be

image.png

, whatever.

@Sergei Baklan 

 

Hier ein kleiner Ausschnitt aus der Tabelle. Sind noch andere Daten, aber diese sind hier nicht von Bedeutung.

TagDatumOrtAlarmeingangWestMitteOst
Freitag01.01.2021Zuchwil15.02 hx  
Sonntag03.01.2021Solothurn22.16 hx  
Montag04.01.2021Bellach06.04 hx  
Montag04.01.2021Dintikon14.46 h  x
Montag04.01.2021Aarau18.16 h  x
Dienstag05.01.2021Wohlen00.38 h  x
Dienstag05.01.2021Oensingen17.52 h x 
Mittwoch06.01.2021Oberentfelden20.52 h  x
Mittwoch06.01.2021Solothurn22.00 hx  
Donnerstag07.01.2021Olten09.48 h x 
Samstag09.01.2021Niederbipp00.06 h x 

@Ursula76 

Thank you. It's not clear in which form you'd like to have the result and on which version of Excel you are. As variant that could be

image.png

If you open attached file formula will be shown in your locale.

Another variant could be PivotTable and PivotChart but again, depend on in which form shall be result.