Comment faire une somme de deux colonnes qui utilisent une fonction matricielle

=SOMME.SI(INTER;AF4;N$4:N$38)+SOMME.SI('com ref L2'!inter;AF4;'com ref L2'!P$4:P$38)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim d As Object, c As Range
Set d = CreateObject("Scripting.Dictionary")
For Each c In Sheets("Com ref L1 ").[INTER]
  If c <> "" Then d(UCase(c)) = ""
Next
For Each c In Sheets("com ref L2").[INTER]
  If c <> "" Then d(UCase(c)) = ""
Next
If d.Count Then
  Application.EnableEvents = False
  Sheets("Com ref L1 ").[AF4].Resize(d.Count) = Application.Transpose(d.keys)
  Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim d As Object, r As Range
Set d = CreateObject("Scripting.Dictionary")
For Each r In Sheets("Com ref L1 ").[INTER]
  If r <> "" Then d(UCase(r)) = ""
Next
For Each r In Sheets("Com ref L2").[INTER]
  If r <> "" Then d(UCase(r)) = ""
Next
Application.EnableEvents = False
With Sheets("Com ref L1 ")
  If d.Count Then _
    .[AF4].Resize(d.Count) = Application.Transpose(d.keys)
  Set r = .Range(.[AF4].Offset(d.Count), .Range("AF" & .Rows.Count))
  Set r = Intersect(r, .[AF4].CurrentRegion)
  If Not r Is Nothing Then r.ClearContents 'effacement sous la liste
End With
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim d As Object, r As Range
Application.EnableEvents = False
With Sheets("Com ref L1 ")
  Set d = CreateObject("Scripting.Dictionary")
  For Each r In .[INTER]
    If r <> "" Then d(UCase(r)) = ""
  Next
  For Each r In Sheets("Com ref L2").[INTER]
    If r <> "" Then d(UCase(r)) = ""
  Next
  If d.Count Then _
    .[AF4].Resize(d.Count) = Application.Transpose(d.keys)
  Set r = .Range(.[AF4].Offset(d.Count), .Range("AF" & .Rows.Count))
  Set r = Intersect(r, .[AF4].CurrentRegion)
  If Not r Is Nothing Then r.ClearContents 'effacement sous la liste
  d.RemoveAll 'RAZ
  For Each r In .[PALETTE]
    If r <> "" Then d(UCase(r)) = ""
  Next
  For Each r In Sheets("Com ref L2").[PALETTE]
    If r <> "" Then d(UCase(r)) = ""
  Next
  If d.Count Then _
    .[AJ4].Resize(d.Count) = Application.Transpose(d.keys)
  Set r = .Range(.[AJ4].Offset(d.Count), .Range("AJ" & .Rows.Count))
  Set r = Intersect(r, .[AJ4].CurrentRegion)
  If Not r Is Nothing Then r.ClearContents 'effacement sous la liste
End With
Application.EnableEvents = True
End Sub