Add cells in a range from all worksheets in the workbook

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 True ' 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

The function is used in the same way as the built-in worksheetfunctions. In Excel 5/95 the variable InputRange must be defined as Variant instead of Range. The function can easily be adapted for use with other worksheetfunctions that you want to use on all the worksheets in a workbook.

Document last updated 1999-08-14 12:44:13 Printerfriendly version

User comments:

Jon from UK wrote (2005-04-26 11:58:05 CET):

Re: Worksheets Not Counted
Thanks for your swift reply, but the number of sheets that need counting are variable, and will be sent to users who don't really use excel and so won't manually change the formula. I figured out a workaround however, on the "info" sheets i've hidden the "usual" range and have offset any data on these sheets to start at "AA". Therefore no data will be entered into these within the range specified on the calculated sheets hence the formula now works a charm. Thx again.

Ole P. from Norway wrote (2005-04-25 14:26:30 CET):

Re: Worksheets Not Counted
If you have 13 sheets named Sheet1 to Sheet13 you can sum the last 10 like this:
=SUM(Sheet4:Sheet13!A1:A100)

Jon from UK wrote (2005-04-25 12:15:44 CET):

Worksheets Not Counted
I was wondering how you could use this function but not include a number of specifically named worksheets.

e.g. if i had 13 sheets but the first three are informational or static, and i would like to sum the remaining 10 sheets (these may be added to, so i may have up to 20-25 "sum" sheets that need summing.

Any help would be greatly appreciated, what you provided is already very useful and i have used a number of your functions already.....

Thanks and happy coding

Paul Dumitrescu from Bucharest, Romania wrote (2004-09-24 15:46:51 CET):

Thanks a lot
Thanks a lot for this tip, I saved big time.
Very useful your site.
Thank you again.