具有多个条件的Excel DSUM [英] Excel DSUM with multiple criteria

问题描述

我对Excel很陌生.这是代码

I'm pretty new to Excel. Here is the code

=DSUM(Table_1,"column1",INDIRECT(INDIRECT(ADDRESS(MATCH($C9,Names!B:B,0), 1, 1, , "Names"))))

将具有匹配条件的Table_1中column1的所有值相加.但是，如果我想向DSUM添加另一个条件，例如Table1_column_amount = 1，我该怎么做?

which sums up all the values of column1 inside Table_1 with the matched criteria. However, if I would like to add another criteria say Table1_column_amount=1 to the DSUM, how would I possibly do that?

任何帮助将不胜感激.

any help would be appreciated.

推荐答案

因此在您的设置中，您有两个表:

So in your setup, you have two tables:

1. Table_1，数据源.
2. 名称"表上的表:
   • Column A:条件范围的地址，以字符串形式存储在单元格中
   • Column B:查找名称

1. Table_1, the data source.
2. Table on the Names sheet:
   • Column A: Address of the criteria range, stored as a string in the cell
   • Column B: A name to lookup

您在Names表中查找一个单元格，然后取消引用以获得标准范围的地址.

You lookup a cell in the Names table and dereference it to get the address of the criteria range.

首先，最好摆脱内部间接/地址对，这不是必需的:

First of all, it's good to get rid of the inner indirect/address pair, it is not required:

=DSUM(Table_1,"column1",INDIRECT(INDEX(Names!A:A,MATCH($C9,Names!B:B,0),1)))

(仅出于此目的，它更具可读性且执行速度更快.)

(Just for the sake of it, it's more readable and much faster to execute.)

现在，提一个问题.
您必须将条件添加到INDEX返回的单元格所指向的单元格范围中.您必须在其中添加一列，提供新的条件，然后修改存储的引用，使其指向新的扩展范围.

Now, to the question.
You have to add the condition to the cell range which is pointed from the cell returned by the INDEX. You have to add a column to it, provide your new condition, and amend the stored reference so that it points to the new, extended range.

您不能仅通过使用范围并运算符来累加该条件范围.不幸的是，D*函数集要求条件范围为一个单一区域.

You cannot just add up to that condition range by using the range union operator. Unfortunately, the D* set of functions require the condition range to be one single area.

这篇关于具有多个条件的Excel DSUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！