I have come up with this but it doesn't get around the range problem. i.e. 54858 needs replacing with siommething like lrow.

    ActiveCell.Offset(3, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((Download!RC[2]:R[54858]C[2]=""CUSTOMER1"")*(Download!RC[1]:R[54858]C[1]=CUSTOMER1!RC[1])*(Download!RC:R[54858]C))"
    ActiveCell.Offset(1, 0).Range("A1").Select