Generieke formule
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Samenvatting
Om weekdagen (maandag, vrijdag, zondag, enz.) Tussen twee datums te tellen, kunt u een matrixformule gebruiken die verschillende functies gebruikt: SOMPRODUCT, WEEKDAY, ROW en INDIRECT. In het getoonde voorbeeld is de formule in cel E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
In de generieke versie van de formule, start = startdatum, end = einddatum en dow = dag van de week.
Uitleg
In de kern gebruikt deze formule de functie WEEKDAY om een aantal datums te testen om te zien of ze op een bepaalde dag van de week terechtkomen (dow) en de functie SUMPRODUCT om het totaal bij te houden.
Wanneer een datum wordt gegeven, retourneert WEEKDAY eenvoudig een getal tussen 1 en 7 dat overeenkomt met een bepaalde dag van de week. Met standaardinstellingen, 1 = zondag en 7 = zaterdag. Dus 2 = maandag, 6 = vrijdag, enzovoort.
De truc van deze formule is te begrijpen dat datums in Excel slechts seriële getallen zijn die beginnen op 1 januari 1900. 1 januari 2016 is bijvoorbeeld het serienummer 42370 en 8 januari is 42377. Datums in Excel zien er alleen uit als datums waarop een datumnotatie wordt toegepast.
Dus de vraag wordt: hoe kun je een reeks datums construeren die je in de WEEKDAY-functie kunt invoeren om de overeenkomstige dagen van de week te achterhalen?
Het antwoord is om RIJ te gebruiken met INDIRECTE functies zoals:
ROW(INDIRECT(date1&":"&date2))
INDIRECT staat toe dat de aaneengeschakelde datums "42370: 42377" worden geïnterpreteerd als rijnummers. Vervolgens retourneert de functie RIJ een array als deze:
(42370;42371;42372;42373;42374;42375;42376;42377)
De WEEKDAY-functie evalueert deze getallen als datums en retourneert deze matrix:
(6;7;1;2;3;4;5;6)
die wordt getoetst aan de gegeven dag van de week (6 in dit geval vanaf D6). Zodra de resultaten van de test zijn geconverteerd naar enen en nullen met het dubbele koppelteken, wordt deze array verwerkt door SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Wat 2 terugkeert.
Met SEQUENCE
Met de nieuwe SEQUENCE-functie kan deze formule enigszins als volgt worden vereenvoudigd:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
In deze versie gebruiken we SEQUENCE om de reeks datums direct te genereren, zonder dat INDIRECT of ROW nodig is.
