# 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?

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!)

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)

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

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```

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!

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