Forum Discussion
Calculate duration of cells but within a certain range
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.
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))
)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!
