Search results

I have a reference sheet named April. On Sheet 3, i would like to use excel to tabulate the result of the shift each staff is working and according to weekday and weekend/holiday in order to calculate the shift allowance WD means weekday day shift WA means weekday afternoon shift WN means...

=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.

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

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?

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

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...

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...

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

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...

Pardon me, but I still don’t understand why it’s DC7:GQ69. How you derive the set of helper column and how they recognize D and 12hrs from it? I don’t see D and 12hrs inside the formula

Yes it works great. Can I know how the formula works? =MMULT(INDEX(October!$DC$7:$GQ$69,MATCH($A$4,October!$B$7:$B$69,0),),TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$CQ$3,$B$1:$F$1,0),0)>0)))

Narayan Yes it worked for the two formulas. But now there's a problem which i have forgotten to mention. Besides D 12hrs, I have N 12hrs too(the cell in purple). Using the two formulas you gave, it also counts D68 and E68 as one count which I don't want it to happen. The purpose of this is to...

Here you go. i have tried to modify the formula but it's giving wrong result

Yes I got the same result in the sample file. But when I put it in the actual file, it give me a result of 2

I have tried this but it gave me a result of 2 instead of 1.

The cells highlighted in green are the one which i want to count I4 contains the formula for weekday =SUMPRODUCT(((October!$C$3:$N$3=$B$1)+(October!$C$3:$N$3=$C$1)+(October!$C$3:$N$3=$D$1)+(October!$C$3:$N$3=$E$1)+(October!$C$3:$N$3=$F$1))*(((October!$B$7:$B$69)=$A4)*((October!$C$7:$N$69="D...

Yes. Tried thus before but the result is a zero. Was wondering what the problem

Hi this is the formula I’m using which is not what I want. I want the formula to able to count if column F is D and column G is 12 hrs

I need to get excel to recognize two different values in two different column but unable to do so. So i was "forced to" put the value in one single column and used the formula below formula used...