Option Explicit
Sub Somme_Prod()
Dim plage1 As String, plage2 As String, plage3 As String, critere As String, resultat
plage1 = [noms].Address
plage2 = [ccp].Address
plage3 = [bc].Address
critere = [I2]
resultat = Evaluate("Sumproduct((plage1=""" & critere & """)*(plage1 + plage3)")
'[F2].FormulaLocal = "=SOMMEPROD((noms=I2)*1; (ccp)+(bc))"
[F2] = resultat
'With [F2]
'.Value = .Value
'End With
End Sub