this is a bit of weird request but I'm trying to develop a formula in google sheets that does the following... Given an example sheet (imagine abritrary dimensions):

I want to take the values from all rows in a given column, take the difference between all comma separated pairs (with /'s or cells separating pairs), and then sum all those differences. The idea is that the pairs represent time work started, and time work ended, and I want a total sum of time worked over multiple days (rows) for multiple tasks (columns).

My attempt thus far for a single column is as follows:

=IFERROR(SUM(INDEX(SPLIT(INDEX(SPLIT(TEXTJOIN("/",TRUE,B5:B), "/"), 0,1), ","), 0, 2) 
             - INDEX(SPLIT(INDEX(SPLIT(TEXTJOIN("/,",TRUE,B5:B), "/"), 0,1), ","), 0,1)

             IFERROR(INDEX(SPLIT(INDEX(SPLIT(TEXTJOIN("/",TRUE,B5:B), "/"), 0,2), ","), 0, 2) 
              - INDEX(SPLIT(INDEX(SPLIT(TEXTJOIN("/",TRUE,B5:B), "/"), 0,2), ","), 0, 1))
            ......

Repeated over and over with the first index call incrementing by one each time. Obviously this is super inefficient, and doesn't scale very well.

Ideally I'd like it to repeat for any N number of comma separated pairs, and I'd also like it to automatically populated an entire row (perhaps with an ArrayFormula()?)

CodePudding user response：

delete everything from row 4 and try in B4:

=ARRAYFORMULA(SUBSTITUTE(TEXT(IFNA(VLOOKUP(COLUMN(B4:4), 
 QUERY(QUERY(VALUE(SPLIT(FLATTEN(SPLIT(FLATTEN(REGEXREPLACE(TRIM(QUERY(
 IF(B5:20="",,"/"&B5:20),,9^9)), "(/)", "$1"&COLUMN(B5:20)&"×")), "/")), "×,")), 
 "select Col1,Col3-Col2 where Col2<>0"), 
 "select Col1,sum(Col2) group by Col1 label sum(Col2)''"), 2, 0)), 
 "[h]:mm"), "0:00", ))