XL 2010 Somme sur une fonction non standard

=SOMMEPROD(B6:B8;$D6:$D8)

Option Explicit
Function PondH(ByVal R1 As Range, ByVal RLig As Range) As Double
Dim T1(), TL(), C As Long
T1 = R1.Value: TL = RLig.Value
For C = 1 To UBound(TL, 2)
    PondH = PondH + Coef(RLig(1, C)) * TL(1, C) * T1(1, C)
    Next C
End Function
Function PondV(ByVal V1 As Double, ByVal RCol As Range) As Double
Dim TC(), L As Long
TC = RCol.Value
For L = 1 To UBound(TC, 1)
    PondV = PondV + Coef(RCol(L, 1)) * TC(L, 1) * V1
    Next L
End Function
Function Coef(ByVal C As Range) As Double
Const Blanc = -4142, Orange = 44, Gris = 15, Mauve = 39
Select Case C.Interior.ColorIndex
     Case Orange: Coef = 1.5
     Case Gris: Coef = 1.75
     Case Mauve: Coef = 2
     Case Else: Coef = 1: End Select
End Function

=PondH($B$1:$C$1;$B6:$C6)

=PondV(B$1;B$6:B$8)