3

This is a follow-up to these questions and information.

Thank you Jacob and Kowser for the formulae you shared many years ago! The information you shared is almost exactly what I need to simplify a large task I have of keeping running totals of time spent on various projects by many students.

I have a follow-up question regarding this slightly-modified Google Sheet.

I have added a new column which is labeled "Project"

My goal is to see the sum of hours each person spent on each project. Thus, because John and Susan worked on three different projects, I would like to see the hours John spent working on Apple, Fruit, and Helium; and the hours Susan spent on Cantaloupe. I still need the hours the other people dedicated to their respective projects. Because they only worked on one project each, it seems easier. It would be great to have the name of each project visible with the totals. That should be easy to accomplish.

The data in the sheet will be ever-changing and populated from Google Forms entries. The number of rows will be over 6000 each year, thus your help is a significant time saver for me!

Thank you very much!

1 Answer 1

0

This can be accomplished by using the QUERY formula, like so:

=QUERY(A:C; "SELECT A, C, SUM(B) WHERE A IS NOT NULL GROUP BY C, A LABEL SUM(B) 'Hours Total'")

which gives this result: enter image description here

The formula reads as follows:

  • select the range you want to process
  • make sure to only process those rows that have a value (IS NOT NULL)
  • Sum the hours and group by the project. You can also group by the name first and then the project.
  • lastly, add a customised label to the header of column B

I've added some extra data, to show the difference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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