Using a reference in the array sizing

[weeble33]

Hello,

I have this formula:

=SUMPRODUCT((LEFT($B$4:$B$897,2)="CD")*($P$4:$P$897))

Now, given the nature of of the formula, the whole thing will error out if a single cell in B is an error. For what I'm doing, my list (B4:B897) changes frequently in size. What I'd like to do is in the array statement, instead of manually changing the 897 to 856 or whatever the new size is, I want to point it to a cell where I can enter in the number 856 and thus make the array (B4:B856).

I've tried doing something like (B4:B&$A$1). Where A1 would contain the 856 or whatever length it is. I know I can just do Indirect($A$1), but it creates a little more work on the people using this report to input $B$4:$B$856 into a cell vs just 856. Also because like the formula above, there are several formulas calling to various columns other than just B. Finally, I'd like to avoid suggestions on changing the above formula to something more accommodating, simply because there are several formulas I use that have the same problem and they're not all written with sumproduct. So yea, somehow getting ($B$4:$B(&INDIRECT($A$1))) to work. hopefully I'm just missing the proper syntax here.

[Andrew-R]

Try either:

=SUMPRODUCT((LEFT(INDIRECT("$B$4:$B$" & A1),2)="CD")*(INDIRECT("$P$4:$P$" & A1)))

Or:

=SUMPRODUCT((LEFT($B$4:OFFSET($B$4,A1-4,0),2)="CD")*(P$4:OFFSET($P$4,A1-4,0))

The 2nd one is probably the better option, as it's non-volatile.

[Tony Valko]

OFFSET is volatile.

[Tony Valko]

Here's another one...

A1 = 897

=SUMIF(B4:INDEX(B:B,A1),"CD*",P4:INDEX(P:P,A1))

[weeble33]

The project got put on hold for awhile, but now it's back and Tony, your solution worked wonderfully. Changing fx(b1:b247) to fx(b1:index(b:b,A1)) coupled with a counta() in A1 works great. However, it doesn't work for the Row() function, or maybe I'm just not doing it right. Row($3:$247) to Row($3:index(???,A1)) I tried just putting in a random column, like B:B, but that doesn't work. Any ideas?

[Tony Valko]

Not sure I follow you on the ROW(...) function.

What are you wanting to do with it?

[weeble33]

Well, so I have a formula:

=IFERROR(INDEX(Sheet2!$X$3:$X$2000,SMALL(IF($B$2=Sheet2!$X$3:$X$2000,ROW($3:$2000)-2,IF($B$2=Sheet2!$Y$3:$Y$2000,ROW($3:$2000)-2,"")),ROW(L3)-2)),"")

I can't have the index arrays change, and not the row arrays as well, otherwise you end up with arrays of different sizes and it returns nothing. Even if there is a simpler way to write this formula, I have others that make use of the Index, Small, Row combo to resize and filter lists.

So if I can make Index($B$3:$B$2000,...,....) into Index($B$3:Index(B:B,$A$1),...,...)
What's the corresponding way to do that with Row($3:$2000) into Row($3:?????) so that is also points to A1 for its variable array sizing.

[Tony Valko]

I'm not sre what you're wanting that formula to do but if you index the entire column then you don't need to use an "offset correction".

=IFERROR(INDEX(Sheet2!$X:$X,SMALL(IF(Sheet2!$X$3:$X$2000=$B$2,ROW(Sheet2!X$3:X$2000),IF(Sheet2!$Y$3:$Y$2000=$B$2,ROW(Sheet2!Y$3:Y$2000))),ROWS(L$3:L3))),"")

Still needs array entry.

It looks like you want to return every instance of B2 from column X where either column X and/or Y = B2.

[weeble33]

You are mostly right (and I forgot the array entry when putting it in here, not the actual spreadsheet). This particular formula returns the text in Col X if B2 is found in either x or y. This formula works fine. I want to modify it as I stated before. Also, this isn't the only formula, it was just an example.

I could index the entire column, but I'm already stressing the workbook enough with calculation time. Having several thousands of cells with index functions searching entire columns has cause long calculation times in past workbooks. I will certainly try it with my current one to see if the calc time difference is negligible, but I need to keep calculations down as much as possible.

So, do you not know a way to turn Row(3:3000) into Row(3:$A$1)?

[Tony Valko]

You can do it wrapped in an INDIRECT function but considering the new info you provided it probably isn't a good thing to do since it's volatile and will recalculate on every calculation.

Like this:

ROW(INDIRECT("3:"&A1))

So, if A1 = 3000 then it evaluates to:

ROW($3:$3000)