SUM Single Cell in Multiple Cell Referenced Worksheets

KnightFhawker

New Member
Hi everyone,

This is my first post on here so I hope I follow the procedure appropriately. Please feel free to offer any feedback if necessary.

Despite hours of searches and reference material I have looked though, I cannot figure out why the following formula ends up in a #REF!. It seems to follow almost all related formats that I have found, yet I have not necessarily found this exact situation to confirm the issue. Any suggestions would be much appreciated.

I am simply trying to SUM all \$M29 cells across all of the existing worksheets within the range provided. I want to ensure the worksheet range is updated based on the cell references found in B94 (containing the First Worksheet name within the range) and D94 (containing the Last Worksheet name within the range).

Each of the cells contain the following formulas with the respective result:

B94: =B98 First Worksheet Name
D94: =LOOKUP(2,1/(B:B<>""),B:B) Last Worksheet Name

F48: =SUM(INDIRECT("'"&\$B\$94&":"&\$D\$94&"'!\$M29")) #REF!

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Weazel

Well-known Member
unfortunately the indirect doesn't support 3d cell references.

an alternative might be adding 2 sheets to your workbook say 'start' and 'end' then =sum(start:end!M29) would add all the values in M29 between the 2 sheets.

MrExcel MVP
@KnightFhawker

You appear to have the names of the relevant sheets in column B and want to say sum M29 of the sheets FROM nth sheet TO the last sheet.

If you would be able to specify the FROM sheet, you can have:

=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'"&INDEX(B:B,98):INDEX(B:B,MATCH(REPT("z",255),B:B))&"'!M29")))

The bolded part should be a number or a MATCH expression like:

KnightFhawker

New Member
I guess the fact that INDIRECT doesn't allow 3D expressions explains my issue. Thank you for the explanation.

I believe the the First and Last worksheet might be the best way to proceed for me at this point, but I will see about incorporating the SUMPRODUCT option if I can make it work. Thank you both for your help and taking the time to help me with this issue. I greatly appreciate it.

FDibbins

Well-known Member
Another option might be to use something like this...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!T6:T1000"),C794,INDIRECT("'"&Tabs&"'!M6:M1000")))

You would need to create a list of sheet names, and give it the range name Tabs

KnightFhawker

New Member
Thank you FDibbins!

I was able to take your suggestion and meld it together with a formula within the Name Manager to allow the dynamic adjustment to flow through with the INDIRECT function.

[Cell: =Formula Result]

What I did was create 2 cells, one grabs the first worksheet name in the list and the second grabs the last:

B94: =B98 First Worksheet Name

D94: =LOOKUP(2,1/(B:B<>""),B:B) Last Worksheet Name

The next two cell grab the cell address for each of the above results:

Next, I set an extended range of cells (B98-B250) that includes both current and empty cells that could in the future hold worksheet names as they are added, calling this range TABS as FDibbins suggested.

Then, within the Name Manager, I associated this formula with the TABS range name in order to grab the cell values in B95 and D95:

Name Manager, TABS: =INDIRECT("'SUMMARY'!"&SUMMARY!\$B\$95&":"&SUMMARY!\$D\$95) 'SUMMARY'!(First Worksheet Name Cell Address):(Last Worksheet Name Cell Address)

Finally, I used the following formula to combine the TABS range with the actual cell I want to sum across the range of cell within TABS:

F48 =SUMPRODUCT(SUMIF(INDIRECT("'"&TABS&"'!\$M29"),"<>0")) Displays the Sum of each M29 cell across all worksheets within the TABS range

In case anyone is interested I used the following VBA associated with a button labeled REFRESH in order to grab and list all of the worksheet names within B98:B250:
(keep in mind that I set this up when I was using the F(first) and L(last) tabs to enclose the range as suggested by Weazel)
(Also, the IF section is there strictly to skip listing worksheets that are not intended to be listed, and can be removed if you are not needing to skip any of the worksheets)
(The i is set to start at 2 in order to skip the first "Summary" worksheet, so if you would like to start it at the first worksheet then "i" would need to be 1 instead)
(Finally, the +94 after the "i" within the Cells line is simply there to shift the row of where the results will be listed, with the 2 representing column B. Adjustments were made to the 94 due to skipped worksheets. I'm sure there's a more efficient way but didn't take the time to work on it)

Sub SheetNames()

Dim i As Integer

Sheets("Summary").Range("B98:B250").ClearContents

For i = 2 To Sheets.Count

If (Sheets(i).Name = "F" Or Sheets(i).Name = "L" Or Sheets(i).Name = "Input") Then GoTo 1

Cells(i + 94, 2) = Sheets(i).Name
1
Next i
End Sub

Thank you again to all of you for your suggestions! I truly appreciate the help.

Replies
2
Views
37
Replies
33
Views
642
Replies
5
Views
429
Replies
2
Views
92
Replies
1
Views
209

1,191,030
Messages
5,984,232
Members
439,879
Latest member
KingGoulash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back