Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-query

Summarize Date From Multiple Sheets In Google Sheets

I am using an app that counts many of our daily activities but unfortunately does not have a good reporting tool. I am able to easily copy and paste our daily activities into a Google Sheet, but I need to be able to summarize by weeks, months, quarters, half-years and years so that I can determine our activity trends.

I cannot use Zapier or any other automation (that I am aware of), so the daily copy/paste to a sheet is the starting point for this. I have created

https://docs.google.com/spreadsheets/d/1JrOGql3jTEI6kRxVrlUcFSDVHiK2rsAOkdXJ3ZL94Kc/edit?usp=sharing

as a simplified example of what I need, it is editable. Also, there will be people added and removed from the daily report as time goes on (hirings/terminations).

How do I produce a report that shows individual and collective trends weekly, monthly, quarterly, half-year, and annually when each day's data exists on its own sheet?

20 years ago, I did something like this in excel, using a formula like: =SUM(FirstSheet:LastSheet!I2) to grab a specific cell from every sheet. But my current needs require the flexibility of employee records adjusting to different rows as employees are added or terminated. So I need a variable that reads the employee name to confirm, as well as the column header in case my software adds or removes something from the report that I am copying and pasting.

=SUM(FirstSheet:LastSheet!I2)

I would like report(s) that show summaries by an employee for each referenced time frame for each data being tracked.

Solution 
  • ={"Name", "Calls", "Emails", "Texts"; 
 QUERY({'1-1-19'!A2:E;'1-2-19'!A2:E;'1-3-19'!A2:E;'1-4-19'!A2:E;
        '1-5-19'!A2:E;'1-6-19'!A2:E;'1-7-19'!A2:E;'1-8-19'!A2:E}, 
"select Col1,sum(Col2),sum(Col3),sum(Col4) 
 where Col1 is not null 
 group by Col1
 label sum(Col2)'',sum(Col3)'',sum(Col4)''", 0)}

    0

    perhaps to make it easier you could create a Named Range from sheet names like for filter of year, month, week, quarter etc.