Formula speed required.

[Craig1]

Hi All,
I have a sumproduct formula and because I want it to check the whole column it takes quite a while to update.
The formula is:-

=SUMPRODUCT(--(L$L$11:$L$62056="Black"),--($A$11:$A$62056>=$G$44),--($A$11:$A$62056<=$H$44))

Instead of the 62056 rows, is there a quicker way of looking at the column to the end of the data, the data may only be 2 rows down so it doesn't need to look all the way down or up.

Craig.

 

[RSXchin]

Hi All,
I have a sumproduct formula and because I want it to check the whole column it takes quite a while to update.
The formula is:-

=SUMPRODUCT(--(L$L$11:$L$62056="Black"),--($A$11:$A$62056>=$G$44),--($A$11:$A$62056<=$H$44))

Instead of the 62056 rows, is there a quicker way of looking at the column to the end of the data, the data may only be 2 rows down so it doesn't need to look all the way down or up.

Craig.

Is there a reason you say L$ twice?

 

[Craig1]

Thanks for that Armando I might consider that if all else fails.

Michael,
I will try the code in a little while.

Thanks for the help.

Craig.

 

[Craig1]

rsxchin, just a typing error

Craig

 

[Michael M]

ooops, typo....too much easter bunny.
you will need to change the formula to access the sheet name

Sub fastsum()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lr = Cells(Rows.Count, "L").End(xlUp).Row
    For r = lr To 11 Step -1
        If Range("L" & r).Value <> "" Then
            Range("A" & r).Formula = "=SUMPRODUCT(--(L$11:$L$" & lr & "=""Black""),--($A$11:$A$" & lr & ">=$G$44),--($A$11:$A$" & lr & "<=$H$44))"
        End If
    Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Craig1]

Biff,
Thanks also for your reply, I will try this as well. But I think excel will have to wait a couple of days while I enjoy the rest of Easter with the kids.

All,
Thanks for all the help tonight and I will let you know how I get on.

Craig.