Find the previous Row with a specific value at in column A

Solution 1:

The easy way to do this is to cheat and use a mixed absolute/relative formula. This is an array formula (enter with CTRL+SHIFT+ENTER) entered into cell B4 but it could go anywhere in row 4. It will return the row number of the one marked S.

=MAX(IF($A$1:A4="S",ROW($A$1:A4)))

When copied down, the second part of the reference B4 and A4 will increase. This ensures that you get the row with the largest match that is above the current row. You can enter those formulas more quickly using F4 after typing/selecting the relevant range. This will cycle the dollar signs through all the choices.

Picture of ranges

Used to replace your formulas

After reading the question a bit (and based on the edit by @SteveTaylor), it seems your use for this is to update your formulas. You can use the row that is returned from above along with INDEX to get ranges of data to sum. I see 2 formulas that can be replaced:

• Total calculation for each labeled row of data. In this case, the subtotal row above can be referenced dynamically.
• Total calcualtion for the substotal row. In this case the values to sum from above can be reference dynamically.

For the single row data, you can use the formula, starting in F3 as an array formula. Note that I switched over to using SUMPRODUCT which makes it much easier to go to more than 2 columns.

=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)

For the total row formula, you can use, starting in F11, again array formula:

=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))

If you want one formula to rule them all! then you can combine these into a nested IF based on the value in column A. Here is said array formula, starting in F2 which can be copied down.

=IF(
  A2="S", 
  SUM(D2:E2), 
    IF(A2="T", 
      SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))), 
      C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))

This formula does not differentiate between a blank row and a "data" row. It currently returns 0 for the spacer row which is fine.

Picture of results of and formulas for two blocks of your data.