Sum hours by date range by employee ID

alessiaexcels · Jul 29, 2024

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

A

B

C

D

E

F

G

1

ID

Name

Start date

End date (blank if current)

Hours

Bill rate

Bill $

2

100484

Name 1

9/19/23

9/30/23

100

0

3

100484

Name 1

10/1/23

10/26/23

130

0

4

100089

Name 2

9/11/23

9/30/23

50

0

5

100089

Name 2

10/1/23

5/14/24

55

0

6

100089

Name 2

5/15/24

70

0

7

100081

Name 3

9/11/23

9/30/23

145

0

8

100081

Name 3

10/1/23

5/5/24

150

0

9

100081

Name 3

5/6/24

180

0

10

107338

Name 4

11/27/23

6/28/24

125

0

11

100353

Name 5

11/20/23

66

0

12

107450

Name 6

3/1/24

95

0

13

104585

Name 7

4/8/24

5/14/24

110

0

14

104585

Name 7

5/15/24

130

0

15

0

Bill

Cell Formulas
Range	Formula
G2:G14	G2	=E2*F2
G15	G15	=SUM(G2:G14)

Bill example.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

CA

CB

CC

CD

CE

CF

CG

CH

CI

CJ

CK

CL

CM

CN

CO

CP

CQ

CR

CS

CT

CU

CV

CW

CX

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

DI

DJ

DK

DL

DM

DN

DO

DP

DQ

DR

DS

DT

DU

DV

DW

DX

DY

DZ

EA

EB

EC

ED

EE

EF

EG

EH

EI

EJ

EK

EL

EM

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

FH

FI

FJ

FK

FL

FM

FN

FO

FP

FQ

FR

FS

FT

FU

FV

FW

FX

FY

FZ

GA

GB

GC

GD

GE

GF

GG

GH

GI

GJ

GK

GL

GM

GN

GO

GP

GQ

GR

GS

GT

GU

GV

GW

GX

GY

GZ

HA

HB

HC

HD

HE

HF

HG

HH

HI

HJ

HK

HL

HM

HN

HO

HP

HQ

HR

HS

HT

HU

HV

HW

HX

HY

HZ

IA

IB

IC

ID

IE

IF

IG

IH

II

IJ

IK

IL

IM

IN

IO

IP

IQ

IR

IS

IT

IU

IV

IW

IX

IY

IZ

JA

JB

JC

JD

JE

JF

JG

JH

JI

JJ

JK

JL

JM

JN

JO

JP

JQ

JR

JS

JT

JU

JV

JW

JX

JY

JZ

KA

KB

KC

KD

KE

KF

KG

KH

KI

1

ID

Name

9/11/23

9/12/23

9/13/23

9/14/23

9/15/23

9/16/23

9/17/23

9/18/23

9/19/23

9/20/23

9/21/23

9/22/23

9/23/23

9/24/23

9/25/23

9/26/23

9/27/23

9/28/23

9/29/23

9/30/23

10/1/23

10/2/23

10/3/23

10/4/23

10/5/23

10/6/23

10/7/23

10/8/23

10/9/23

10/10/23

10/11/23

10/12/23

10/13/23

10/14/23

10/15/23

10/16/23

10/17/23

10/18/23

10/19/23

10/20/23

10/21/23

10/22/23

10/23/23

10/24/23

10/25/23

10/26/23

10/27/23

10/28/23

10/29/23

10/30/23

10/31/23

11/1/23

11/2/23

11/3/23

11/4/23

11/5/23

11/6/23

11/7/23

11/8/23

11/9/23

11/10/23

11/11/23

11/12/23

11/13/23

11/14/23

11/15/23

11/16/23

11/17/23

11/18/23

11/19/23

11/20/23

11/21/23

11/22/23

11/23/23

11/24/23

11/25/23

11/26/23

11/27/23

11/28/23

11/29/23

11/30/23

12/1/23

12/2/23

12/3/23

12/4/23

12/5/23

12/6/23

