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

 

[acw]

Mark

Try

=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)),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)),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($5:$5,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()-13)),""))

Array entered.


Tony

 

[mArkcpp]

Thanks Tony. You've helped me in the past, and I certainly appreciate it. I came up with a temporary fix using the following formula:

=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)),INDEX($A$5:$L$5,MATCH($A12&$A$8&$A$9,$A$4:$L$4&$A$1:$L$1&$A$2:$L$2,0))))

This "cheats" by looking at the last value once ROW()-12 >sumproduct... evaluates true, searching for that value in the range, then indexing the column that it finds the value in to return the value in the row I'm interested in. I prefer your code since it accomplishes what I wanted following the logic that I described.

I have one more question.

Assume I add Row 6 to read:

Row 6: -5 9, 6, 6, 24, 5, 2, 7, 1, -1, 5, 4


I was using the following formla to sum certain data (for instance, for the above example, the formula would add any data in row 6 with a "D" or a "1").

=IF(A9<>"",SUMPRODUCT(--(A1:L1=A8),--(A2:L2="D"),A6:L6)+SUMPRODUCT(--(A1:L1=A8),--(A2:L2=A9),A6:L6),"")

Let's assume Row 3 is changed to read:

Row 3: P, D, D, 1, 1, "2,3", 2, 2, P, 1,

(Note that "2,3" is an entry in one cell.)

For a value in A8=P-150 and A9=2, can the above formula be modified to still pull and add the data in Row corresonding to the cell with "2,3"? The result should be 22.

Essentially what I'm doing here is accomodating a scenario where that column would need to be included in any calcs/formulas for both a "2" or a "3" entry. This means that if cell A9=3, the result should be 14. It is possible that the this cell could have "2,3,4,5" or something similar to where the corresponding value would then need to be accounted for in system 2,3,4, and/or 5.

If this can't be done, I'm open for suggestions on an alternative method to accomplish what I'm looking forward. The only thing that comes to my mind is a simple macro using a loop and trim function.

Thanks for any help......we'll see if I can stump you with this one.

Mark

 

[acw]

Mark

Must be missing something. The sumproduct formula doesn't reference row 3 so there should not be any impact.

Tony

 

[mArkcpp]

oops....I meant to say that Row 2 was changed as follows:

Row 2: P, D, D, 1, 1, "2,3", 2, 2, P, 1,

(Note that "2,3" is an entry in one cell.)

Any ideas?

Thanks,
Mark

 

[acw]

Mark

How about

=IF(A9<>"",SUMPRODUCT(--(A1:L1=A8),--(A2:L2="D"),A6:L6)+SUMPRODUCT(--(A1:L1=A8),--(ISNUMBER(SEARCH(A9,A2:L2))),A6:L6),"")


Tony

 

[mArkcpp]

Tony, thanks again for the help. (sorry for the delay, I've been on vacation.)

The only problem I see with this is that the value gets counted if it finds the search criteria in any digits between the commas, i.e. if in the cell next to the one with "2,3", 23 was the value and the search criteria was "3", the column with 23 would have its value added.

I can work around this and specify that these identifiers be single digit numbers or letters.

Thanks again for the help.

Mark

 

[Domenic]

The only problem I see with this is that the value gets counted if it finds the search criteria in any digits between the commas, i.e. if in the cell next to the one with "2,3", 23 was the value and the search criteria was "3", the column with 23 would have its value added.

If I understand you correctly, Tony's formula can be modified as follows...

=SUM(IF(A1:L1=A8,IF(ISNUMBER(SEARCH(","&{2;"D"}&",",","&A2:L2&",")),A6:L6)))

or

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

...where A9 contains 2 and A10 contains D. Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[mArkcpp]

Thanks for the help. This isn't quite working.....sort of.

The first formula works fine, and if I change the search criteria from 2 to 1 or 23, it gives the correct results.

The second formula is not quite working. It is grabbing more data than it should. For instance, if I change the search criteria to 23. I get a result of -3 whiich looks like it is grabbing the data in the first column from Row 6 (-5) and then only the data in column G (2) for a total of -3.

One more piece of information, the "D" search criteria would never be changed by a user, so I would like it to be hard-keyed into the formula. Ideally, the formula would be the first one you showed but with A9 where you have "2". When I try this, I am told the formula has an error. any ideas?

This is what I thought might work:

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

Thanks again.

Mark

 

[Domenic]

That's because an array constant {...} does not accept cell references. With regards to the search criteria "D", will it always be by itself in a cell or can there be other characters included?