vba 如何在多列中CountIf？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12856114/
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
How to CountIf in multiple columns?
提问by Alegro
x = 0
For Each cell in Sheets("01").Range("A:A").Cells
If cell.Value = "aaa" And cell.Offset(0, 1).Value = "bbb" Then
x = x+1
End If
Next cell
MsgBox x
Is there a shorter way, pls ?
请问有更短的方法吗？
回答by chris neilsen
You can do this with a formula, like this
你可以用一个公式来做到这一点，就像这样
(Excel 2007 or later)
（Excel 2007 或更高版本）
=COUNTIFS(A:A,"aaa",B:B,"bbb")
(Excel 2003 or earlier)
（Excel 2003 或更早版本）
=SUMPRODUCT(--(A:A="aaa")*--(B:B="bbb"))
Or, if you have to do it in VBA, use Evaluate
或者，如果您必须在 VBA 中执行此操作，请使用 Evaluate
MsgBox Evaluate("=COUNTIFS(A:A,""aaa"",B:B,""bbb"")")
MsgBox Evaluate("=SUMPRODUCT(--(A:A=""aaa"")*--(B:B=""bbb""))")
EDITbased on your comment
根据您的评论进行编辑
If rDatand rSecare Range's, and ctis the sheet CodeNamedo it like this
If rDatand rSecare Range's, and ctis the sheet CodeNamedo it like this
Dim rDat As Range
Dim rSec As Range
Set rDat = ct.[A:A]
Set rSec = ct.[B:B]
ct.Range("C6").Value = Evaluate("=COUNTIFS(" & rDat.Address(, , , True) & " ,""a""," & rSec.Address(, , , True) & ",""1"")")
If ctis the sheet Name, use this
如果ct是工作表Name，请使用此
Dim ws As Worksheet
Dim rDat As Range
Dim rSec As Range
Set ws = ActiveWorkbook.Worksheets("ct")
Set rDat = ws.[A:A]
Set rSec = ws.[B:B]
ws.Range("C6").Value = Evaluate("=COUNTIFS(" & rDat.Address(, , , True) & " ,""a""," & rSec.Address(, , , True) & ",""1"")")
If rDatand rSecare strings, use this
如果rDat和rSec是字符串，使用这个
Dim ws As Worksheet
Dim rDat As String
Dim rSec As String
Set ws = ActiveWorkbook.Worksheets("ct")
rDat = "ct!A:A"
rSec = "ct!B:B"
ws.Range("C6").Value = Evaluate("=COUNTIFS(" & rDat & " ,""a""," & rSec & ",""1"")")
Another option (for rDat, rSecas ranges)
另一种选择（对于rDat，rSec作为范围）
ct.Range("C6").Value = Application.WorksheetFunction.CountIfs(rDat, "a", rSec, "1")
