excel - SumIF 值 <> 下一列中的“#NV”

Question

我的 excelsheets 看起来像这样：

然后我用公式试了一下：

=SUMIF(B1:B200;B1:B200<>"#NV";B:B)

但这似乎是错误的。如果每个部分（11111、22222）中不是#NV，我想总结所有数字。每个数字 2,3,4,6,9 都属于组 11111。我想确定分配给组11111的所有值的总和。

对于 11111：对单元格 B2:B6 求和，对于 22222，对 B8:B12 求和。这就是我想做的。

希望您能够帮助我。如果有可能有一个宏，我将非常感谢，如果你能分享你的想法。谢谢

标签： excelvbaformula

Answer 1

间接英尺数组公式

• 强烈建议您将出现的 替换$B:$B为实际范围，例如$B$1:$B$100

• 以下两个公式将被复制到C1和D1。CTRL第二个是数组公式，SHIFT如果ENTER没有365.

• 这是一个糟糕的解决方案，因为第一个公式使用了间接的，它是一个易失的（每次计算后更新）函数，第二个是一个数组公式。第二个应该放在第一个而不是D1但它不起作用（我使用的是 Excel 2019）。因此，您必须为此使用额外的列。也许365可以接受。

• 希望有人会使用这些信息来创建更有效的解决方案。

公式

这两个是您需要的C1和D1：

=IF($B2<>"#NV","",SUM(INDIRECT(ADDRESS(D1,2,4)&":"&ADDRESS(ROW(),2,4))))
=IF($B2<>"#NV","",SMALL(IF($B:$B="#NV",ROW($B:$B)-ROW(INDEX($B:$B,1,1))+2),COUNTIF($B$1:$B1,"#NV")))

这个计数“#NV”从B$1当前行出现的次数。

=COUNTIF(B$1:B6,"#NV")

这是一个数组公式，返回第一次出现“#NV”之后的行。请注意，+2instead of+1表示下面的行，而1after 表示第一次出现。

=IFERROR(SMALL(IF($B:$B="#NV",ROW($B:$B)-ROW(INDEX($B:$B,1,1))+2),1),"")

与上一个类似，这是一个数组公式，返回最后一次出现“#NV”之后的行。

=IFERROR(SMALL(IF($B:$B="#NV",ROW($B:$B)-ROW(INDEX($B:$B,1,1))+2),COUNTIF(B$1:B6,"#NV")),"")

这是SUM/INDIRECT公式：注意第一个数字2是要被数组公式替换的数字，而6是要被最后一个ROW公式替换的数字。

=SUM(INDIRECT(ADDRESS(2,2,4)&":"&ADDRESS(6,2,4)))

ROW公式。

=ROW()