May 11, 2021
Sum the total of time per day formulas by date
One that identifies each unique date from a data set created by the form.
The second one that gives the sum of each of those unique days.
I have looked high and low for appropriate formulas for these but it's been elusive specifically for this use.
Any help from the community would be appreciated in advance to solve - and the kids will appreciate the earned pool time!
Thanks!
Sample of data:
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Adding the homework column is as simple as including column I to the QUERY and adding "SUM(I)" to the query language:
=QUERY(F6:I, "Select F, G, SUM(H), SUM(I) where F is not null group by F, G label SUM(H) '', SUM(I) '' ",0)
For the time part, as long as you have a separate date column (rather than the timestamp) and you reference that in the QUERY it will use the date you provide it to aggregate with.
Original Poster John J9455 marked this as an answer
if they are entering their data using a form the table will look different from what you show.
1) It will have a timestamp automatically that could be used (assuming it's entered the day they do the work.)
2) They will need to enter their name each time (or choose from a list more likely.) Having the name and date on EVERY row makes this MUCH easier to do and I highly recommend it. Not just because it makes the calculations easier but also because it is better data practice.
With #2 in mind, I've adjusted what your data looks like in a copy of your tab then I've put this formula in the yellow cell. It's not exactly what you were wanting but very easy to do:
=QUERY(F6:H, "Select F, G, SUM(H) where F is not null group by F, G label SUM(H) ''",0)
Does that at least look close?
We can tweak as necessary (get only the last 7 days? Just a specific week number? Etc.)
Original Poster John J9455 marked this as an answer
All Replies (5)
if they are entering their data using a form the table will look different from what you show.
1) It will have a timestamp automatically that could be used (assuming it's entered the day they do the work.)
2) They will need to enter their name each time (or choose from a list more likely.) Having the name and date on EVERY row makes this MUCH easier to do and I highly recommend it. Not just because it makes the calculations easier but also because it is better data practice.
With #2 in mind, I've adjusted what your data looks like in a copy of your tab then I've put this formula in the yellow cell. It's not exactly what you were wanting but very easy to do:
=QUERY(F6:H, "Select F, G, SUM(H) where F is not null group by F, G label SUM(H) ''",0)
Does that at least look close?
We can tweak as necessary (get only the last 7 days? Just a specific week number? Etc.)
Original Poster John J9455 marked this as an answer
May 11, 2021
if they are entering their data using a form the table will look different from what you show.
1) It will have a timestamp automatically that could be used (assuming it's entered the day they do the work.)
Correct - in my use case it is some times a day or two before they get back to entering data in the form.
2) They will need to enter their name each time (or choose from a list more likely.) Having the name and date on EVERY row makes this MUCH easier to do and I highly recommend it. Not just because it makes the calculations easier but also because it is better data practice.
I agree with you here and to your point above about the form, this data is collected in this way but I left it out of my sample.
With #2 in mind, I've adjusted what your data looks like in a copy of your tab then I've put this formula in the yellow cell. It's not exactly what you were wanting but very easy to do:
=QUERY(F6:H, "Select F, G, SUM(H) where F is not null group by F, G label SUM(H) ''",0)
Does that at least look close?
This is actually dead on! In all of my searching to solve this, use of query never came up. I do realize that I have left off a column for homework hours as my wife asked about it this morning "um.. yeah honey, of course I'm counting homework!" (I think one of my offspring ratted me off tbh)
Can this additional column be added to the query? I added sample data to the sheet your created as an example to work with.
We can tweak as necessary (get only the last 7 days? Just a specific week number? Etc.)
The only tweak relating to time I can think of needing is to collect the data as it was entered and not the actual date - say 1st to 15th as one period and 16th to end of the month as the other. This should cover any time entered outside of it's expected time frame in the case of a late entry which will happen.
Thank you so much for your help!
Adding the homework column is as simple as including column I to the QUERY and adding "SUM(I)" to the query language:
=QUERY(F6:I, "Select F, G, SUM(H), SUM(I) where F is not null group by F, G label SUM(H) '', SUM(I) '' ",0)
For the time part, as long as you have a separate date column (rather than the timestamp) and you reference that in the QUERY it will use the date you provide it to aggregate with.
Original Poster John J9455 marked this as an answer
May 11, 2021
Best,
John
You're welcome. QUERY is one of the most powerful functions that Sheets has. Lots of options and, when combined with some other tricks, you can do a lot of things.
Here is the QUERY help page and a more detailed information* page. There is also an excellent introduction and video here.
*Some of the samples in this link use the column data headers ("salary", "department", etc.) instead of column designation (or numbers) which is not supported, most of the other material is applicable though. Use column designators (A, B, C, ...) when you provide QUERY with a direct range (A1:Z, ...). Use column numbers (Col1, Col2, ...) when the range you give QUERY is from a formula (IMPORTRANGE or other).