Conditional Sum from different sheet

[wezzy28]

Hello!

If you look at the image, I'm trying to sum the total number of hours for a week for concrete repairer, skilled op, labourer etc...

https://ibb.co/fYYSKnThe sum should be on a different page and looks like thisTrade Total HoursConcrete RepairerLabourerSkilled OpI tried sumifs but it's not working.Any help would be greatly appreciated.Thank you

 

[oldbrewer]

is this what you want ?

[TABLE="width: 502"]
<colgroup><col span="2"><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD]01/03/2018[/TD]
[TD]02/03/2018[/TD]
[TD]03/03/2018[/TD]
[TD]04/03/2018[/TD]
[TD]05/03/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]concrete[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]steel[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wood[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]concrete[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]steel[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wood[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]concrete[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]steel[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wood[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]01/03/2018[/TD]
[TD]02/03/2018[/TD]
[TD]03/03/2018[/TD]
[TD]04/03/2018[/TD]
[TD]05/03/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]concrete[/TD]
[TD]18[/TD]
[TD]21[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]steel[/TD]
[TD]21[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]30[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wood[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]30[/TD]
[TD]33[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

Hello!If you look at the image, I'm trying to sum the total number of hours for a week for concrete repairer, skilled op, labourer etc...https://ibb.co/fYYSKnThe sum should be on a different page and looks like thisTrade Total HoursConcrete RepairerLabourerSkilled OpI tried sumifs but it's not working.Any help would be greatly appreciated.Thank you

Hi!

Put the formula below in C3 (in Summary sheet)

=SUMPRODUCT(('Jun ''17 - 28 jan '' 18'!$GZ$126:$HT$141*('Jun ''17 - 28 jan '' 18'!$C$126:$C$141=$A3))*
('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2>=C$2)*('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2<=D$2))

After that, select the range C3:D3 and copy to the right until H3 and down until H7.


[TABLE="class: grid, width: 722"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD][/TD]
[TD]Week2[/TD]
[TD][/TD]
[TD]Week3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Trade[/TD]
[TD]Agency[/TD]
[TD="align: right"]15/01/2018[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]22/01/2018[/TD]
[TD="align: right"]28/01/2018[/TD]
[TD="align: right"]29/01/2018[/TD]
[TD="align: right"]04/02/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Concrete Repair[/TD]
[TD]Search[/TD]
[TD="align: right"]482,0[/TD]
[TD][/TD]
[TD="align: right"]438,0[/TD]
[TD][/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Labourer[/TD]
[TD]Search[/TD]
[TD="align: right"]89,0[/TD]
[TD][/TD]
[TD="align: right"]107,0[/TD]
[TD][/TD]
[TD="align: right"]108,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Joiner[/TD]
[TD]Search[/TD]
[TD="align: right"]44,0[/TD]
[TD][/TD]
[TD="align: right"]61,0[/TD]
[TD][/TD]
[TD="align: right"]57,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Senior Joiner[/TD]
[TD]TMR[/TD]
[TD="align: right"]207,0[/TD]
[TD][/TD]
[TD="align: right"]205,0[/TD]
[TD][/TD]
[TD="align: right"]210,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Concrete Repair[/TD]
[TD]TMR[/TD]
[TD="align: right"]482,0[/TD]
[TD][/TD]
[TD="align: right"]438,0[/TD]
[TD][/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]*******[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[wezzy28]

Hi!

Put the formula below in C3 (in Summary sheet)

=SUMPRODUCT(('Jun ''17 - 28 jan '' 18'!$GZ$126:$HT$141*('Jun ''17 - 28 jan '' 18'!$C$126:$C$141=$A3))*
('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2>=C$2)*('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2<=D$2))

After that, select the range C3:D3 and copy to the right until H3 and down until H7.


[TABLE="class: grid, width: 722"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD][/TD]
[TD]Week2[/TD]
[TD][/TD]
[TD]Week3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Trade[/TD]
[TD]Agency[/TD]
[TD="align: right"]15/01/2018[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]22/01/2018[/TD]
[TD="align: right"]28/01/2018[/TD]
[TD="align: right"]29/01/2018[/TD]
[TD="align: right"]04/02/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Concrete Repair[/TD]
[TD]Search[/TD]
[TD="align: right"]482,0[/TD]
[TD][/TD]
[TD="align: right"]438,0[/TD]
[TD][/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Labourer[/TD]
[TD]Search[/TD]
[TD="align: right"]89,0[/TD]
[TD][/TD]
[TD="align: right"]107,0[/TD]
[TD][/TD]
[TD="align: right"]108,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Joiner[/TD]
[TD]Search[/TD]
[TD="align: right"]44,0[/TD]
[TD][/TD]
[TD="align: right"]61,0[/TD]
[TD][/TD]
[TD="align: right"]57,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Senior Joiner[/TD]
[TD]TMR[/TD]
[TD="align: right"]207,0[/TD]
[TD][/TD]
[TD="align: right"]205,0[/TD]
[TD][/TD]
[TD="align: right"]210,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Concrete Repair[/TD]
[TD]TMR[/TD]
[TD="align: right"]482,0[/TD]
[TD][/TD]
[TD="align: right"]438,0[/TD]
[TD][/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]*******[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

Thank you Mark! I've solved it on my own yesterday and yes, I did indeed use the sumproduct formula and it worked! However, I'll try yours because I think your formula automatically add the 7 days for the weekly total?

 

[markmzz]

Thank you Mark! I've solved it on my own yesterday and yes, I did indeed use the sumproduct formula and it worked! However, I'll try yours because I think your formula automatically add the 7 days for the weekly total?

You're welcome.

Markmzz

 

[wezzy28]

Thank you Mark! I've solved it on my own yesterday and yes, I did indeed use the sumproduct formula and it worked! However, I'll try yours because I think your formula automatically add the 7 days for the weekly total?

I have one more question:

My weekly total should be displayed in cell week ending 21/01/2018 (cell D2) rather than C2. On my summary page I'll have only 21/01/2018, 28/01/2018, 04/02/2018, etc...

the logical operators won't work then...how do I proceed please?

 

[markmzz]

I have one more question:

My weekly total should be displayed in cell week ending 21/01/2018 (cell D2) rather than C2. On my summary page I'll have only 21/01/2018, 28/01/2018, 04/02/2018, etc...

the logical operators won't work then...how do I proceed please?

Hi!

Try this in C3 and copy down and to the right:

=SUMPRODUCT(('Jun ''17 - 28 jan '' 18'!$GZ$126:$HT$141*('Jun ''17 - 28 jan '' 18'!$C$126:$C$141=$A3))*
('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2>=C$2-6)*('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2<=C$2))


[TABLE="class: grid, width: 470"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Trade[/TD]
[TD]Agency[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]28/01/2018[/TD]
[TD="align: right"]04/02/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Concrete Repair[/TD]
[TD]Search[/TD]
[TD="align: right"]482,0[/TD]
[TD="align: right"]438,0[/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Labourer[/TD]
[TD]Search[/TD]
[TD="align: right"]89,0[/TD]
[TD="align: right"]107,0[/TD]
[TD="align: right"]108,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Joiner[/TD]
[TD]Search[/TD]
[TD="align: right"]44,0[/TD]
[TD="align: right"]61,0[/TD]
[TD="align: right"]57,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Senior Joiner[/TD]
[TD]TMR[/TD]
[TD="align: right"]207,0[/TD]
[TD="align: right"]205,0[/TD]
[TD="align: right"]210,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Concrete Repair[/TD]
[TD]TMR[/TD]
[TD="align: right"]482,0[/TD]
[TD="align: right"]438,0[/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]*******[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[wezzy28]

Hi!

Try this in C3 and copy down and to the right:

=SUMPRODUCT(('Jun ''17 - 28 jan '' 18'!$GZ$126:$HT$141*('Jun ''17 - 28 jan '' 18'!$C$126:$C$141=$A3))*
('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2>=C$2-6)*('Jun ''17 - 28 jan '' 18'!$GZ$2:$HT$2<=C$2))


[TABLE="class: grid, width: 470"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Trade[/TD]
[TD]Agency[/TD]
[TD="align: right"]21/01/2018[/TD]
[TD="align: right"]28/01/2018[/TD]
[TD="align: right"]04/02/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Concrete Repair[/TD]
[TD]Search[/TD]
[TD="align: right"]482,0[/TD]
[TD="align: right"]438,0[/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Labourer[/TD]
[TD]Search[/TD]
[TD="align: right"]89,0[/TD]
[TD="align: right"]107,0[/TD]
[TD="align: right"]108,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Joiner[/TD]
[TD]Search[/TD]
[TD="align: right"]44,0[/TD]
[TD="align: right"]61,0[/TD]
[TD="align: right"]57,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Senior Joiner[/TD]
[TD]TMR[/TD]
[TD="align: right"]207,0[/TD]
[TD="align: right"]205,0[/TD]
[TD="align: right"]210,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Concrete Repair[/TD]
[TD]TMR[/TD]
[TD="align: right"]482,0[/TD]
[TD="align: right"]438,0[/TD]
[TD="align: right"]487,0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]*******[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

So, I've started from july...week ending 30/07/2017

and the formula I used is

=SUMPRODUCT(('LABOUR ONLY'!$E$3:$LI$178*('LABOUR ONLY'!$C$3:$C$178=$G4))*
('LABOUR ONLY'!$E$2:$LI$2>=G$2-6)*('LABOUR ONLY'!$E$2:$LI$2<=G$2))

When I use the above formula, the number of hours for labourer becomes zero..and not 43..the range has changed..E3 to LI178 is the whole range...

 

[markmzz]

Hi!

Here all is Ok. Look at this:

In C29 and copy to the right and copy the range C29:I29 to the ranges C34:I34 and C39:I39

=SUMPRODUCT(('LABOUR ONLY'!$E$3:$LI$178*('LABOUR ONLY'!$C$3:$C$178=$B29))*
('LABOUR ONLY'!$E$2:$LI$2>=C$2-6)*('LABOUR ONLY'!$E$2:$LI$2<=C$2))

If possible, put a example of your file (without confidencial data) in OneDrive and put the link in your next post.


[TABLE="class: grid, width: 757"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]WEEK-ENDING[/TD]
[TD="align: right"]02/07/2017[/TD]
[TD="align: right"]09/07/2017[/TD]
[TD="align: right"]16/07/2017[/TD]
[TD="align: right"]23/07/2017[/TD]
[TD="align: right"]30/07/2017[/TD]
[TD="align: right"]06/08/2017[/TD]
[TD="align: right"]13/08/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD]Supervisor[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]62[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD]Mon - Fri[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD]Ganger[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]61[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD]Mon - Fri[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][/TD]
[TD]Labourer[/TD]
[TD="align: right"]618[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]660[/TD]
[TD="align: right"]823[/TD]
[TD="align: right"]862[/TD]
[TD="align: right"]735[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD][/TD]
[TD]Mon - Fri[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]**************[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[markmzz]

So, I've started from july...week ending 30/07/2017
and the formula I used is

=SUMPRODUCT(('LABOUR ONLY'!$E$3:$LI$178*('LABOUR ONLY'!$C$3:$C$178=$G4))*
('LABOUR ONLY'!$E$2:$LI$2>=G$2-6)*('LABOUR ONLY'!$E$2:$LI$2<=G$2))

When I use the above formula, the number of hours for labourer becomes zero..and not 43..the range has changed..E3 to LI178 is the whole range...

Hi!

First, the problem with your formula is $G4.

Now, here is a new approach with your new layout.

1) Enter the formula below in C29 and copy to the right

=SUM(C30:C32)

2) Enter the formula below in C30 and copy down and to the right

=SUMPRODUCT(('LABOUR ONLY'!$E$3:$LI$178*
('LABOUR ONLY'!$C$3:$C$178=OFFSET($C30,-MOD(ROWS(C$3:C3),5),-1)))*
('LABOUR ONLY'!$E$2:$LI$2>=C$2-6)*('LABOUR ONLY'!$E$2:$LI$2<=C$2)*
(LOOKUP(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2),{1;6;7},$B$30:$B$32)=$B30))

3) Finally, select de range C29:I32 and copy to C34 and C39


[TABLE="class: grid, width: 764"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]WEEK-ENDING[/TD]
[TD="align: right"]02/07/2017[/TD]
[TD="align: right"]09/07/2017[/TD]
[TD="align: right"]16/07/2017[/TD]
[TD="align: right"]23/07/2017[/TD]
[TD="align: right"]30/07/2017[/TD]
[TD="align: right"]06/08/2017[/TD]
[TD="align: right"]13/08/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD]Supervisor[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]62[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD]Mon - Fri[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD]Ganger[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]61[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD]Mon - Fri[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][/TD]
[TD]Labourer[/TD]
[TD="align: right"]618[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]660[/TD]
[TD="align: right"]823[/TD]
[TD="align: right"]854[/TD]
[TD="align: right"]735[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD][/TD]
[TD]Mon - Fri[/TD]
[TD="align: right"]402[/TD]
[TD="align: right"]426[/TD]
[TD="align: right"]384[/TD]
[TD="align: right"]486[/TD]
[TD="align: right"]605[/TD]
[TD="align: right"]648[/TD]
[TD="align: right"]435[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]157[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]143[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]**************[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]



Markmzz