Sumproduct

[NARAYANK991]

Hi ,

See the attached file , for a formula which does not make use of any helper columns.

Verify the results thoroughly.

Narayan

 

[Jeepcraz]

Hi ,

See the attached file , for a formula which does not make use of any helper columns.

Verify the results thoroughly.

Narayan

Noted. Thanks for providing another solution.

How do i use this formula on N4 when there's a public holiday?

D 12hrs on public holiday is equivalent to Day 12hrs on Weekdend (M4) instead of Day 12hrs on weekday (L4). In this case 18/10 is a holiday, so L4 should be 1 and N4 is 1. The final result will be displayed on C4 and F4

 

[NARAYANK991]

Hi ,

Is there any other requirement that has not been mentioned so far ?

if so , please post a complete list of all requirements.

Giving attention to a problem over days is more tiring than spending a whole day on it.

Narayan

 

[Jeepcraz]

So sorry about it. That’s about it. The rest I will just stick to mine

 

[NARAYANK991]

Hi ,

How exactly does a Public Holiday function in your setup :

a) When it falls on a weekday

b) When it falls on a weekend

Since a public holiday can fall on any day , I am not sure that it can be incorporated in the formula which does not use the helper columns in the range DC7:GQ69.

Are you OK with using the helper columns ?

Narayan

 

[Jeepcraz]

One thing is that I want to mention is I have created a macro
My original format, C7 is D accompanying with number or alphabet (eg D1 or DA). This is for job area allocation. So the macro was used to convert them to just a D. But it takes time to do this process. Is there a way to eliminate this process ?

 

[Jeepcraz]

Hi ,

How exactly does a Public Holiday function in your setup :

a) When it falls on a weekday

b) When it falls on a weekend

Both falls under weekend afternoon shift allowance

Since a public holiday can fall on any day , I am not sure that it can be incorporated in the formula which does not use the helper columns in the range DC7:GQ69.

Are you OK with using the helper columns ?

Yes. As long as I can get the desired result

Narayan

 

[NARAYANK991]

Hi ,

You have introduced formulae in cells I4:AO4 ; which formula needs to be revised to consider public holiday working ?

Narayan

 

[Jeepcraz]

Hi ,

You have introduced formulae in cells I4:AO4 ; which formula needs to be revised to consider public holiday working ?

Narayan

Hi Narayan

Cells that falls under Public holiday working
K4 - D
N4 - D 12hrs
Q4 - A
T4 - N
X4 - either CCL, SCL, ML, PL on Day shift
AA4 - either CCL, SCL, ML, PL on Afternoon shift
AD4 - either CCL, SCL, ML, PL on Night shift
AH4 - US or OJT on Day shift
AK4 - US or OJT on Afternoon shift
AN4 - US or OJT on Night shift

I have attached another file which contains the original format of the roster with the macro and your formula. The previous one is after macro which I hope not to use.

 

[NARAYANK991]

Hi ,

So many cells ?

I am sorry , but I can look into only 2 of them , which dealt with D and 12hrs on weekdays and weekends.

Here , as far as I can see , you want that if a Public Holiday has D and 12hrs , it will be treated as a weekend.

Narayan

 

[Jeepcraz]

Hi ,

So many cells ?

I am sorry , but I can look into only 2 of them , which dealt with D and 12hrs on weekdays and weekends.

Here , as far as I can see , you want that if a Public Holiday has D and 12hrs , it will be treated as a weekend. That’s correct

Narayan

Sure. I got you wrong. Thought you want me to provide those that involved public holiday

 

[NARAYANK991]

Hi ,

See the attached file , where the revised formula , using helper cells , has been entered in cells I7 and J7. You may insert this in the relevant cells.

The helper cells are in the range DC3:GQ3 , where the data in cells C3:CQ3 is copied unless a day happens to be a public holiday , in which case the weekend day Saturday is entered.

The revised formula makes use of DC3:GQ3 instead of the original C3:CQ3.

Narayan

 

[Jeepcraz]

Okie. Is there's a way to modify the formula to recognize e.g D1 12hrs or DSGT 12hrs, instead of just D 12hrs. Anything numeric or alphabet that accompanying D?

 

[NARAYANK991]

Hi ,

Can you specify all the text strings that will precede the 12hrs in a cell ?

You have mentioned D1 12hrs , DSGT 12hrs ; any others ?

Narayan

 

[Jeepcraz]

D1 12hrs to D6 12hrs, DT 12hrs, DSGT 12hrs, DA 12hrs, DB 12hrs

 

[NARAYANK991]

Hi ,

OK. Give me some time to upload the revised file.

Narayan

 

[Jeepcraz]

Hi ,

OK. Give me some time to upload the revised file.

Narayan

=SUMPRODUCT(MMULT(--((October!$B$7:$B$69=$A4)*((LEFT(October!$C$7:$CP$69,1)="D")*(October!$D$7:$CQ$69="12hrs"))),TRANSPOSE(--(IFERROR(MATCH(October!$C$75:$CP$75,$B$1:$F$1,0),0)>0))))

By changing it to this, I managed to get the same result.