Sum the same cell in all worksheets

[helpexcel]

Hi is there a way besides using .Formula to say sum all the B6's on the worksheets in a workbook expect for sheet1, and put the results in B6 on sheet1?

 

[tyija1995]

Hey,

Say if you had all your sheet names on sheet1 (excluding sheet1 itself) then you can use:

=SUMPRODUCT((SUM(INDIRECT(A1:A2&"!B6"))))

Where A1:A2 contain the other sheet names (Sheet2 & Sheet3) in this small example.

Are there many sheets? You might want to use VBA to generate the sheetnames on to sheet1 if there are.

EDIT: Use something like this to get the sheet names if there are too many to manually write:

Sub SheetNames()
    Dim nSheets As Long
    Dim i As Long
    nSheets = ActiveWorkbook.Sheets.Count - 1
    Sheets("Sheet1").Activate
    For i = 1 To nSheets
        Cells(i, 1).Value = Sheets(i + 1).Name
    Next i
    
End Sub

This pastes the sheet names (except sheet1) in to column A of sheet1 (Make sure that column A is empty otherwise it will be overwritten!)

 

[Eric W]

If you're looking for VBA:

    MyTotal = 0
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then MyTotal = MyTotal + ws.Range("B6")
    Next ws
    Sheets("Sheet1").Range("B6") = MyTotal

Or you could use a formula:

=SUM(Sheet2:Sheet3!B6)

 

[tyija1995]

Or you could use a formula:

=SUM(Sheet2:Sheet3!B6)

I didn't know you could do that cross-worksheet SUM!! I've took note of that for future use, learn something new everyday

 

[helpexcel]

@Eric W that code worked great, thanks! I updated it to include NxtRow so that it will continue to sum as data is added.

With Sheet1
NxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
End With

MyTotal = 0    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then MyTotal = MyTotal + ws.Range("B" & NxtRw)
    Next ws
    Sheet1.Range("B" & NxtRw) = MyTotal

 

[Eric W]

tyija1995: Yes, those 3-D functions can be useful in some cases, and they're not really well known. Not every function can use a 3-D reference. Here's a link explaining a bit more:

https://support.office.com/en-us/ar...rksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6

helpexcel: Glad that worked for you!

 

[tyija1995]

tyija1995: Yes, those 3-D functions can be useful in some cases, and they're not really well known. Not every function can use a 3-D reference. Here's a link explaining a bit more:

https://support.office.com/en-us/ar...rksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6

Thanks for this Eric! Will take a look at the link