excel - Excel Sum(Indirect))

Question

I created a code that has the user type in how many stages are needed and then from there, the stages are copied from a "stage1" worksheet. On another sheet I am trying to create a sum(indirect)) formula to look up the materials number and then correspond to the "amt to order" column. I created this formula:

= SUMIF(INDIRECT("Stage1!$C$6:$C$18"), $A5, INDIRECT("Stage1!$E$6:$E$18")) +SUMIF(INDIRECT("Stage2!$C$6:$C$18"), $A5, INDIRECT("Stage2!$E$6:$E$18")) +SUMIF(INDIRECT("Stage3!$C$6:$C$18"), $A5 ,INDIRECT("Stage3!$E$6:$E$18")) +SUMIF(INDIRECT("Stage4!$C$6:$C$18"), $A5 ,INDIRECT("Stage4!$E$6:$E$18")) +SUMIF(INDIRECT("Stage5!$C$6:$C$18"), $A5 ,INDIRECT("Stage5!$E$6:$E$18")) +SUMIF(INDIRECT("Stage6!$C$6:$C$18"), $A5 ,INDIRECT("Stage6!$E$6:$E$18")) +SUMIF(INDIRECT("Stage7!$C$6:$C$18"), $A5, INDIRECT("Stage7!$E$6:$E$18")) +SUMIF(INDIRECT("Stage8!$C$6:$C$18"), $A5, INDIRECT("Stage8!$E$6:$E$18")) +SUMIF(INDIRECT("Stage9!$C$6:$C$18"), $A5, INDIRECT("Stage9!$E$6:$E$18")) +SUMIF(INDIRECT("Stage10!$C$6:$C$18"), $A5, INDIRECT("Stage10!$E$6:$E$18")) +SUMIF(INDIRECT("Stage11!$C$6:$C$18"), $A5, INDIRECT("Stage11!$E$6:$E$18")) +SUMIF(INDIRECT("Stage12!$C$6:$C$18"), $A5, INDIRECT("Stage12!$E$6:$E$18")) +SUMIF(INDIRECT("Stage13!$C$6:$C$18"), $A5, INDIRECT("Stage13!$E$6:$E$18")) +SUMIF(INDIRECT("Stage14!$C$6:$C$18"), $A5, INDIRECT("Stage14!$E$6:$E$18")) +SUMIF(INDIRECT("Stage15!$C$6:$C$18"), $A5, INDIRECT("Stage15!$E$6:$E$18")) +SUMIF(INDIRECT("Stage16!$C$6:$C$18"), $A5, INDIRECT("Stage16!$E$6:$E$18")) +SUMIF(INDIRECT("Stage17!$C$6:$C$18"), $A5, INDIRECT("Stage17!$E$6:$E$18")) +SUMIF(INDIRECT("Stage18!$C$6:$C$18"), $A5, INDIRECT("Stage18!$E$6:$E$18")) +SUMIF(INDIRECT("Stage19!$C$6:$C$18"), $A5, INDIRECT("Stage19!$E$6:$E$18")) +SUMIF(INDIRECT("Stage20!$C$6:$C$18"), $A5, INDIRECT("Stage20!$E$6:$E$18")) +SUMIF(INDIRECT("Stage21!$C$6:$C$18"), $A5, INDIRECT("Stage21!$E$6:$E$18")) +SUMIF(INDIRECT("Stage22!$C$6:$C$18"), $A5, INDIRECT("Stage22!$E$6:$E$18")) +SUMIF(INDIRECT("Stage23!$C$6:$C$18"), $A5, INDIRECT("Stage23!$E$6:$E$18")) +SUMIF(INDIRECT("Stage24!$C$6:$C$18"), $A5, INDIRECT("Stage24!$E$6:$E$18")) +SUMIF(INDIRECT("Stage25!$C$6:$C$18"), $A5, INDIRECT("Stage25!$E$6:$E$18")) +SUMIF(INDIRECT("Stage26!$C$6:$C$18"), $A5, INDIRECT("Stage26!$E$6:$E$18")) +SUMIF(INDIRECT("Stage27!$C$6:$C$18"), $A5, INDIRECT("Stage27!$E$6:$E$18")) +SUMIF(INDIRECT("Stage28!$C$6:$C$18"), $A5, INDIRECT("Stage28!$E$6:$E$18")) +SUMIF(INDIRECT("Stage29!$C$6:$C$18"), $A5, INDIRECT("Stage29!$E$6:$E$18")) +SUMIF(INDIRECT("Stage30!$C$6:$C$18"), $A5, INDIRECT("Stage30!$E$6:$E$18"))

But when I used it and had the user only input 9 stages then the #REF! error came back. Any help would be great! I've been stuck for a while.

Thank you!

标签： excelvba

Answer 1

我想我明白你在做什么。您希望 INDIRECT 将本质上相同的 SUMIF 应用于多个工作表。这是一个使用减少数量的工作表的示例。

我使用以下数据创建了 4 个相同的工作表，Stage1、Stage2、Stage3 和 Stage4。

然后在 Sheet1（我打算在其中使用公式）中，我在 F2:F5 中列出了这些相同的工作表。在 A5 中我使用了标准值，然后在 B5 中我使用了这个公式。

'standard formula with known number of worksheets
 =SUMPRODUCT(SUMIF(INDIRECT(TEXT(F$2:F$5, "'@'!")&"C6:C18"),$A5,INDIRECT(TEXT(F$2:F$5, "'@'!")&"E6:E18")))
'array formula for unknown number of worksheets
=SUMPRODUCT(IFERROR(SUMIF(INDIRECT(TEXT(F$2:F$5, "'@'!")&"C6:C18"),$A5,INDIRECT(TEXT(F$2:F$5, "'@'!")&"E6:E18")), 0))

在下面的示例图像中，您可以看到该公式从每个工作表中提取 SUMIF 小计并生成总计。

如果您不想修改工作表列表，则可以使用第二个公式。如果找不到工作表，它将为列表中的该名称返回零，而不是 #REF！错误。

注意，第二个公式是数组公式，必须用ctr+shift+enter结束，而不是直接enter。

使用第二个数组公式和列表中不存在的工作表名称。

现在，您应该能够通过创建要小计的工作表列表来显着减少您的公式。并调整上述公式以适应。

您可以通过将此公式放入 Sheet1!G2 并填写来测试列表中的工作表名称。

=NOT(ISERROR(INDIRECT(TEXT(F2, "'@'!")&"A1")))

TRUE 表示工作表存在于工作簿中，FALSE 表示您在某处搞砸了。