sum every sheet cell into a sheet easy

[elmnas]

Hello people,



I have got an excel file that contains about 12 different sheets.
I need a formula
that says:

for each cell (B5) in every sheet except the sheet with name "totalt"
Present the total in the sheet totalt and the cell B3
Could someone help me?

Thank you in advance

[SamT]

Put this code in a Standard Module.

Option Explicit

Public Function SumOfAllSheets(SumRange As String, ExcludeSheets As String) As Double
Dim Temp As Double
Dim Sht As Worksheet

For Each Sht in Worksheets
If InStr(ExcludeSheets, Sht.Name) = 0 then Temp = Temp + Sht.Range(SumRange)
Next Sht

SumOfAllSheets = Temp

End Function

In totalt, Cell B3, put this formula:
=SumOfAllSheets("B5", "totalt")

To Exclude many sheets:
=SumOfAllSheets("B5", "totalt, Sheet1, Sheet2, etc")

[Kenneth Hobs]

=SUM(Sheet1:Sheet3!B5)-totalit!B5

[Paul_Hossler]

If your sheets are arranged right, you can use a multi-sheet formula like this.




=SUM(Sheet1:Sheet6!B5)



If totalt is between Sheet1 and Sheet6, then something like



=SUM(Sheet1:Sheet6!B5) - totalt!B5


17035