Calculate duration of cells but within a certain range

[snowkin000]

Hello! 

I've been trying out different formulas but I can't get it to work. So as per below data, column B is a set of duration timecodes. I want to calculate the duration of the timecodes that only fall between C to T (coloured). And the formula won't repeat itself on the same set of C to T. Does anyone know how? thank you!

[#2 · Accepted solution]

@snowkin000 can you please clarify. do you want another column that only shows those values and the other values are blank, or just a list of those values, or a list of the sums of each set, or the sum of all those periods, or something else?
and will C and T always alternate or could you have C C C T T C T T C and if you can then the area of interest would be from 1st of any multiple Cs to 1st of any multiple Ts?
the first and last could be:
formula for column C to only show those values:

=IFERROR(IF(XMATCH("C",$A$1:$A2,,-1)>XMATCH("T",$A$1:$A2,,-1),B2,""),"")

formula to sum all those values:

=LET(toggle,A2:A19,times,B2:B19,
     REDUCE(0,SEQUENCE(ROWS(toggle)),LAMBDA(p,q,
        IF(IFERROR(XMATCH("C",TAKE(toggle,q),,-1)>XMATCH("T",TAKE(toggle,q),,-1),FALSE),
           p+MID(INDEX(times,q),3,9),
           p))))

and btw I used MID( xxx, 3, 9) because excel didn't recognize that time format (line 4) so if the sum isn't correct it could be that there is some confusion on what is meant by that formatting.

[#3]

@snowkin000 

If I understand the request, you could make the data whole by using SCAN and then SUM the results of FILTER:

=LET(
    filldown, LAMBDA(acc, v, IF(v = "", acc, v)),
    filled, SCAN("", Demo[Code], filldown),
    SUM(FILTER(Demo[Duration], filled = "C", 0))
)

[#4]

@m_tarler Hello! thank you for your solutions. i have been trying to reply but the system keeps giving me overflow error.

I tried your first option and was able to achieve what i was looking for! thank you so much!

[#5]

@Patrick2788 

thank you for your solution! i did try it out but i can't seem to get it right - the name error. 

but i tried out other suggestions and managed to get what i wanted!