[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?

=SUMPRODUCT(MAX((EUT!$B$2:$B$10000=$C4)*(EUT!$C$2:$C$10000=$U$3)*(EUT!$F$2:$F$10000)))

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

=LOOKUP(2;1/(B:B<>"");ROW(B:B))

- 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

=LOOKUP(2;1/(B:B<>"");ROW(B:B))

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

Option Explicit


Function prune(rng As Range) As Range
  Set prune = Intersect(rng, rng.Parent.UsedRange)
End Function


Function lastrow(rng As Range) As Long
  If rng.Columns.Count = 1 Then
    Set rng = Intersect(rng, rng.Parent.UsedRange)
    Set rng = rng.Rows(rng.Rows.Count)
    If IsEmpty(rng.Value2) Then Set rng = rng.End(xlUp)
    lastrow = rng.Row
  End If
End Function


Sub prof()
  Const MAXITER As Long = 200
  Const MAXBUMP As Long = 20

  Dim dt As Date, j As Long, k As Long

  For j = 1 To 4
    '# following 2 statements ensure a clean slate for each expression
    [B2:B1048576].ClearContents
    ThisWorkbook.Save
    dt = Now
    [A1].Value2 = j
    For k = 1 To MAXITER
      [B1].Offset([A2].Value2 + Application.WorksheetFunction.RandBetween(1, MAXBUMP), 0).Value2 = "x"
    Next k
    Debug.Print j, Format(dt, "hh:mm:ss"), Format(Now, "hh:mm:ss"), Format((Now - dt) * 86400# / MAXITER, "0.0000000000"), [A2].Value2
  Next j

  [A1].Value2 = 0
  [B2:B1048576].ClearContents
  ThisWorkbook.Save

End Sub

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.