excelsubtractionexcel-indirect

Return the difference, in the first cell, between two cells with a certain value

Goal: Return the date difference shown in cell C5 to cell C4, and the date difference shown in C6 to cell C5 etc.

Currently I am getting the date difference of cells with the letter "s" but it is being shown in the next corresponding row with "s"

Formula currently in C2 then dragged down is as shown below:

=IFERROR(IF(B2="s",A2-INDIRECT("A"&LOOKUP(2,1/($B$1:$B1="s"),ROW($B$1:$B1))),"-"),"n/a")

and this is how my sheet currently looks

Current Sheet

Thanks in advance.

Solution

  • The following formula will look for the next "s", as long as it is within 1000 rows of the current one. Put this in C2:

     =IF(B2="s",INDEX(A3:A1002,MATCH("s",B3:B1002,0))-A2,"-")

    Then populate down

    To avoid the last "s" giving a result of #N/A you can wrap the whole thing in an IFERROR statement:

     =IFERROR(IF(B2="s",INDEX(A3:A1002,MATCH("s",B3:B1002,0))-A2,"-"),"-")