# How do I sum the hours worked, per employee, between specified dates in Google Sheets?

I have been working on a Google Sheet that I am able to import my Google Calendar into and it automatically breaks it down.

I have one more formula to figure out before its usable but I can't seem to figure it out.

The formula needs to sum up the hours worked by each employee based on the specified dates in the formula. I'd like to break it down Monthly and Weekly.

I have tried the following formula which sums all of the hours worked within the date range:

=SUMIFS('2. Calendar'!G14:G1000,'2. Calendar'!B14:B1000,">="&date(2021,1,1),'2. Calendar'!B14:B1000,"<="&date(2021,1,31))

BUT

It needs to also search through a column of names and only show the hours worked for the specified employee. Using this formula below, I'm able to sum up the total hours of a specific employee:

=SUMIF('2. Calendar'!E14:E1000,"*"&D6&"*",'2. Calendar'!G14:G1000)

How can I combine these formulas so that it sums up the total hours worked within a specified date range, by employee?

Any help is appreciated, I have been trying to figure this out now for over a week. I'm at a complete halt until I can find help from someone.

These kinds of reports are often created with the query() function. Unfortunately, the "Notes" column appears to contain a comma-separated list of employee names, rather than a single name each time. Is this the original format? The data would be easier to work with if you used a simple row-oriented data layout. In your sample spreadsheet, try Insert > New sheet and these query() formulas to see how it would work:

=arrayformula(
query(
{ Import!A4:E, text(Import!A4:A, "yyyy-mm") },
"select Col6, sum(Col4)
where Col4 is not null
group by Col6
pivot Col5
label Col6 'Period' ",
1
)
)
=arrayformula(
query(
{ Import!A4:E, year(Import!A4:A) & "/w" & weeknum(Import!A4:A) },
"select Col6, sum(Col4)
where Col4 is not null
group by Col6
pivot Col5
label Col6 'Period' ",
1
)
)

Row-oriented data layout looks like this:

Name             Subject     Quarter   Score
Marie Curie       Chemistry   Q1       100
Albert Einstein   Chemistry   Q1       34
Marie Curie       Math       Q1       88
Albert Einstein   Math       Q1       66

This loosely corresponds with second normal form.

The primary benefit of a row-oriented data layout that it makes it easy to use spreadsheet functions like query() and filter() to further process the data and create reports.

• Thank you! This works perfectly Aug 23, 2021 at 23:56