编程技术网

 用户名 Email 自动登录 找回密码 密码 立即注册

引用Excel函数中的最后一个工作表以将多个表的相同范围求和:Reference to last worksheet in Excel Function to Sum same range across multiple sheets

deeppatidar 集合 2022-5-11 10:40 7人围观

` = SUM('1:10'！D3)`

@BigBen对问题的评论>>>最后有一个隐藏的空白工作表，只是引用它" >>>确实是实用且最简单的解决方案.

` = SUMPRODUCT(SUM(INDIRECT('"& SheetNames&'！B5")))`

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

` INDIRECT(2&"::& SHEETS())`以第二张纸开始数组，以最后一张纸结束(使用Sheets()函数计算的数字)

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

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?

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

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.