Sum the same cell in all worksheets
Results 1 to 7 of 7

Thread: Sum the same cell in all worksheets

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Sum the same cell in all worksheets

    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?
    Last edited by helpexcel; Aug 14th, 2019 at 11:47 AM.

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Sum the same cell in all worksheets

    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:
    Code:
    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!)
    Last edited by tyija1995; Aug 14th, 2019 at 12:01 PM.
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum the same cell in all worksheets

    If you're looking for VBA:

    Code:
        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)
    Last edited by Eric W; Aug 14th, 2019 at 12:01 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Sum the same cell in all worksheets

    Quote Originally Posted by Eric W View Post

    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
    √-1 2³ ∑ π
    …And it was delicious!

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Posts
    491
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum the same cell in all worksheets

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


    Code:
    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

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum the same cell in all worksheets

    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/art...2-787d0bc888b6

    helpexcel: Glad that worked for you!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Sum the same cell in all worksheets

    Quote Originally Posted by Eric W View Post
    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/art...2-787d0bc888b6
    Thanks for this Eric! Will take a look at the link
    √-1 2³ ∑ π
    …And it was delicious!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •