Formula to add numbers from a specific cell in all my worksheets

[Soso22190]

Good morning, 

I have an Excel spreadsheet with many worksheets. The last worksheet is a recap.

In the recap worksheet I have a cell (for sake of argument E5) that needs to capture the overall stock figure (captured in D5) to which I substract figures from all the worksheets (all the worksheets are on the same format so I need to substract B13).

I manually typed it but it's so lengthy: 

=D5-'Woksheet1'!B13-'Worksheet2'!B13-'Worksheet3'!B13-'Worksheet4'!B13-'Worksheet5'!B13 etc etc etc as I can have up to 20+ worksheets!

Is there a formula I could enter that would pull B13 automatically from every worksheets, including the one I will be adding later? 

Thank you. 

[#2 · Accepted solution]

@Soso22190 

Insert a blank worksheet before the first one you want to want to subtract from D5, and name it First.

Insert a blank worksheet after the last one you want to want to subtract from D5, and name it Last.

Change the formula to

=D5-SUM(First:Last!B13)

When you want to add a new sheet, insert it between First and Last. It will automatically be included in the formula result.

[#3]

@Soso22190 

You can enter 20 worksheet names Worksheet1, Worksheet2, Worksheet3 and so on in range I1:I20 with this formula.

=SUMPRODUCT(SUM(INDIRECT("'"&$I$1:$I$20&"'!B13")))

If you have for example 12 worksheets then enter their names in range I1:I12 and change the formula accordingly.

=SUMPRODUCT(SUM(INDIRECT("'"&$I$1:$I$12&"'!B13")))

If you work with a recent version of Excel it should work without SUMPRODUCT.

=SUM(INDIRECT("'"&$I$1:$I$20&"'!B13"))

[#4]

@OliverScheurich Thank you so much for your reply, I wasn't sure anyone would see my query. 

So in my case each worksheet has been name after the work location which is different to every job. I haven't actually named them "worksheet 1, worksheet 2 etc". Does this change anything? 

I tried the formula but it went #REF! and when looking at it, it seems to want to extract info from the I cell in my recap sheet (the I cell is empty). 

I'm probably doing this wrong. 

[#5]

OMG OMG OMG!!! It works!!! Thank you so so so so so so so much!! 😄

[#6]

@Soso22190 

Yes, the worksheet names in e.g. range I1:I12 must be actual worksheet names. An actual worksheet name must be entered in all referenced cells which means that no cell is blank.

If the names of the worksheets are named like months January to December (12 sheets) you can select e.g. January, April, October and December in range I1:I4 and calculate the result only for these 4 sheets. The range within the formula must be changed accordingly to I1:I4.

[#7]

@Soso22190 

The attached workbook follows @HansVogelaar in that it uses a 3D range, but I have named it and then used TOCOL to return the values as an array and filter out the blank cells.  Once that is done it is possible to treat the values as a normal array and display it as a list, sum it or use XLOOKUP to return the result from any specific sheet.