# Thread: Sum the same cell in all worksheets Thanks:  2 Post #5325636 (1)Post #5325667 (1) Likes:  1 Post #5325667 (1)

1. ## 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?  Reply With Quote

2. ## 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!)  Reply With Quote

3. ## 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)  Reply With Quote

4. ## Re: Sum the same cell in all worksheets Originally Posted by Eric W 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   Reply With Quote

5. ## 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```  Reply With Quote

6. ## 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!   Reply With Quote

7. ## Re: Sum the same cell in all worksheets Originally Posted by 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/art...2-787d0bc888b6
Thanks for this Eric! Will take a look at the link   Reply With Quote

## User Tag List

#### Tags for this Thread

expect, put, sheet1, sum, worksheets #### Posting Permissions

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