Create sum of tasks and time spent in Google Sheets

Question

I have a google sheet with two columns with dropdowns, one has a type of task performed, the second has how long it took for the task. I want to create a sum of the number of task types for the day and a sum of the time spent. The table looks roughly like this:

Task Description	Task Type	Time Spent
Task Description	Web Development	15 Min
Task Description	Network Admin	1.5 Hours
Task Description	Helpdesk	30 Min
Task Description	Network Admin	30 Min
Task Description	Helpdesk	15 Min

What I want below is something to tell me how much time was spent for each task type, for instance something that would say Web Development 15 Min, Network Admin 2 Hours, Helpdesk 45 Mins so that I can then take the total amount of time spent and break it into a percentage of the time period worked. I'm not very good with Google Sheets and dropdowns so I'm really unfamiliar with working with that kind of data.

Answer 1

Here's one approach you may test out:

=let(Λ,map(unique(tocol(B2:B,1)),lambda(Σ,{Σ,sum(ifna(filter(--left(C:C,find(" ",C:C))*if(iferror(search("hour",C:C)),60,1),B:B=Σ)))})),
     hstack(index(Λ,,1),let(Ξ,index(Λ,,2),index(if(Ξ>60,round(Ξ/60,2)&" hour(s)",Ξ&" min")))))

Answer 2

Here's another solution:

=MAP(UNIQUE(TOCOL(B2:B,1)),
   LAMBDA(task,{task,
     REGEXREPLACE(
       TEXT(
         SUM(FILTER(
               1/{24,1440}*REGEXEXTRACT(C2:C,"(?:([\d.]+) Hours?)|(?:([\d.]+) Mins?)"),
               task=B2:B)),
         "h \hour\s m \min\s"),
       " ?\b0 \w+ ?",)}))

Explanation

Let duration be a duration in the format # hours, # mins or # hours # mins where # represents a number. The following (array)formula converts this human-readable duration to a duration that Google Sheets can understand

SUM(1/{24,1440}*REGEXEXTRACT(duration,"(?:([\d.]+) Hours?)|(?:([\d.]+) Mins?)"))

If duration is an array of durations, the formula converts all of them to a format that Sheets can understand and sums them.

Now let duration_gs be a duration in a format that Sheets understands, the following formula converts it to a human-readable duration

TEXT(duration_gs,"h \hour\s m \min\s")

This process may end up generating something like 0 hours # mins or # hours 0 mins, so we deal with it with the following regex

REGEXREPLACE(duration," ?\b0 \w+ ?",)