How to return the address of the cell first one in the column to contain a specific value

[stepan1987]

Hi guys,

I need to figure out how to specify one of the conditions for the SUMPRODUCT formula. I have a column like the one below:

column B
CLOSE
CLOSE
OPEN
OPEN
CLOSE
OPEN
OPEN
CLOSE
CLOSE
OPEN
CLOSE+OPEN
OPEN

I need a formula that would lock me in the range down to the last cell containing a value OPEN after OPEN/CLOSE/OPEN+CLOSE when I extend the formula down

The answer would be:
B1:B4
B2:B4
B3:B4
B4:B4
B5:B7
B6:B7
B7:B7
B8:B10
B9:B10
B10:B10
B11:B12
B12:B12


Could you please help me out? I think my brain is wasted already..