CODEWITHSUNDEEP
excelexcel-formulaformulaworksheet-functionworksheet
Christa
Christa

Reputation: 21

Reference to last worksheet in Excel Function to Sum same range across multiple sheets

I've tried a few formula options but nothing is quite working the way I would like it to.

I'm using a very simple SUM formula to total across all the worksheets in my workbook. Currently it's set up like this:

=SUM('1:10'!D3)

However, I'm likely going to add more worksheets and I want them included in this total. I've tried a few different versions of "wshNameLast" or "getlastwsname" that I've found in different articles here but they are all giving me a #REF error. Is there a formula I'm able to put in here without turning to VBA?

Thanks for your help!

Upvotes: 2

Views: 1171

Answers (1)

Naresh
Naresh

Reputation: 3034

Interesting question.

@BigBen's comment to the question >>> "Have a hidden blank worksheet at the end and just reference that" >>> is really practical and the easiest solution.

But just to make it dynamic

Referred to .. Jeffrey Weir's answer and Return array with Index Function

and as suggested by @Michal Rosa in comment on this question How to generate list of Sheet Names

and as suggested by @ashleedawg in comment on this question using indirect function to sum across multiple sheets

Created workbook with 5 worksheets.

First Sheet is Sheet5 and so on the last is Sheet1.

Each Sheet except the first has value in B5.

First defined the name for SheetNames array =GET.WORKBOOK(1)

enter image description here

Then it was easy to sum Values in B5 of all the the Sheets(Including the first) with this formula

=SUMPRODUCT(SUM(INDIRECT("'"&SheetNames&"'!B5")))

But what if one has to enter a formula in the First Sheet(Sheet5) in B5 to sum all remaining Sheets' B5. The formula above returns circulating error.

The formula below can be entered in the first Sheet B5 to Sum all the sheets from Second to last. Note that, its an array formula. (Copy without curly bracket and enter with ctrl+shift+enter)

{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),ROW(INDIRECT(2&":"&SHEETS())))))&"'!B5")))}

INDIRECT(2&":"&SHEETS()) starts the array with Second Sheet and Ends with last sheet (number calculated with Sheets() function)

So, if one wants to start array/ sum from Sheet number 3 use INDIRECT(3&":"&SHEETS()) with Ctrl+Shift+Enter

One can replace ROW function with an array of Sheet Numbers. For example, to sum only Second and Fifth Sheets

{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),{2,5})))&"'!B5")))}

Also, &"'!B5" can be replaced with &"'!"&ADDRESS(ROW(),COLUMN()) to sum current formula cell address across multiple sheets.

Upvotes: 2

Related Questions