It's possible to achieve this with the built-in SUM()-function, but that can be a big task if the workbook contains many worksheets, or if you frequently add new worksheets to the workbook. To add a range from several worksheets you can use the SUM-function like this:

=SUM(Sheet1:Sheet10!A1:A100) The formula above will add the range A1:A100 in the worksheets Sheet1 and Sheet10, including all worksheets between the two sheets in the workbook.

With the custom function below it's easy to add a range from all the worksheets in a workbook:

Function SumAllWorksheets(InputRange As Range, InclAWS As Boolean) As Double
' adds the content of InputRange in all worksheets in the workbook
Dim ws As Worksheet, TempSum As Double
    Application.Volatile ' calculates every time a cell changes
    TempSum = 0
    For Each ws In ActiveWorkbook.Worksheets
        If InclAWS Then ' include the active worksheet
            TempSum = TempSum + Application.WorksheetFunction.Sum(ws.Range(InputRange.Address))
        Else ' exclude the active worksheet
            If ws.Name <> ActiveSheet.Name Then ' not the active worksheet
                TempSum = TempSum + Application.WorksheetFunction.Sum(ws.Range(InputRange.Address))
            End If
        End If
    Next ws
    Set ws = Nothing
    SumAllWorksheets = TempSum
End Function