vba Application.Worksheetfunction Sumproduct 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19600336/
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
Application.Worksheetfunction Sumproduct issue
提问by CaptainABC
I have an excel sheet and I am trying to use a countifs formula to count the number or records meeting multiple conditions, however in one of the columns there are multiple criterias so I used SUMPRODUCT together with the COUNTIFS function.
我有一个 excel 表，我正在尝试使用 countifs 公式来计算满足多个条件的数字或记录，但是在其中一列中有多个条件，因此我将 SUMPRODUCT 与 COUNTIFS 函数一起使用。
I got it to work fine in the sheet but I have no idea on how to get it to work in VBA.
我让它在工作表中正常工作，但我不知道如何让它在 VBA 中工作。
This is what I tried:
这是我尝试过的：
Dim lastrow As Long
lastrow = Sheet2.Cells(Sheet2.Rows.Count, "M").End(xlUp).Row
FirstDate = Sheets("Sheet1").Range("C7")
SecondDate = Sheets("Sheet1").Range("E7")
Application.Worksheetfunction.Sumproduct(CountIfs(Sheet2.Range("E2:E" & lastrow), ">=" & FirstDate, Sheet2.Range("E2:E" & lastrow), "<=" & SecondDate, Sheet2.Range("P2:P" & lastrow), {"John";"James";"Peter"}))
I keep getting an error when using the above formula. Can you please tell me what I'm doing wrong.
使用上述公式时，我不断收到错误消息。你能告诉我我做错了什么吗？
Thanks in advance.
提前致谢。
EDIT: This is the formula I'm trying to mimic:
编辑：这是我试图模仿的公式：
=Sumproduct(CountIfs(Sheet2!E2:E1000000,">="&Sheet1!C7,Sheet2!E2:E1000000,"<="&Sheet1!E7,Sheet2!E2:E1000000,{"John";"James";"Peter"}))
=Sumproduct(CountIfs(Sheet2!E2:E1000000,">="&Sheet1!C7,Sheet2!E2:E1000000,"<="&Sheet1!E7,Sheet2!E2:E1000000,{"John";"James";"Peter "}))
I don't want VBA to insert this formula into the cell, I'd rather have it calculate the value and then insert the result into the chosen cell.
我不希望 VBA 将此公式插入到单元格中，我宁愿让它计算值，然后将结果插入到所选的单元格中。
回答by Tim Williams
Seems to work for me:
似乎对我有用：
Sub Tester()
Dim v, wsf
Dim lastrow As Long
lastrow = Sheet2.Cells(Sheet2.Rows.Count, "M").End(xlUp).Row
Set wsf = Application.WorksheetFunction
v = Application.WorksheetFunction.SumProduct(wsf.CountIfs( _
Sheet2.Range("E1:E" & lastrow), ">=" & Sheet1.Range("C7").Value, _
Sheet2.Range("E1:E" & lastrow), "<=" & Sheet1.Range("E7").Value, _
Sheet2.Range("P1:P" & lastrow), Array("John", "James", "Peter")))
Debug.Print v
End Sub
