Conditional Sum from different sheet

W

wezzy28

New Member
Joined
Mar 12, 2018
Messages
9
Hello!
fYYSKn
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...
fYYSKn
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
 
Sort by date Sort by votes
wezzy28 said:
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...
Click to expand...

Hi Wezzy28!

I did some tests and discovery that my last suggestion is slow.

So try the new formulas below:

1) In C29, C30, C31 and C32 and copy the range C29:C32 to the right

=SUM(C30:C32)

=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)*(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2)<6))

=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)*(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2)=6))

=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)*(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2)=7))

2) Finally, select the range C29:I33 and copy down


[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
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
markmzz said:
Hi Wezzy28!

I did some tests and discovery that my last suggestion is slow.

So try the new formulas below:

1) In C29, C30, C31 and C32 and copy the range C29:C32 to the right

=SUM(C30:C32)

=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)*(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2)<6))

=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)*(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2)=6))

=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)*(WEEKDAY('LABOUR ONLY'!$E$2:$LI$2,2)=7))

2) Finally, select the range C29:I33 and copy down


[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
Click to expand...

It's working!!! Thank you so much! You've saved me a lot of trouble!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
182
Madam_Xavier
M
D
  • Question Question
How to compare study plan on two different sheets?
Replies
0
Views
352
Damianzt47
D
N
  • Question Question
Sim if with conditions
Replies
4
Views
746
KRice
K
M
  • Solved
Sum based on filter and if statement.
Replies
4
Views
627
Fluff
Fluff
simonkingston
  • Question Question
Extracting brackets from Pay for different Calculations
Replies
5
Views
571
Dave Patton
D

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top