# Help needed with Sumproduct

#### Wimpie

##### Board Regular
Good day

I work with Excel 2010
Can someone please assist with the below
In a sheet called "Daily" I have the below
 Date Day Hours Driven Hours Worked Total Hours Overtime due 2016/10/01 Saturday 03:53 02:30 06:23 02:23 2016/10/02 Sunday 02:49 07:22 10:12 10:12 2016/10/03 Monday 04:01 03:47 07:49 00:19 2016/10/04 Tuesday 04:38 04:01 08:39 01:19 2016/10/05 Wednesday 01:48 03:22 05:10 -2:19 2016/10/06 Thursday 3:38 05:26 08:50 01:20

<tbody>
</tbody>

In a sheet called "Cycle" I have the below
 Cycle Number Date From Date To Overtime Hours Payment captured Double Overtime Hours 10 2016/09/19 2016/10/16 11 2016/10/17 2016/11/13

<tbody>
</tbody>
[/SUB]
I need a formula that would add all the Monday's to Saturdays in the Overtime Hours column between the cycle days (it should be 15:26)
And that would calculate the Sundays in the Double Overtime Hours column between the cycle days (it should be 10:12)

After a lot of reading the closest I got was the below
=SUMPRODUCT(--(Daily!\$A\$2:\$A\$2000>=Cycle!B12),--(Daily\$A\$2:\$A\$2000<=Cycle!C12),--(WEEKDAY(A2:A2000)=2),G2:G2000)
This would however only add the Mondays
I tried to use this formula to calculate the Sundays by replacing the 2 with a 1
but do not get 10:12

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Wimpie

##### Board Regular
Maybe a Sumifs formula can resolve this

#### keiserj

##### Board Regular
Try this

Code:
``=SUMIFS(Daily!F2:F7,Daily!A2:A7,">="&cycle!B2,Daily!A2:A7,"<="&cycle!C2)-SUMIFS(Daily!F2:F7,Daily!A2:A7,">="&cycle!B2,Daily!A2:A7,"<="&cycle!C2,Daily!B2:B7,"SUNDAY")``

#### Wimpie

##### Board Regular
Keiserj, this is amazing and 100% correct for the Overtime Hours.
Can you please assist me with the formula for the double overtime , this is the same range but should only add Sundays

#### keiserj

##### Board Regular
You should just be able to delete the front half the last formula

Code:
``=SUMIFS(Daily!F2:F7,Daily!A2:A7,">="&cycle!B2,Daily!A2:A7,"<="&cycle!C2,Daily!B2:B7,"SUNDAY")``

#### Wimpie

##### Board Regular
Keiserj, I figured out that by using the last bit of the formula (From the Minus) it gives me exactly the amount of double overtime for Sunday
Again a BIG THANK YOU
You have saved me days and hours of work with this formulas

Replies
6
Views
729
Replies
7
Views
571
Replies
13
Views
761
Replies
1
Views
504
Replies
7
Views
550

1,190,565
Messages
5,981,707
Members
439,732
Latest member
TC_vii

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

### Which adblocker are you using?

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

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