Uren per ultimo van de maand met een einddatum van deze uren

[hgm]

Hallo,

In een lijst hebben personen begin- en/of einddata met uren en wijzigingen daarop. Graag wil ik per ultimo van iedere maand peilen hoeveel uren deze personen dan hebben.

Zolang er geen einddatum is gevuld dan gaat het goed. Als de einddatum vóór de ultimo datum ligt dan zou de uitkomst 0 moeten zijn.
Wie kan mij hier verder mee helpen?

Zie bijlage met voorbeeld en een formule met index.

Groetjes,
Hanni

 

[HSV]

Komt onderstaande in de buurt?

=SOMPRODUCT(($A$2:$A$11=$H4)*($C$2:$C$11<=J$3)*($D$2:$D$11>=J$3)*($E$2:$E$11))

 

[MarcelBeug]

Mijn voorstel zou zijn deze matrixformule in J4, bevestigd met Ctrl+Shift+Enter en gekopieerd naar rechts en naar beneden t/m U5:

=ALS.FOUT(INDEX($E$2:$E$1000;VERGELIJKEN(J$3;ALS($A$2:$A$1000=$H4;ALS((($D$2:$D$1000>J$3)+($D$2:$D$1000=""));$C$2:$C$1000))));"")

@HSV: close but no cigar :d Bij een lege einddatum gaat het niet goed.

 

[HSV]

Ik heb geen idee Marcel, maar als ik jouw formule gebruik en ik maak cel D10 leeg heb jij een uitkomst in Q5 van 8.
Terwijl in D9 "31-08-2015" staat waar een waarde in E9 van 16 tegenover staat, die mijn formule genereert.

 

[MarcelBeug]

Ik heb geen idee Marcel, maar als ik jouw formule gebruik en ik maak cel D10 leeg heb jij een uitkomst in Q5 van 8.
Terwijl in D9 "31-08-2015" staat waar een waarde in E9 van 16 tegenover staat, die mijn formule genereert.

Die 31-8 is een geval apart waar je over van mening kunt verschillen, omdat dit zowel als begin- als als einddatum voorkomt.
Ik doelde meer op de originele gegevens waarbij de SOMPRODUCT bij J. Peters nullen oplevert voor okt-dec i.p.v. 32.

 

[sylvester-ponte]

=somproduct(($a$2:$a$11=$h4)*($c$2:$c$11<=j$3)*($d$2:$d$11>=j$3)*($e$2:$e$11)*($d$2:$d$11<>""))

 

[MarcelBeug]

Er is hier sprake van een OF situatie (einddatum groter(/gelijk) datum OF einddatum niet ingevuld) en dat vereist een optelling in de formule.

Met SOMPRODUCT is dat alleen mogelijk als beide voorwaarden elkaar uitsluiten (zoals hier), anders kun je op 2 uitkomen en dan klopt de uitkomst niet meer.

Zo kom ik uit op de volgende SOMPRODUCT formule (is als gewone formule inderdaad te verkiezen boven mijn eerdere matrixformule):

=SOMPRODUCT(($A$2:$A$11=$H4)*($C$2:$C$11<=J$3)*(($D$2:$D$11>J$3)+($D$2:$D$11=""))*($E$2:$E$11))

(bijvoorbeeld begindatum 31-8-2015 is bepalend voor Q5 --> 8)
dan wel

=SOMPRODUCT(($A$2:$A$11=$H4)*($C$2:$C$11<J$3)*(($D$2:$D$11>=J$3)+($D$2:$D$11=""))*($E$2:$E$11))

(bijvoorbeeld einddatum 31-8-2015 is bepalend voor Q5 --> 16)

 

[hgm]

Dit is de juiste oplossing Marcel. Dank je wel hiervoor.
Groetjes Hanni

 

[MarcelBeug]

Graag gedaan, Hanni.