# Help with formula using Sumproduct, Sign, and Index

#### mArkcpp

##### Board Regular
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.

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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### acw

##### MrExcel MVP
Mark

Try

Code:
``=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

##### Board Regular
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:

Code:
``=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.

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

Code:
``=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

##### MrExcel MVP
Mark

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

Tony

#### mArkcpp

##### Board Regular

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

##### MrExcel MVP
Mark

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

Tony

#### mArkcpp

##### Board Regular

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

##### MrExcel MVP
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

##### Board Regular
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:

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

Thanks again.

Mark

#### Domenic

##### MrExcel MVP
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?

Replies
5
Views
79
Replies
19
Views
662
Replies
6
Views
217
Replies
6
Views
156
Replies
0
Views
480

### Forum statistics

1,141,924
Messages
5,709,375
Members
421,633
Latest member
Ubergribbler ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back