Summing hours spent on a subject in Google Sheets' "Assignment Tracker"

Question

Hi everyone!

I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject. Is there a way to automatically sum the number of hours associated with each subject as I input them into the master sheet above?

Thanks! And please let me know if you require any clarification.

Answer 1

• in minutes:

  =QUERY({Assignments!B5:B, 
   ARRAYFORMULA(IFERROR(IF(INDEX(SPLIT(Assignments!E5:E, " "), , 2)="hrs",
   INDEX(SPLIT(Assignments!E5:E, " "), , 1)*60, 
   INDEX(SPLIT(Assignments!E5:E, " "), , 1))))},
   "select Col1,sum(Col2),'min' 
    where Col1 is not null 
    group by Col1 
    label sum(Col2)'','min'''")

  

• in hours:

  =QUERY({Assignments!B5:B, 
   ARRAYFORMULA(IFERROR(IF(INDEX(SPLIT(Assignments!E5:E, " "), , 2)="min", 
   INDEX(SPLIT(Assignments!E5:E, " "), , 1)/60, 
   INDEX(SPLIT(Assignments!E5:E, " "), , 1))))},
   "select Col1,sum(Col2),'hrs' 
    where Col1 is not null 
    group by Col1 
    label sum(Col2)'','hrs'''")