Help with formula using Sumproduct, Sign, and Index

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
60
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
Joined
Aug 8, 2002
Messages
60
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.

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


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
Joined
Feb 13, 2004
Messages
4,814
Mark

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

Tony
 

mArkcpp

Board Regular
Joined
Aug 8, 2002
Messages
60

ADVERTISEMENT

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
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Aug 8, 2002
Messages
60

ADVERTISEMENT

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
Joined
Mar 10, 2004
Messages
19,379
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
Joined
Aug 8, 2002
Messages
60
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
Joined
Mar 10, 2004
Messages
19,379
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,053
Messages
5,545,738
Members
410,703
Latest member
yaronjoseph
Top