# Workload change every 3 month base on number of recruit

#### KIM Chanthan

##### Member
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,

#### Attachments

• 10.8 KB Views: 6

#### bosco_yip

##### Excel Ninja
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))

#### Attachments

• 13.3 KB Views: 5

#### Excel Wizard

##### Member
=SUMPRODUCT(\$B3:B3*LOOKUP(1+COLUMN()-COLUMN(\$B3:B3),LEFT(\$B\$9:\$B\$12,2)-LEFT(\$B\$9)+1,\$C\$9:\$C\$12))

#### Attachments

• 12.1 KB Views: 5
Last edited:

#### KIM Chanthan

##### Member
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