Help with formula using Sumproduct, Sign, and Index

[mArkcpp]

Hello. I have a question building on some of the help I've received here a month or so ago.

I have some data that looks something like:

Row 1: P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-150, P-100, P-100, P-100
Row 2: , P, D, D, 1, 1, 2, 2, 2, , P, 1
Row 3: U, U, U, U, D, 125, U, D, 150, U, U, 50
Row 4: 100, 175, 166, 160, 154, 130, 160, 158, 151, 25, 75, 54
Row 5: 105, 166, 160, 154, 130, 125, 158, 151, 150, 26, 70, 50


Cell A8 = P-150
Cell A9 = 1

Formula in Cell A13 through A30 =

=IF((ROW() - 12)>SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,"",INDEX($4:$4,SMALL(IF(($A$1:$L$1=$A$8)*SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),COLUMN($A$2:$L$2),""),ROW()-12)))

Results look like:

100
175
166
160
154
130
(now a bunch of empty cells)

This works exactly like I had hoped; however, now I would like to complicate the formula a little more. I would like one more number to be pulled after the 130. I would like that number to be the number in row 5 corresponding to the last value pulled (i.e. 130 was last number, and 125 is in the same column one row down, so pull 125.

The results would read:
100
175
166
160
154
130
125

Can anyone help with this new task?

I started adjusting the formula to this point:

=IF((ROW() - 12)>SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,"",IF((ROW() - 12)<SUMPRODUCT(SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),--($A$1:$L$1=$A$8))+1,INDEX($4:$4,SMALL(IF(($A$1:$L$1=$A$8)*SIGN(($A$2:$L$2=$A$9)+($A$2:$L$2="")+($A$2:$L$2="P")+($A$2:$L$2="D")),COLUMN($A$2:$L$2),""),ROW()-12)),"new number here"))

I've replaced "new number here" with a few tries but have not had any success pulling the formula.

I can send the excel file if needed.

Thanks for any help.

Mark

 

[mArkcpp]

"D" would always be by itself.

 

[Domenic]

Try...

=SUM(IF(A1:L1=A8,IF((A2:L2="D")+(ISNUMBER(SEARCH(","&A9&",",","&A2:L2&","))),A6:L6)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[mArkcpp]

Domenic, this appears to work perfect.

Thanks to you and Tony for the help!! I certainly appreciate it.

Mark