间接使用工作表名称的变量范围 [英] Indirect using variable range for Worksheet names

问题描述

我有以下公式：

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:D2&"'!A1"),E2))

从这里取消
https://www.extendoffice.com/documents/excel/ 2541-excel-countif-across-multiple-worksheets.html

现在，我对D列做了一个COUNTA：

Now, i'm making a COUNTA to the D column:

=COUNTA(D:D)

这将基本上导致我有多张表。

Which will basically result in me having the number of sheets.

现在，我想使第一个公式使用第二个公式的结果：

Now, i would like to make the first formula use the result of the second one:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:***COUNTA RESULT***&"'!A1"),E2))

正确的语法是什么？没有成功。

What is the correct syntax to perform that? Did not manage to succeed.

推荐答案

由于工作表通常具有字母名称，您正在尝试一个不为COUNT的COUNTA解决方案，请使用，

Since worksheets typically have alphabetic names and you were attempting a solution with COUNTA not COUNT, use,

D1:index(D:D, match("zzz", D:D))

...来描述您的工作表名称范围。

... to describe your range of worksheet names.

如果工作表名称是数字的，那么

However, if the worksheet names are numeric then,

D1:index(D:D, match(1e99, D:D))

完成为

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:index(D:D, match("zzz", D:D))&"'!A1"),E2))
=SUMPRODUCT(COUNTIF(INDIRECT("'"&D1:index(D:D, match(1e99, D:D))&"'!A1"),E2))

。既然你已经使用了一个带有INDIRECT的volatile函数，那么添加另一个并不是那么大。

You could also use OFFSET(D1, 0, 0, COUNTA(D:D), 1). Since you are already using a volatile function with INDIRECT, adding another isn't that big of a deal.

这篇关于间接使用工作表名称的变量范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！