Forum Discussion
Calculate duration of cells but within a certain range
- Sep 19, 2024
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.
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.
- snowkin000Sep 22, 2024Copper Contributor
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!