Workload change every 3 month base on number of recruit

[KIM Chanthan]

Hi,

I want to designed workload for my staff. That workload will be change every 3 month starting from recruit date.
We just put number of recruitment in any month and workload will automatically calculate and change every 3 month.
Please refer to attached file for more detail.

Thanks,

 

[bosco_yip]

Try,

In B5, array (CSE) formula copied across right :

=SUM(IFERROR(LOOKUP(COUNT($B2:B2)-AGGREGATE(15,6,COLUMN($A1:$L1)/($B3:B3<>""),COLUMN(INDIRECT("C1:C"&COUNT($B3:$M3),)))+1,IFERROR(LEFT($B8:$B11)+1,0),$C9:$C12)*INDEX($B3:$M3,N(IF(1,AGGREGATE(15,6,COLUMN($A1:$L1)/($B3:$M3<>""),COLUMN(INDIRECT("C1:C"&COUNT($B3:$M3),)))))),0))

 

[Excel Wizard]

Please try at B5
=SUMPRODUCT($B3:B3*LOOKUP(1+COLUMN()-COLUMN($B3:B3),LEFT($B$9:$B$12,2)-LEFT($B$9)+1,$C$9:$C$12))

 

[KIM Chanthan]

Try,

In B5, array (CSE) formula copied across right :

=SUM(IFERROR(LOOKUP(COUNT($B2:B2)-AGGREGATE(15,6,COLUMN($A1:$L1)/($B3:B3<>""),COLUMN(INDIRECT("C1:C"&COUNT($B3:$M3),)))+1,IFERROR(LEFT($B8:$B11)+1,0),$C9:$C12)*INDEX($B3:$M3,N(IF(1,AGGREGATE(15,6,COLUMN($A1:$L1)/($B3:$M3<>""),COLUMN(INDIRECT("C1:C"&COUNT($B3:$M3),)))))),0))

View attachment 71012

It works magically as expected. Thanks

 

[KIM Chanthan]

Please try at B5
=SUMPRODUCT($B3:B3*LOOKUP(1+COLUMN()-COLUMN($B3:B3),LEFT($B$9:$B$12,2)-LEFT($B$9)+1,$C$9:$C$12))

Thanks for your great help. It solved my issue.