Option Explicit

Public tmpSum As Long

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim shX As Worksheet
    Dim X As Double
    
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    tmpSum = 0
    
    For Each shX In ThisWorkbook.Worksheets
        If shX.Visible = xlSheetVisible And shX.Name <> Sh.Name Then
            shX.Activate
            tmpSum = tmpSum + Application.WorksheetFunction.Sum(Selection)
        End If
            
    Next shX
    
    Sh.Activate
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Application.StatusBar = tmpSum + Application.WorksheetFunction.Sum(Selection)
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.StatusBar = tmpSum + Application.WorksheetFunction.Sum(Selection)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.StatusBar = False
End Sub

Option Explicit
 
Public tmpSum As Long
Public shIsCounted As Boolean
Const MaxCells = 100000
 
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    getAllShSum
    
    If shIsCounted Then
        On Error Resume Next
        If Selection.Cells.Count > MaxCells Then
           Application.StatusBar = "Выделено больше " & MaxCells & " ячеек"
           Exit Sub
        End If
        On Error GoTo 0
        
        Application.StatusBar = tmpSum + getActShSum
    End If
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    Dim aShSum As Double
    Dim c As Range
    
    If shIsCounted Then
        On Error Resume Next
        If Selection.Cells.Count > MaxCells Then
           Application.StatusBar = "Выделено больше " & MaxCells & " ячеек"
           Exit Sub
        End If
        On Error GoTo 0
        
        Application.StatusBar = tmpSum + getActShSum
    Else
        getAllShSum
        
        If shIsCounted Then
            Application.StatusBar = tmpSum + getActShSum
        End If
    End If
End Sub

Sub getAllShSum()
    Dim sh As Worksheet
    Dim shX As Worksheet
    Dim X As Double
    Dim c As Range
    Dim aShSum As Double
     
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    tmpSum = 0
    shIsCounted = False
     
    Set sh = ActiveSheet
    
    For Each shX In ThisWorkbook.Worksheets
        If shX.Visible = xlSheetVisible And shX.Name <> sh.Name Then
            shX.Activate
            On Error Resume Next
            If Selection.Cells.Count > MaxCells Then
                Application.StatusBar = "На листе """ & shX.Name & """ выделено больше " & MaxCells & " ячеек"
                sh.Activate
                Application.ScreenUpdating = True
                Application.EnableEvents = True
                Exit Sub
            End If
        On Error GoTo 0
            tmpSum = tmpSum + getActShSum
        End If
    Next shX
     
    shIsCounted = True
    sh.Activate
     
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
 
Function getActShSum() As Double
    Dim aShSum As Double
    Dim c As Range
    
    On Error Resume Next
    If Selection.Cells.Count > MaxCells Then
       getActShSum = 0
       Exit Function
    End If
    On Error GoTo 0
    
    With CreateObject("scripting.Dictionary")
        .Item(0) = 0
        For Each c In Selection.Cells
            If IsNumeric(c.Value) And c.Value <> 0 Then .Item(c.Row & "," & c.Column) = c.Value
        Next c
        aShSum = aShSum + Application.WorksheetFunction.Sum(.items)
    End With
    
    getActShSum = aShSum
End Function

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.StatusBar = False
End Sub

Option Explicit
Sub ShowRange(ByVal rg As Range)

  Dim r As Range

  Debug.Print "Областей " & rg.Areas.Count, "Ячеек " & rg.Cells.Count
  
  For Each r In rg.Areas
    Debug.Print r.Address
  Next r

End Sub

Sub test()

  Dim r As Range
  
  Set r = Range("A1:A3,A2:B2")
  ShowRange r
  
  r.Select
  ShowRange Union(Selection, Selection)

  ShowRange Intersect(Selection, Selection)

End Sub