Forum Discussion

snowkin000's avatar
snowkin000
Copper Contributor
Sep 19, 2024

Calculate duration of cells but within a certain range

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!

  • 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.

  • m_tarler's avatar
    m_tarler
    Steel Contributor

    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's avatar
      snowkin000
      Copper 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!

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    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))
    )
    • snowkin000's avatar
      snowkin000
      Copper Contributor

      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!

       

Resources