Sum hours by date range by employee ID

alessiaexcels

New Member
Joined
Jul 29, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to work out an excel formula to sum the hours (yellow shaded column E) for a date range by employee ID. I need this as the bill rate to multiply these hours by changes over time.

I have two sheets:
1) With hours by day - dates across the columns and employee ID/name down the rows.
2) A summary of employee IDs and date ranges for a specific bill rate to apply to that range. I need the total hours by relevant date ranges pulled in here from 1).

I have included both sheets as mini sheets below.

Any help is much appreciated.

Thank you!
Kind regards,
Alessia


Bill example.xlsx
ABCDEFG
1IDNameStart dateEnd date (blank if current)HoursBill rateBill $
2100484Name 19/19/239/30/231000
3100484Name 110/1/2310/26/231300
4100089Name 29/11/239/30/23500
5100089Name 210/1/235/14/24550
6100089Name 25/15/24700
7100081Name 39/11/239/30/231450
8100081Name 310/1/235/5/241500
9100081Name 35/6/241800
10107338Name 411/27/236/28/241250
11100353Name 511/20/23660
12107450Name 63/1/24950
13104585Name 74/8/245/14/241100
14104585Name 75/15/241300
150
Bill
Cell Formulas
RangeFormula
G2:G14G2=E2*F2
G15G15=SUM(G2:G14)



Bill example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZFAFBFCFDFEFFFGFHFIFJFKFLFMFNFOFPFQFRFSFTFUFVFWFXFYFZGAGBGCGDGEGFGGGHGIGJGKGLGMGNGOGPGQGRGSGTGUGVGWGXGYGZHAHBHCHDHEHFHGHHHIHJHKHLHMHNHOHPHQHRHSHTHUHVHWHXHYHZIAIBICIDIEIFIGIHIIIJIKILIMINIOIPIQIRISITIUIVIWIXIYIZJAJBJCJDJEJFJGJHJIJJJKJLJMJNJOJPJQJRJSJTJUJVJWJXJYJZKAKBKCKDKEKFKGKHKI
1IDName9/11/239/12/239/13/239/14/239/15/239/16/239/17/239/18/239/19/239/20/239/21/239/22/239/23/239/24/239/25/239/26/239/27/239/28/239/29/239/30/2310/1/2310/2/2310/3/2310/4/2310/5/2310/6/2310/7/2310/8/2310/9/2310/10/2310/11/2310/12/2310/13/2310/14/2310/15/2310/16/2310/17/2310/18/2310/19/2310/20/2310/21/2310/22/2310/23/2310/24/2310/25/2310/26/2310/27/2310/28/2310/29/2310/30/2310/31/2311/1/2311/2/2311/3/2311/4/2311/5/2311/6/2311/7/2311/8/2311/9/2311/10/2311/11/2311/12/2311/13/2311/14/2311/15/2311/16/2311/17/2311/18/2311/19/2311/20/2311/21/2311/22/2311/23/2311/24/2311/25/2311/26/2311/27/2311/28/2311/29/2311/30/2312/1/2312/2/2312/3/2312/4/2312/5/2312/6/2312/7/2312/8/2312/9/2312/10/2312/11/2312/12/2312/13/2312/14/2312/15/2312/16/2312/17/2312/18/2312/19/2312/20/2312/21/2312/22/2312/23/2312/24/2312/25/2312/26/2312/27/2312/28/2312/29/2312/30/2312/31/231/1/241/2/241/3/241/4/241/5/241/6/241/7/241/8/241/9/241/10/241/11/241/12/241/13/241/14/241/15/241/16/241/17/241/18/241/19/241/20/241/21/241/22/241/23/241/24/241/25/241/26/241/27/241/28/241/29/241/30/241/31/242/1/242/2/242/3/242/4/242/5/242/6/242/7/242/8/242/9/242/10/242/11/242/12/242/13/242/14/242/15/242/16/242/17/242/18/242/19/242/20/242/21/242/22/242/23/242/24/242/25/242/26/242/27/242/28/242/29/243/1/243/2/243/3/243/4/243/5/243/6/243/7/243/8/243/9/243/10/243/11/243/12/243/13/243/14/243/15/243/16/243/17/243/18/243/19/243/20/243/21/243/22/243/23/243/24/243/25/243/26/243/27/243/28/243/29/243/30/243/31/244/1/244/2/244/3/244/4/244/5/244/6/244/7/244/8/244/9/244/10/244/11/244/12/244/13/244/14/244/15/244/16/244/17/244/18/244/19/244/20/244/21/244/22/244/23/244/24/244/25/244/26/244/27/244/28/244/29/244/30/245/1/245/2/245/3/245/4/245/5/245/6/245/7/245/8/245/9/245/10/245/11/245/12/245/13/245/14/245/15/245/16/245/17/245/18/245/19/245/20/245/21/245/22/245/23/245/24/245/25/245/26/245/27/245/28/245/29/245/30/245/31/246/1/246/2/246/3/246/4/246/5/246/6/246/7/246/8/246/9/246/10/246/11/246/12/246/13/246/14/246/15/246/16/246/17/246/18/246/19/246/20/246/21/246/22/246/23/246/24/246/25/246/26/246/27/246/28/24TOTAL
2100484Name 188888888888888888888888888888848
3100089Name 28888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888881010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101188
4100081Name 38888888888888888888888881010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101200
5107338Name 4888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888881480
6100353Name 58888888888888888888888888888888888888888888888881010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101010101284
7107450Name 688888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888736
8104585Name 7101010101010101010101010101010101010101010101010101010101010101010101010360
Hours to 28JUN24
Cell Formulas
RangeFormula
KI2:KI8KI2=SUM(AQ2:KH2)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
You should be able to use something like this:

Excel Formula:
=SUMIFS(INDEX('Hours to 28JUN24'!$C$2:$KH$8,MATCH(A2,'Hours to 28JUN24'!$A$2:$A$8,0),0),'Hours to 28JUN24'!$C$1:$KH$1,">="&$C2,'Hours to 28JUN24'!$C$1:$KH$1,"<="&$D2)

adjusting the ranges to cover the real data rows
 
Upvote 0
Solution
Another option:
Excel Formula:
=sumproduct('Hours to 28JUN24'!$C$2:$KH$8,('Hours to 28JUN24'!$B$2:$B$8=B2)*('Hours to 28JUN24'!$C$1:$KH$1>=C2)*('Hours to 28JUN24'!$C$1:$KH$1>=if(D2="",TODAY(),D2)))
You may use the last part also in Rory's formula:
Excel Formula:
... ,'Hours to 28JUN24'!$C$1:$KH$1,"<="&if(D2="",TODAY(),D2))
 
Upvote 0

Forum statistics

Threads
1,224,226
Messages
6,177,274
Members
452,765
Latest member
Erka Gizli

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