Sumproduct in-between two date range with greater than and less than

[silambarasan.J]

Hi Forum Leader,

Column A is a date
Column B is a overtime hours from Monday to Friday
Column C is a working hours from Monday to Sunday

DATA;

DATE ***********OVER TIME-MONDAY TO FRIDAY TOTAL WORKING HOURS
20/03/2017 ******1.5**********************************8
23/03/2017 ******1 **********************************8
24/03/2017 *****0.5 **********************************8
27/03/2017** 2.5 **********************************8
29/03/2017***** 2.5 **********************************8

REQUIRED FORMAT;

SHIFT *******TOTAL WORKING HOUR
Saturday ******* 42
Sunday ******* 91
Overtime < 2 ****** 5
Overtime > 2 ****** 5

Query 1;

Am trying to achieve the result sum of working hours for Saturday and Sunday

Am try to apply the formula in D3 and D4 for Saturday and Sunday name of the sheet “Payslip”

Saturday

=SUMPRODUCT(('TIME SHEET'!$A$2:$A$92 > PAYSLIP!$D$1)*('TIME SHEET'!$A$2:$A$92 < PAYSLIP!$E$1)*--(WEEKDAY('TIME SHEET'!$A$2:$A$92,1)=7)*('TIME SHEET'!$C$2:$C$92))

Sunday

=SUMPRODUCT(('TIME SHEET'!$A$2:$A$92 > PAYSLIP!$D$1)*('TIME SHEET'!$A$2:$A$92 < PAYSLIP!$E$1)*--(WEEKDAY('TIME SHEET'!$A$2:$A$92,1)=1)*('TIME SHEET'!$C$2:$C$92))

Query 2;

Sum of units for over time from Monday to Friday two different ranges less than 2 hours and great than 2 hours

[JohnTopley]

Try

in D3

=SUMPRODUCT(('TIME SHEET'!$A$2:$A$92>=PAYSLIP!$D$1)*('TIME SHEET'!$A$2:$A$92<=PAYSLIP!$E$1)*--(WEEKDAY('TIME SHEET'!$A$2:$A$92,1)=7),('TIME SHEET'!$C$2:$C$92))

in D4

=SUMPRODUCT(('TIME SHEET'!$A$2:$A$92>=PAYSLIP!$D$1)*('TIME SHEET'!$A$2:$A$92<=PAYSLIP!$E$1)*--(WEEKDAY('TIME SHEET'!$A$2:$A$92,1)=1),('TIME SHEET'!$C$2:$C$92))

in D5

=SUMPRODUCT(('TIME SHEET'!$A$2:$A$92>=PAYSLIP!$D$1)*('TIME SHEET'!$A$2:$A$92<=PAYSLIP!$E$1)*('TIME SHEET'!$B$2:$B$92<2),('TIME SHEET'!$B$2:$B$92))

in D6

=SUMPRODUCT(('TIME SHEET'!$A$2:$A$92>=PAYSLIP!$D$1)*('TIME SHEET'!$A$2:$A$92<=PAYSLIP!$E$1)*('TIME SHEET'!$B$2:$B$92>2),('TIME SHEET'!$B$2:$B$92))

[silambarasan.J]

thank you so much for your help.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.