vba - 在 excel 中，只想对某些值求和(不像 SUMIF 那样简单)？

Question

所以我在命名程序上有两列，一列是成本值。这三个程序是 ABC、A、B 和 C。我想将所有包含 A 的程序的成本相加。所有包含 B 的程序。以及所有包含 C 的程序。ABC 显然包含在所有总和中。问题在于，为了获得这些程序，电子表格上有一个过滤器，它会弄乱 sumif。有人可以帮忙吗？这是我的意思的一个例子:

program     cost
A           5.00
B           4.00
ABC         9.00
A           2.00

所以我想在三个单独的单元格中“与 A 求和”=16.00，“与 B 求和”=13.00，“与 C 求和”=9.00。

Answer 1

Item | TotalA    | 16B    | 13C    | 9

Assuming your above range is in A1:B5, my first formula is the following Array formula:

{=SUM(IF(ISERROR(FIND(B6,$A$1:$A$5)),0,$B$1:$B$5))}

您可以通过输入公式并按住 Ctrl+Shift 创建一个数组公式。按下 Enter 键。在我的解决方案中，我创建了一个按总计计算的区域，并有一个列(在本例中称为 Item)，它指示我在原始 A 列中看到的字母。

如果您尝试使用 VBA 输入，您将使用 FormulaArray属性(property):

Selection.FormulaArray ="SUM(IF(ISERROR(FIND(B6,$A$1:$A$5)),0,$B$1:$B$5))"

更新

将计算限制为仅可见单元格有点复杂。假设我们在单元格 A1:B5 中有您的原始数据。我们还假设我们的测试值从单元格 C7 开始(与源数据成对角线)。我们的总计公式如下所示:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1)), --NOT(ISERROR(FIND(C7,$A$1:$A$5))), $B$1:$B$5)

以下部分返回单元格的范围

OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1)

这部分为每个可见单元格返回 1，为不可见单元格返回 0

SUBTOTAL(3,OFFSET($B$1:$B$5,ROW($B$1:$B$5)-ROW($B$1),0,1))

这部分是我们的标准。 NOT(ISERROR(...将返回 TRUE 或 FALSE。双负号 --将该值转换为负整数，然后删除该否定。

--NOT(ISERROR(FIND(C7,$A$1:$A$5)))

最后， SUMPRODUCT函数将匹配的数组相乘并执行总和。前两个数组返回一系列 0 或 1。如果该行既可见又符合我们的条件，那么我们得到 1*1 乘以单元格中的给定值。如果给定单元格不可见或不符合条件，则两者之一返回零并将整个项目清零。