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.

 

[Peter_SSs]

1. Why have you used the number 62,056 in the formula?

2. Is there some sort of maximum you could estimate for the number of rows of data? Even if you could say no more than 5,000 rows that would help.

3. What version of Excel are you using?

 

[Michael M]

Hi Craig
Have you considered putting it in a macro to dynamically select the last row ??
Which column does the SUMPRODUCT formula reside in ??

 

[Craig1]

Hi guys,
Thanks for the quick replies.

Peter -
1, I used 62,056 because that was the actual number when I scrolled down to the bottom of the page, nothing more and nothing less.
2, It's a new database and will grow very quickly, in a trial we were up to 1250 rows in a month, so the setting of 5,000 would be quickly reached.
3, At work I use excel 2003 and at home i use 2007 but the database is for work so it will have to be 2003.

Michael -
No I haven't considered a macro but more than willing to learn. The columns vary depending what it is looking at but the example I posted was in column L & A.

Craig.

 

[Craig1]

Sorry Michael I misread your post, the sumproduct formula resides in column A in another woksheet in the same workbook.

Craig.

 

[Peter_SSs]

1. And do you have a lot of those SUMPRODUCT formulas? I found that even with 60,000+ rows of data the formula calculated quite quickly.

2. Have you considered setting Calculation to Manual (Tools|Options...|Calculation tab) and then just calculate the sheet manually when you need to?

 

[Armando Montes]

You might try using a helper column for true/false and countif instead of sumproduct.

 

[Craig1]

Hi Peter,
33 sumproducts along with a lot of VBA. The workbook is 5.3MB.
And yes I already update manually.

Craig.

 

[Michael M]

Or try

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("M" & 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

 

[T. Valko]

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.

You can use dynamic named ranges.


Assuming:

• Col L contains text
• Col A contains numbers (or dates?) only
• data is entered as a contiguous block

Create the named ranges:

• Goto the menu Insert>Name>Define
• Name: Dates
• Refers to: =$A$11:INDEX($A$11:$A$65536,MATCH(1E100,$A$11:$A$65536))
• Name: Color
• Refers to: =$L$11:INDEX($L$11:$L$65536,MATCH(1E100,$A$11:$A$65536))

Then the formula becomes:

=SUMPRODUCT(--(Color="Black"),--(Dates>=$G$44),--(Dates<=$H$44))