[SOLVED] SUMPRODUCT with DYNAMIC range

Hi All

I have the following formula which is copied down from cell A4 but now the INDEX range number of rows will change every week. Can this formula be modified to find the last row?

Please Login or Register  to view this content.

Thanks

Hi,

I would use named ranges in this instance.

Column B's named range might look like this..

=$B$2:INDEX($B$2:$B$50000,MATCH(9.999E307,$B$2:$B$50000,1))

Column C and F like so..

=$C$2:INDEX($C$2:$C$50000,MATCH(9.999E307,$B$2:$B$50000,1))
=$F$2:INDEX($F$2:$F$50000,MATCH(9.999E307,$B$2:$B$50000,1))

note the use of column B in all formulas to make sure the number of rows in each dynamic range is the same to suit the SUMPRODUCT formula.

You could also use the CountA version shown by BMV above, but if there are blanks within the range, it will fail.

but if there are string in the B column it will fail also

True enough!!

I would use sumproduct here as it's known to be a little faster than an array function. Given the size of the ranges, warp785 might notice the calculation speed. I guess it's only around 10%, but every little helps?!

Originally Posted by sweep

I would use sumproduct here as it's known to be a little faster than an array function.

in the case of Sum and product is main operation but here MAX will return only one and sumproduct will replace CSE and
=INDEX(MAX((D10:D15=D11)*C10:C15);) or =LOOKUP(9E+36;MAX((D10:D15=D11)*C10:C15)) also work without CSE.
and _{=AGGREGATE(14;6;C10:C15/(D10:D15=D11);1)} available for 2013

Originally Posted by hrlngrv

The last nonblank row in col B would be given by =MATCH(1,INDEX(0/NOT(ISBLANK(B:B)),0)).

- it's worse then use 100 000 in first formula because this operation will calculate 1 048 576 cells. and

Please Login or Register  to view this content.

- much shorter quicker and with the same problem.

Originally Posted by hrlngrv

The last nonblank row in col B would be given by =MATCH(1,INDEX(0/NOT(ISBLANK(B:B)),0)).

Originally Posted by BMV

. . .
- it's worse then use 100 000 in first formula because this operation will calculate 1 048 576 cells. and

Please Login or Register  to view this content.

- much shorter quicker and with the same problem.

In a new workbook, enter

A1: 0
B1: x
A2: =CHOOSE(A1,MATCH(1,INDEX(0/NOT(ISBLANK(B:B)),0)),LOOKUP(1,0/(B:B<>""),ROW(B:B)),MATCH(1,INDEX(0/NOT(ISBLANK(prune(B:B))),0)),lastrow(B:B))

Insert a general VBA module, and enter this code.

Please Login or Register  to view this content.

Now run prof. On my system (a VM with not a lot of RAM) I get the following results in the Immediate window.

1	08:06:32	08:07:00	0.1399999982	2253
2	08:07:01	08:07:24	0.1150000017	2193
3	08:07:25	08:07:25	0.0000000000	2380
4	08:07:26	08:07:26	0.0000000000	2386
-	--------	--------	------------	----
3	08:07:50	08:08:00	0.0049999999	23279
4	08:08:01	08:08:07	0.0030000001	22815

3 and 4 each took less than a second for 200 iterations, so I increased MAXITER to 2000 and reran them. This does show that your lookup formula is about 18% faster than my match formula, but using a little VBA chops orders of magnitude off the calculation time. Note: <>"" isn't the same thing as blank. If the last cell with visible contents was B99 and there were IFERROR(...,"") formulas in B100:B200 all returning "", your lookup formula would return 99 while my match formula would return 200.