excel - 如何从动态命名范围中进行 OFFSET 并在 SUMPRODUCT 中使用该范围？

Question

情况:

尝试将动态命名范围的 OFFSET 传递给 SUMPRODUCT 时，我收到 #Value!。

设置:

我在 Sheet1 的 A2:B4 范围内有以下数据。

| TextA | 1 |
|-------|---|
| TextA | 2 |
|-------|---|
| TextB | 3 |

我使用以下公式创建了 A 列中值的动态命名范围 textRange:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1048576),1)

如图所示:

注意: textRange 将选取所有必需的行。可以假设它是从连续填充的范围中提取的，即 textRange 涵盖了所需的所有行。

目标:

我现在想要使用Column函数来偏移，以获得相同的行1列，即B2:B4；如果 A 列中的相应文本以“A”结尾，则对该范围内的值求和。

预期结果为 3。

流程:

1) 我使用以下公式来构建偏移范围:

OFFSET(A2,0,COLUMN(B1)-1,COUNTA(textRange),1)

偏移范围可以是 A 之后的任何列，因此我使用 Column 函数，以便可以拖动公式以返回感兴趣的范围。

2) 然后，如果相应的 Column A 行的最后一个字母为“A”，则我将此偏移范围传递给 SUMPRODUCT 并对其值求和，即

=SUMPRODUCT(OFFSET(A2,0,COLUMN(B1)-1,COUNTA(textRange),1),--(RIGHT(textRange,1)="A"))

结果:

预期结果为 3，但当前为 #Value!

问题:

我做错了什么？我猜这是因为我如何通过范围。

请求的解决方案:

我愿意接受任何其他方式来实现相同的结果。但是，当跨列拖动时，公式必须更新动态范围的偏移量，并且必须对新的行集执行条件求和。

引用:

https://chandoo.org/forum/threads/using-offset-function-with-sumproduct.960/

Answer 1

#值(value)!错误似乎来自于 Column(B1) 生成一个单单元格数组这一事实，如果您使用评估公式对其进行跟踪，您可以看到

这确实是一个奇怪的问题，但我猜测 OFFSET 然后会尝试返回一个范围数组，该数组只能由几个函数处理 - N()、SUBTOTAL 和 INDEX。

您可以通过像这样对数组求和来修复它

=SUMPRODUCT(OFFSET($A2,0,SUM(COLUMN(B1)-1),COUNTA(TextRange),1),--(RIGHT(TextRange,1)="A"))

或将 OFFSET 包装在 INDEX 中

=SUMPRODUCT(INDEX(OFFSET($A2,0,COLUMN(B1)-1,COUNTA(TextRange),1),0,1),--(RIGHT(TextRange,1)="A"))