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

 

[pgc01]

Hi
Welcome to the board

Try:

B1:INDEX(B1:$B$20,MATCH(1,(B1:$B$20="open")*(B2:$B$21<>"open"),0))

Remark: don't forget to confirm the formula with CSE

 

[stepan1987]

Thanks, pgc01, unfortunately after putting this part in it gives me #N/A
I think I will descibe the whole situation in a new thread, mb something else is wrong..