使用 CountIF 创建 VBA 公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13748821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a VBA formula using CountIF
提问by Aerogal31
I am creating a formula to count based upon 2 conditions. My logic is wrong - again.
我正在创建一个基于 2 个条件进行计数的公式。我的逻辑是错误的 - 再次。
When I (manually) enter enter code the summation formula (COUNTIFS) into correct cell itself, it sums correctly:
当我（手动）将求和公式（COUNTIFS）的输入代码输入到正确的单元格中时，它会正确求和：
COUNTIFS(E4:E1362,"Requirement",S4:S1362, "<>4")
When I execute the following code line, I do not get any errors, but instead, all the sums are zero.
当我执行以下代码行时，我没有收到任何错误，而是所有的总和都为零。
Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & "," & "Requirement" & ",S" & Start(groups) & ":S" & Finish(groups) & "," & Chr(34) & "<>4" & Chr(34) & ")"
I realized that the COUNTIFS was comparing the value in column S to the string "<>4". And none of the cells contain that string. This is why all my values were zero. I do not want the comparison to be against that string. I want the comparison to be column S value NOT equal to 4.
我意识到 COUNTIFS 正在将 S 列中的值与字符串“<>4”进行比较。并且没有任何单元格包含该字符串。这就是为什么我所有的值都是零的原因。我不希望比较针对该字符串。我希望比较为 S 列值不等于 4。
So, I changed the line to (and variations to get it to work):
因此，我将行更改为（以及使其工作的变体）：
Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & "," & "Requirement" & ",S" & Start(groups) & ":S" & Finish(groups) & "," & Chr(34) & "<>" & Chr(34) & "4)"
So, I am at a loss. (1) I manually enter the formula and it works. (2) I build it in VBA, and it does not sum correctly. (3) I look up how to do build the formula correctly, and I get errors time and time again. It seems like the only way to get this to work is to keep the quotes within quotes, but I don't want to compare against the string.
所以，我不知所措。（1）我手动输入公式并且它有效。(2) 我在 VBA 中构建它，但它的总和不正确。(3) 我查找如何正确构建公式，但我一次又一次地出错。似乎让这个工作的唯一方法是将引号保留在引号内，但我不想与字符串进行比较。
Does this make sense? I'm not liking my worksheet anymore. It is no longer any fun at all. :(
这有意义吗？我不再喜欢我的工作表了。它不再是任何乐趣。:(
Thank you so much.
非常感谢。
回答by SeanC
I think all the Chr(34)are getting in the way of you viewing your formula correctly. try using ""to make the quote for the formula.
我认为所有这些Chr(34)都妨碍了您正确查看公式。尝试使用""来制作公式的报价。
.Formula = "=COUNTIFS(E4:E1362,""Requirement"",S4:S1362,""<>4"")"
with your Startand Finishfunctions, that would change the formula to:
使用您的Start和Finish函数，这会将公式更改为：
.Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & _
",""Requirement"",S" & Start(groups) & ":S" & Finish(groups) & ",""<>4"")"
回答by Serafin William
Well, One of the problem I found there was quite simple, instead using "," you must use ";". It depends on the version you used, some using "," and some others using ";". you can just try it, I hope it solved your problem.
嗯，我发现的问题之一很简单，而不是使用“，”你必须使用“;”。这取决于您使用的版本，有些使用“,”，有些使用“;”。你可以试试看，希望它能解决你的问题。
and second one "Requirement" there, I think you should use double quote instead.
和第二个“要求”，我认为你应该使用双引号。
and for your case To meet This condition:
并且对于您的情况要满足此条件：
COUNTIFS(E4:E1362,"Requirement",S4:S1362, "<>4")
Use This:
用这个：
"=COUNTIF(E" & Start(groups) & ":" & "E" & Finish(groups) & "," & """Requirement""" & "," & "S" & Start(groups) & ":" & "S" & Finish(groups) & "," & """<>4"")"
I think it Should Work (if I don't miss any Quotes thought).
我认为它应该有效（如果我没有错过任何报价的话）。
回答by Rick
After running a quick test with your initial code
使用初始代码运行快速测试后
Range("G1").Formula = "=COUNTIFS(E1" & ":E20" & "," & "Requirement" & ",S1" & ":S20" & "," & Chr(34) & "<>4" & Chr(34) & ")"
Modified slightly to account for your groups variable cell G1 ends up with the following formula:
稍微修改以考虑您的组变量单元格 G1 最终使用以下公式：
=COUNTIFS(E1:E20,requirement,S1:S20,"<>4")
which when tested works absolutely fine for your second if statement S1:S20,"<>4". I would guess that the problem lies with your first statement. What is in your requirement range and what data are you trying to match with.
经过测试，对于您的第二个 if 语句 S1:S20,"<>4" 来说绝对正常。我猜问题在于你的第一句话。您的要求范围内有哪些内容以及您要匹配哪些数据。
I set up the requirement named range to be cell D5, I entered a 5 in there and the formula would increment each time I added an extra 5 to the range E1:E20. I then started typing 4's into the range S1:s20 and this decreased my count.
我将名为 range 的要求设置为单元格 D5，我在其中输入了 5，每次我向范围 E1:E20 添加额外的 5 时，公式都会增加。然后我开始在 S1:s20 范围内输入 4，这减少了我的计数。
editThe last thing that I can suggest is to add in a helper column in column F that evaluates row to see if it matches the requirement condition, then it would be a simple matter of making your formula:
编辑我可以建议的最后一件事是在 F 列中添加一个辅助列，用于评估行以查看它是否符合要求条件，那么制作公式就很简单了：
=COUNTIFS(F1:F20,TRUE,S1:S20,"<>4")
回答by John Bustos
... Try removing the chr(34) and the extra quotations - It should look exactly like the "Requirement" criteria:
...尝试删除 chr(34) 和额外的引号 - 它应该看起来与“要求”标准完全一样：
Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & ",""Requirement"",S" & Start(groups) & ":S" & Finish(groups) & ",""<>4"")"
