CODEWITHSUNDEEP
google-sheetssum
gomisha
gomisha

Reputation: 2924

Summing values across multiple spreadsheets based on date

I'm trying to sum values across multiple Google Sheet spreadsheets (workbooks) that are grouped by dates. For example, I want to sum all the Delta values for March 2, 2020 across multiple spreadsheets and each spreadsheet will have 0 or more values for that date.

Here's an example with 2 spreadsheets:

Spreadsheet 1:

Date        Start   Stop    Delta
Mon 02Mar20 16:51   16:56   0:05
Mon 02Mar20 16:56   17:00   0:03
Tue 03Mar20 18:45   18:49   0:03
Tue 03Mar20 19:04   19:06   0:01

Spreadsheet 2:

Date        Start   Stop    Delta
Mon 02Mar20 8:38    8:49    0:11
Tue 03Mar20 4:47    4:50    0:03
Tue 03Mar20 17:42   17:55   0:13
Tue 03Mar20 17:58   18:45   0:47
Tue 03Mar20 18:53   19:03   0:10

I want to have a dynamic sum of the Delta columns across spreadsheets by each day in a separate spreadsheet. So here's what I would like to autogenerate. Specifically, the sum of the Delta values for Spreadsheet 1 and Spreadsheet 2 for each day (0:08, 0:11, 0:04, 1:10):

Date        Total   Spreadsheet 1   Spreadsheet 2
Mon 02Mar20 0:19    0:08            0:11
Tue 03Mar20 1:14    0:04            1:10

I tried using IMPORTRANGE but I'm not sure how to make the sums dynamic for each day. I don't know ahead of time how many entries I'll have for each date in Spreadsheet 1 and 2 so I want to have a way to auto determine how many rows to sum up each day for Spreadsheet 1 and 2. I'm guessing I would need to use QUERY or FILTER to filter all the imported values from IMPORTRANGE but I'm not sure how to do that.

Upvotes: 0

Views: 642

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

I made an easy dataset to be sum up. Got a Spreadsheet like this:

enter image description here

As you can see, the total sum values for 2nd march would be 2 and for 3rd March would be 20.

In a different Spreadsheet, got my dashboard:

enter image description here

The formula I've used in B2 is:

=SUMPRODUCT(--(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rnap9LJQJaqriiJLSsF7EWQLwBUiNviktxDAMFfW0ZE";"Hoja 1!A1:A4")=$A2);IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rnap9LJQJaqriiJLSsF7EWQLwBUiNviktxDAMFfW0ZE";"Hoja 1!B1:B4"))

This is how it works:

  1. --(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rnap9LJQJaqriiJLSsF7EWQLwBUiNviktxDAMFfW0ZE";"Hoja 1!A1:A4")=$A2) will compare the values of column A in Workbook 1 with the date in column A in my main dashboard. Because we've used a double unary operator this will return an array of 1 and 0 if there is a match or not (in this case, it will be an array like {1;1;0;0}
  2. IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rnap9LJQJaqriiJLSsF7EWQLwBUiNviktxDAMFfW0ZE";"Hoja 1!B1:B4") will return as array the values of column B in Workbook 1, in this case it will return {1;1;10;10}
  3. SUMPRODUCT will multiply both arrays and sum up the values, in this case {1;1;0;0} * {1;1;10;10} = {1;1;0;0} and the sum up of this final array is 2.

Same logic applied to second date, we would obtain {0;0;1;1} * {1;1;10;10} = {0;0;10;10} -> 20

Just add each workbook in 1 different column with same formula, and then do a normal sum up in your main dashboard to get the Grand total sum for all values in all workbooks for a specific date:

enter image description here

Hope this helps.

NOTICE: Of course, this method will work only if your dates are dates (not strings/texts) and the times in Delta are date/times too (not strings/texts)

Upvotes: 1

Related Questions