Optimise VBA Code for SUMPRODUCT analysis

I would welcome any thoughts, ideas or suggestions as to how I can optimise the VBA code listed below.

This is running on Windows XP/Excel 2003 on a Toshiba Portege laptop with 512 Mb RAM

I am trying to analyse the contents of a worksheet. To that end, I have created a matrix on another sheet. This matrix is 117 columns wide by 232 rows deep and is/was populated by SUMPRODUCT formulae. The SUMPRODUCT formula works and gives the correct results but the sheer volume made the worksheet/workbook unusable.

To overcome the impact on the rest of the workbook, I had copied the formulae and pasted the values back. I now want to revisit the calculation and add another criteria.

Initially, I just edited the formula and copied and pasted it to the rest of the matrix ... but the workbook appeared to hang. I decided then to use VBA to insert the formulae a column at a time and copy and paste the values. However, this process is taking around 7 to 9 seconds per column. What is worse, if I just let it run, it seems to hang.

I can nurse the code through and it works but I could do without sitting for 15 minutes or so every time I run it. I have other sheets I will want to do similar analyses with so it's not just idle interest.

Sub Analyse_AR()

Dim calcState
Dim lLRow As Long
Dim lLCol As Long
Dim lLC As Long

If ActiveSheet.Name <> "Analysis (ar)" Then
    MsgBox "Select Analysis (ar) Sheet"
    Exit Sub
End If

Application.ScreenUpdating = False
calcState = Application.Calculation
Application.Calculation = xlCalculationManual

lLRow = Range("B" & Rows.Count).End(xlUp).Row - 1
lLCol = Cells(1, Columns.Count).End(xlToLeft).Column - 1
    
For lLC = 2 To lLCol
    Debug.Print lLC; "  start "; Now()
    With Range(Cells(3, lLC), Cells(lLRow, lLC))
        .FormulaR1C1 = _
            "=SUMPRODUCT(('No 1 '!R2C7:R6600C7=RC1)*('No 1 '!R2C3:R6600C3=R1C)*('No 1 '!R2C11:R6600C11=""ar""))"
        Application.Calculate
        .Copy
        .PasteSpecial _
            Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
    Debug.Print lLC; "  end "; Now()
Next 'lLC

Application.Calculation = calcState
Application.ScreenUpdating = True

End Sub

Thanks in advance for any help or guidance you can offer.

Regards

I assume you can't use SUMIF with a key column instead of the SUMPRODUCT?

@romperstomper: well, I can do pretty much what I like with the data. I was given a raw data file to process and i've made several (dozen) copies as I have worked through it. The analysis is my way of presenting to my customer what has been done, what has not been done and why. They aren't interested in the detail, just the end result ... but I want the detail.

What would a key column look like and how would I then use it with SUMIF?

Regards

sensitive data can be replaced with anything (digits with other digits etc.) else, I will offer to use arrays. Three to five rows will be enough.

Rather than comparing three columns to three criteria separately, create a key column that concatenates the three fields for each record (I always include a separator character too) and then use SUMIF on that. For example, your code is checking cols G, C and K, so if you added a new column to the table using:
=G2&"|"&C2&"|"&K2
and then use that column in a SUMIF something like:
=SUMIF(new_column,$A1&"|"&B$1&"|az")

@romperstomper: that sounds like a neat and simple solution. I'll give that a go later.

Thank you.

@romperstomper: thank you, your suggestion worked well ... my only hangup was realising that I needed a COUNTIF, not a SUMIF. It took a while before it dawned on me that was the reason I was getting zeros returned. It's now realistic to copy the formula to the whole matrix and then copy the values. I'm still doing that in VBA so that I'm not constantly recalculating for no reason. Excellent!

@watersev: thanks again for your suggestion and offer of help.