SUMIFS 跨一系列工作表 [英] SUMIFS across a range of sheets

问题描述

我正在构建一个 excel 家庭财务/预算工作簿.我想将每个帐户的交易保存在单独的表格中，并将我的预算表格与这些表格分开.我一直在寻找一种方法来汇总类别，而无需对所使用的工作表的名称进行硬编码.我按如下方式设置工作表.

I'm building an excel home finances/budget workbook. I would like to keep the transactions for each account on separate sheets with my budget sheet separate from those. I was looking for a way to sum the categories without hard-coding the names of the sheets used. I setup the sheets as follows.

预算 - 开始 - 帐户 1 - 帐户 2 - 结束

Budget - Start - Account1 - Account2 - End

一旦我开始工作，我有更多的帐户要添加.这都是原型设计，以确保我可以做我想做的事.

I have more accounts to add once i get it working. This is all prototyping to make sure I can do what I want.

我发现我可以执行以下操作来总结开始和结束之间所有工作表上的所有内容(只要它们是空白的).

I found I can do the following to sum everything on all sheets between Start and End (as long as those are blank).

=SUM(Start:End!B1:B10)

我无法工作的是 SUMIFS

What I can't get working is a SUMIFS

=SUMIFS(Start:End!B1:B10,Start:End!A1:A10,"Count")

在 A 列中，一个单元格有Count"或Don't".这些最终将成为类别.以上给了我一个#VALUE"错误.我也无法让 SUMIF 工作.我可能会添加其他条件.

In column A a cell that has "Count" or "Don't". These will eventually become categories. The above gives me a "#VALUE" error. I wasn't able to get SUMIF to work either. I may add other conditions down the road.

我做错了吗?有没有简单的方法来做这样的事情?

Am I doing something wrong? Is there an easy way to do something like this?

推荐答案

我通过创建一个包含工作表名称的范围 E2:E4 来实现它，第一个是开始"，最后一个是结束".

I got it to work by making a Range E2:E4 containing the names of the sheets, the first being "Start" and the last being "End".

我也有一个值为Count"的条件框 E7

I also have a criteria box E7 with value "Count"

开始

我在 A1:A10 列中填充了Count"或Don't".我用数字填充了 B1:B10 列.

I've populated columns A1:A10 with either "Count" or "Don't". I've populated columns B1:B10 with numbers.

Sheet2(左)和 End(右)

Sheet2(left) and End(right)

然后我用这个公式返回了想要的结果:

Then I used this formula to return the desired result:

=SUMPRODUCT(SUMIF(INDIRECT("'"&E2:E4&"'!A1:A10"),E7,INDIRECT("'"&E2:E4&"'!B1:B10")))

不知道这是否对您有帮助，但它确实通过编写工作表列表和标准列表的额外工作产生了预期的结果.

Don't know if that helps you but it does produce the desired results with the extra work of writing up a sheet list and a criteria list.

这篇关于SUMIFS 跨一系列工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！