12/7/23

12/8/23

12/9/23

12/10/23

12/11/23

12/12/23

12/13/23

12/14/23

12/15/23

12/16/23

12/17/23

12/18/23

12/19/23

12/20/23

12/21/23

12/22/23

12/23/23

12/24/23

12/25/23

12/26/23

12/27/23

12/28/23

12/29/23

12/30/23

12/31/23

1/1/24

1/2/24

1/3/24

1/4/24

1/5/24

1/6/24

1/7/24

1/8/24

1/9/24

1/10/24

1/11/24

1/12/24

1/13/24

1/14/24

1/15/24

1/16/24

1/17/24

1/18/24

1/19/24

1/20/24

1/21/24

1/22/24

1/23/24

1/24/24

1/25/24

1/26/24

1/27/24

1/28/24

1/29/24

1/30/24

1/31/24

2/1/24

2/2/24

2/3/24

2/4/24

2/5/24

2/6/24

2/7/24

2/8/24

2/9/24

2/10/24

2/11/24

2/12/24

2/13/24

2/14/24

2/15/24

2/16/24

2/17/24

2/18/24

2/19/24

2/20/24

2/21/24

2/22/24

2/23/24

2/24/24

2/25/24

2/26/24

2/27/24

2/28/24

2/29/24

3/1/24

3/2/24

3/3/24

3/4/24

3/5/24

3/6/24

3/7/24

3/8/24

3/9/24

3/10/24

3/11/24

3/12/24

3/13/24

3/14/24

3/15/24

3/16/24

3/17/24

3/18/24

3/19/24

3/20/24

3/21/24

3/22/24

3/23/24

3/24/24

3/25/24

3/26/24

3/27/24

3/28/24

3/29/24

3/30/24

3/31/24

4/1/24

4/2/24

4/3/24

4/4/24

4/5/24

4/6/24

4/7/24

4/8/24

4/9/24

4/10/24

4/11/24

4/12/24

4/13/24

4/14/24

4/15/24

4/16/24

4/17/24

4/18/24

4/19/24

4/20/24

4/21/24

4/22/24

4/23/24

4/24/24

4/25/24

4/26/24

4/27/24

4/28/24

4/29/24

4/30/24

5/1/24

5/2/24

5/3/24

5/4/24

5/5/24

5/6/24

5/7/24

5/8/24

5/9/24

5/10/24

5/11/24

5/12/24

5/13/24

5/14/24

5/15/24

5/16/24

5/17/24

5/18/24

5/19/24

5/20/24

5/21/24

5/22/24

5/23/24

5/24/24

5/25/24

5/26/24

5/27/24

5/28/24

5/29/24

5/30/24

5/31/24

6/1/24

6/2/24

6/3/24

6/4/24

6/5/24

6/6/24

6/7/24

6/8/24

6/9/24

6/10/24

6/11/24

6/12/24

6/13/24

6/14/24

6/15/24

6/16/24

6/17/24

6/18/24

6/19/24

6/20/24

6/21/24

6/22/24

6/23/24

6/24/24

6/25/24

6/26/24

6/27/24

6/28/24

TOTAL

2

100484

Name 1

8

48

3

100089

Name 2

8

10

1188

4

100081

Name 3

8

10

1200

5

107338

Name 4

8

1480

6

100353

Name 5

8

10

1284

7

107450

Name 6

8

736

8

104585

Name 7

10

360

Hours to 28JUN24

Cell Formulas
Range	Formula
KI2:KI8	KI2	=SUM(AQ2:KH2)

RoryA · Jul 29, 2024

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

Kaper · Jul 29, 2024

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))

alessiaexcels · Sep 19, 2024

Thank you both, very helpful!!

Sum hours by date range by employee ID

alessiaexcels

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

Kaper

Well-known Member

alessiaexcels

New Member

Similar threads

Forum statistics

Share this page

Sum hours by date range by employee ID

alessiaexcels

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

Kaper

Well-known Member

alessiaexcels

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock