Excel: geavanceerd tellen met AANTAL(LEN).ALS

[iAmRenzo]

Ik moet een aantal waarden tellen die binnen een bepaald bereik liggen.

Ik heb een kolom met dagen van de week (opgehaald met =TEKST(B241;"dddd") van een datumveld) en ik heb tijdstippen (notering: 18:05).
Ik heb een tabel waarin ik moet tellen hoeveel items er zijn op maandag tussen 9:00 en 10:00, en dat voor meerdere dagen/tijdstippen.

Ik dacht dat ik met

=AANTALLEN.ALS(Sheet0!D:D;"=>" & Q19;Sheet0!D:D;"<" & R19)

waarin Q19 gevuld is met "9:00" en R19 met "10:00". Maar ik krijg maar geen waarden. Laat staan dat ik de kolom met de dagen er bij kan betrekken...

=AANTALLEN.ALS(Sheet0!D:D;"=>9:00";Sheet0!D:D;"<10:00")

levert ook 0

Kan iemand mij vertellen waarom de tijdnotaties geen gegevens teruggeven. En hoe ik vervolgens nog de dag van de week er bij betrekt?

Ik heb ook gerommelt met

=AANTAL.ALS(Sheet0!D:D;"=>9:00")-AANTAL.ALS(Sheet0!D:D;">10:00")

maar daar krijg ik een vreemde -504 waarde uit die niet kan kloppen. Ook hie rkrijg ik de maandag dan niet in verwerkt.

PS: Ik heb nu overigens een originele datum kolom (datum + tijd). Eigenlijk zou alles daarmee moeten gebeuren.
Ik heb nu twee extra kolommen gemaakt: 1 met de dag en 1 met de tijd (zoals ik boven zei). Als het antwoord op mijn vraag zonder die twee aangemaakte kolommen kan dan scheelt dat nog meer werk!

 

[popipipo]

plaats even een klein xls bestandje dan wordt je wat gemakkelijker geholpen.

 

[iAmRenzo]

Hier staat een sheet. Ik heb er wat rigoreus in geknipt e.d. ivm gevoelige gegevens.

Ik heb de eerste sheet (waar de gegevens onbewerkt "binnenkomen"). De tabellen in de tweede sheet moeten worden gevuld. Ik heb de in de eerste post genoemde kolommen hier niet meer, alles moet dus uit het datum/tijd (kolom B) komen.

De cellen P37 moeten gevuld worden met aantallen op basis van dag, "Maandag" werkt hier dus niet maar =TEKST(B241;"dddd") in de formule ook niet.
De cellen J19:N28 moeten gevuld worden met aantallen op basis van dag en uurbereik.
De cellen J36:J40 en allen er omheen moeten gevuld worden op basis van de datum.

Dank

 

[Cobbe]

Ik zou deze eens proberen:

=SOMPRODUCT((WEEKDAG(Sheet0!$B$2:$B$750;2)=KOLOM()-9)*(TIJDWAARDE(UUR(Sheet0!$B$2:$B$750)&":"&MINUUT(Sheet0!$B$2:$B$750))>=$A19)*(TIJDWAARDE(UUR(Sheet0!$B$2:$B$750)&":"&MINUUT(Sheet0!$B$2:$B$750))<=$B19))

 

[iAmRenzo]

Dat ziet er inderdaad goed uit!
Alleen kom ik hier niet uitt: ik heb Sheet0!$B$2:$B$750 proberen te wijzigen in $B:$B of in $B2:$B20000 om een groter bereik aan te kunnen. Ik moet de sheet met formules elke maand in de datasheet plakken en de hoeveelheid data is altijd onbekend en aan verandering onderhevig. Ik wil het liefst maandelijks zo weinig mogelijk aanpassen.

Als iemand ook nog een tip heeft om de weekdagen (rechtsboven in de sheet) en datums (onderin de sheet) te vullen want op de een of andere manier lukt het me niet om van zo'n algemene datum de informatie te vergelijken.

 

[Cobbe]

Gebruik hem dan zo:

=SOMPRODUCT((WEEKDAG(datum_tijd;2)=KOLOM()-9)*(TIJDWAARDE(UUR(datum_tijd)&":"&MINUUT(datum_tijd))>=$A19)*(TIJDWAARDE(UUR(datum_tijd)&":"&MINUUT(datum_tijd))<=$B19))

Naambepaling:

=INDIRECT("Sheet0!$B$2:$B$" & AANTALARG(Sheet0!$B:$B))

 

[iAmRenzo]

Daar gaat het te ver boven mijn pet: dit volg ik niet

 

[Cobbe]

Heb die weekdagen EN de eerste week van december voor u gedaan, omdat ik in een goede bui ben.

 

[Cobbe]

Daar gaat het te ver boven mijn pet: dit volg ik niet

Er staat een heel weekend an te komen, tijd genoeg om dat in u op te nemen.
Veel lezen en googlen helpt u zeker op weg.

 

[iAmRenzo]

Ik waardeer je hulp. En ik ben al veel aan het lezen! Al een tijdje ook, haha. Er lijkt steeds meer bij te komen en steeds meer te kunnen.

Er zitten gek genoeg nog wat foutjes in, en ik krijg niet helder hoe of wat.
=SOMPRODUCT((WEEKDAG(datum_tijd;2)=KOLOM()-3)*(TIJDWAARDE(UUR(datum_tijd)&":"&MINUUT(datum_tijd))>=$A23)*(TIJDWAARDE(UUR(datum_tijd)&":"&MINUUT(datum_tijd))<=$B23))
Als ik deze formule uiteen haal dan wordt er dus geteld in een kolom het aantal regels waarbij de tijd groter/gelijk is dan A23 en kleiner dan B23. Met WEEKDAG trek je de dag eruit.
Niet duidelijk is waar je die mee vergelijkt... en niet duidelijk is wat KOLOM()-3 (in jouw voorbeeld -9, maar ik heb een aantal loze kolommen verwijderd) precies doen.

Ik heb de tijden van A23 en B23 veranderd van 9:00:00 - 10:00:00 naar 9:00:00 - 9:59:59 omdat 10:00:00 niet tweemaal wordt geteld.

Ik heb nu ook bij horizontaal zaterdag en verticaal 0:00:00 - 9:00:00 een totaal van 136 (dat klopt niet).

Nog vreemder is als de totalen van die tabel worden opgeteld dan krijg ik niet het totaal telefoontjes dat eerder is berekend!

 

[Cobbe]

Die KOLOM() levert het kolomnummer, dus als je in C1 =KOLOM() zet krijg je 3, maar je moet 1 hebben, dat wordt dan KOLOM()-2.
Je kan die KOLOM()-9(-3) ook vervangen door 1,2,3,4,5,6,7 voor de dagen van de week.
Wat die tijden en verschillen betreft kan je dat beter in je bestand aangeven zodat ik zie wat er aan de hand is.

 

[iAmRenzo]

Ok.
Dat lijkt helder.
Maar ik begrijp niet hoe excel dan de data uit het ene worksheet haalt (omdat ik geen verwijzing zie, zoals Sheel0!B:B)

 

[Cobbe]

Datum_tijd verwijst naar de range Sheet0!B2:B-laatste gebruikte rij:
Deze formule benoemt je naambereik (had ik al aangegeven in post #6

=INDIRECT("Sheet0!$B$2:$B$" & AANTALARG(Sheet0!$B:$B))

 

[iAmRenzo]

Met die formules ben ik niet bekend, ik kan ze niet vinden en als ik de sheet verplaats (andere map) werkt het niet meer.
Ik ga terug naar een simpelere oplossing
Maar in ieder geval bedankt voor de inzit!

 

[Cobbe]

Je moet ook een beetje moeite doen, in de tab Formules staat een item Namen beheren,
kies deze eens en zie in je schermpje de namen met hun verwijzingen.
Het zijn die namen die in de formules worden gebruikt.

Dan begrijp je ook hoe de formules in elkaar zitten.