0

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.

The spreadsheet can be accessed here: https://docs.google.com/spreadsheets/d/1TeB73zsRo3Bm9tvN392Uy1I-KcWy5PRTs6KSFe-m0VM/edit?usp=sharing

1
0

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.

1
  • Thank you! This works perfectly
    – Becca W
    Aug 23 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.